- Usunięcie indeksów o wysokim clustering factor
- Wykonanie operacji DML
- Ponowne stworzenie usuniętych indeksów
Do usuwania indeksów w innych schematach niż bieżący przydałyby się uprawnienia CREATE ANY INDEX i DROP ANY INDEX
CREATE TABLE TBL_INDEXES
(
ID NUMBER(10),
SCHEMA_NAME VARCHAR2(30 CHAR) NOT NULL,
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
INDEX_NAME VARCHAR2(30 CHAR) NOT NULL,
DDL_STATEMENT CLOB
);
ALTER TABLE TBL_INDEXES ADD (
CONSTRAINT PK_INDEXES_COMPLEX
PRIMARY KEY (ID, SCHEMA_NAME, TABLE_NAME, INDEX_NAME);
CREATE SEQUENCE SEQ_INDEXES START WITH 1 NOCACHE NOCYCLE;
CREATE OR REPLACE PACKAGE PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2) RETURN BOOLEAN;
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2) RETURN BOOLEAN;
END PCKG_INDEXES;
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2) RETURN BOOLEAN
AS
vn_LiczbaBledow NUMBER(10) :=0;
CURSOR C IS
SELECT
INDEX_NAME,
DDL_STATEMENT
FROM TBL_INDEXES
WHERE ID =
(
SELECT MAX(ID) FROM TBL_INDEXES WHERE TABLE_NAME = pc_TableName AND SCHEMA_NAME = pc_SchemaName
);
BEGIN
COMMIT; -- aby zamknąc wszystkie locki w danym połączeniu
FOR EL IN C
LOOP
BEGIN
EXECUTE IMMEDIATE EL.DDL_STATEMENT;
PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_CreateIndexes', 'Pomyślnie utworzono indeks: ' || EL.INDEX_NAME);
EXCEPTION WHEN OTHERS THEN
PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_CreateIndexes', 'Błąd podczas tworzenia indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );
vn_LiczbaBledow := vn_LiczbaBledow +1;
END;
END LOOP;
IF vn_LiczbaBledow > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
------------------------------------------------------------------------------------------
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2) RETURN BOOLEAN
AS
vn_SeqId NUMBER(10):= SEQ_INDEXES.nextval;
CURSOR C IS
SELECT
vn_SeqId AS ID,
OWNER,
TABLE_NAME,
INDEX_NAME,
DBMS_METADATA.GET_DDL( 'INDEX', INDEX_NAME, pc_SchemaName) AS DDL_STATEMENT
FROM ALL_INDEXES -- Tutaj działającą perspektywą zawsze będzieALL_INDEXES, dla ALL_INDEXES trzeba mieć specjalne uprawnienia
WHERE TABLE_NAME = pc_TableName
AND OWNER = pc_SchemaName
AND 4*CLUSTERING_FACTOR > NUM_ROWS
AND INDEX_TYPE = 'NORMAL';
BEGIN
FOR EL IN C
LOOP
BEGIN
INSERT INTO TBL_INDEXES VALUES EL;
EXECUTE IMMEDIATE 'DROP INDEX ' || EL.INDEX_NAME;--- DDL czyli niejawny COMMIT = zatwierdzainstrukcję insert
PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Pomyślnie usunięto indeks: ' || EL.INDEX_NAME);
EXCEPTION WHEN OTHERS THEN
DELETE FROM TBL_INDEXES WHERE ID = vn_SeqId;
PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Błąd podczas usuwania indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );
COMMIT;
RETURN FALSE;
END;
END LOOP;
RETURN TRUE;
END;
END PCKG_INDEXES;