Usuwanie indeksów z wysokim clustering factor

Opisany problem dotyczy bardzo niskiej wydajności operacji DML na  tabelach o trochę większym wolumenie (zetknąłem się problemem, kiedy wielkość tabel przekraczała około 300 mln rekordów), często partycjonowanych, zawierających klasyczne drzewiaste indeksy o  bardzo dużej selektywności. Wówczas najszybszą (w moi odczuciu :) ) metodą dokonania operacji DML na większym wolumenie danych jest:

1. Usunięcie indeksów o wysokim clustering factor
2. Wykonanie operacji DML
3. Ponowne stworzenie usuniętych indeksów 

W poniższym rozwiązaniu wykorzystałem własny pakiet logujący prosty logger wzorowany na log4j.
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;

CREATE OR REPLACE PACKAGE BODY 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;