Počas vývojov databázových aplikácií som sa mnohokrát stretol so stavom, že na vývoji projektu spolupracovalo veľa programátorov a každý z nich zanechal v databáze svoju časť práce a nejaké tie stopy po vývoji, ktoré síce mal po sebe zmazať (upratať), ale napr. z časového dôvodu to nestihol alebo zabudol. Stáva sa teda, že v programoch okrem zostávajú nie len dobré časti a črty, ale aj tie zlé, spôsobené buď nedbalosťou, zábudlivosťou alebo neznalosťou. Jednou z nich sú napríklad aj duplicitné objekty na tabuľkách.

Príklad: Týmto jednoduchým SQL dotazom si viem v rámci mojej schémy zobraziť zoznam duplicitných idexov na tabuľke alebo viacerých tabuľkách.

WITH IDX AS (SELECT I.TABLE_NAME,
                    C.INDEX_NAME,
                    LISTAGG(C.COLUMN_NAME, ';')WITHIN GROUP (ORDER BY I.TABLE_NAME, C.INDEX_NAME, C.COLUMN_POSITION) AS COL
             FROM USER_INDEXES I
               JOIN USER_IND_COLUMNS C ON C.INDEX_NAME = I.INDEX_NAME
             GROUP BY I.TABLE_NAME, C.INDEX_NAME
            )
SELECT IDX1.*, IDX2.INDEX_NAME AS INDEX_NAME2, IDX2.COL AS COL2 
FROM IDX IDX1
  JOIN IDX IDX2 ON IDX1.TABLE_NAME = IDX2.TABLE_NAME AND IDX1.INDEX_NAME <> IDX2.INDEX_NAME
    AND IDX1.COL||';' = SUBSTR(IDX2.COL, 1, LENGTH(IDX1.COL)+1)
ORDER BY 1, 2

Vysvetlenie: Predstavme si tabuľku TEMPTABLE:

SQL>CREATE TABLE TEMPTABLE
(
  ID_PX NUMBER,
  A_NAME  VARCHAR2(20 CHAR) NOT NULL,
  B_NAME  VARCHAR2(20 CHAR) NOT NULL,
)
TABLESPACE TEMPTABLESPACE;

Vytvoríme primárny kľuč:

SQL>ALTER TABLE TEMPTABLE ADD (PRIMARY KEY (ID_PX) USING INDEX TABLESPACE INDEX_TEMPTABLESPACE);

Do tabuľky sa vložil jeden milón záznamov a chceme hľadať záznam s hodnotou v stĺpci A_NAME napr. 'Alena', takže programátor napíše:

SQL>SELECT * FROM TEMPTABLE WHERE A_NAME='Alena';

Výsledok mu príde, tipnem si, za 8 sekúnd. Programátor ale vie, že na urýchelnie hľadania je možné na tabuľke vytvoriť index na stĺpci A_NAME a urýchliť tak hľadanie, tak napíše:

SQL>CREATE INDEX IX_A_NAME ON TEMPTABLE (A_NAME) TABLESPACE INDEX_TEMPTABLESPACE;

potom

SQL>SELECT * FROM TEMPTABLE WHERE A_NAME='Alena';

Výsledok mu príde, tipnem si, za 0,1 sekundy. Super. Po určitom čase sa vráti k programu a chce naprogramovať rýchle hľadanie na oboch stĺpcoch A_NAME aj B_NAME. Zabudne na metadáta o tabuľke (že nejaké indexy už na tabuľke vytvorené sú) TEMPTABLE a ihneď píše:

SQL>CREATE INDEX IX_NAMES ON TEMPTABLE (A_NAME,B_NAME) TABLESPACE INDEX_TEMPTABLESPACE;

potom

SQL>SELECT * FROM TEMPTABLE WHERE A_NAME='Alena' AND B_NAME='Cierna';

Výsledok mu príde, tipnem si, za 0,2 sekundy. Všetko sedí, program ako tak funguje, zadávateľ projektu je spokojný ..... Po čase a zaplnení tabuliek sa ale začne spomalovať vkladanie záznamov do tabuľky TEMPTABLE sql príkazm INSERT. Zistíme, že na tabuľke sú vytvorené 3 indexy: primárny a ďalšie dva - IX_A_NAME a IX_NAMES.

Ak si zadáme hore uvedený kontrolný dotaz na duplicitu indexov, vyhodí nám pre tabuľku TEMPTABLE dva indexy: IX_A_NAME a IX_NAMES. Čo to všetko znamená? Znamená to to, že sme vytvorili jeden zbytočný index IX_A_NAME alebo IX_NAMES, pretože IX_A_ANAME je podmnožinou indexu IX_NAMES. Vyplýva z toho, že index IX_A_NAME alebo IX_NAMES vymažem a určite urýchlim vkladanie záznamov do tabuľky TEMPTABLE, pretože po každom vložení záznamu/záznamov sa musí každý index modifikovať paralelne. Čím viac indexov na tabuľke bude figurovať, tým pomalšie budú spracovávané DML príkazy: INSERT, UPDATE a DELETE.

Ak budeme chcieť hľadať riadky na tabuľke len podľa stĺpca A_NAME, optimalizér môže použiť index IX_A_NAME ale aj IX_NAMES v závislosti, čo ďalej sa nachádza v klauzule WHERE. Ak budeme chcieť hľadať na stĺpci A_NAME aj B_NAME mal by byť použitý index IX_NAMES. Pre vymazanie indexu IX_A_NAME uvažujem tak, že v prípade hľadania hodnôt na len stĺpci A_NAME alebo A_NAME aj B_NAME optimalizer využije určite (pri nájdení malého počtu riadkov) len jeden index IX_NAMES. Ak vymažem index IX_NAMES, v prípade hľadania hodnoty len na stĺpci A_NAME bude použitý index IX_A_NAME a v prípade hľadania hodnôt na oboch stĺpcoch A_NAME aj B_NAME taktiež, len s rozdielom, že hodnotu v stĺpci B_NAME doskipuje - dohľadá sekvenčne (pokiaľ nie je index IX_NAMES tzv. UNIQUE). A teraz je na mne ako sa rozhodnúť, ktorý z indexov zmazať. Jedno je isté, určite by som mal jeden zmazať (existuje aj výnimka, kedy nie, má to zmysel v prípade, že hľadanie na danom indexe mám experimentálne odmerané a je rýchlejšie).

Ja osobne by som sa rozhodol takto. V prípade vysokej variability hodnôt v stĺpci A_NAME by som vymazal index IX_NAMES, v prípade nízkej variability hodnôt index IX_A_NAMES. Ďalším kritériom je, že hodnoty v indexe sú vlastne duplikované hodnoty hodnôt tabulky, tak v inom prípade by som bral do úvahy fakt menšieho objemu dát v indexe. Správanie optimalizéra v prípade CBO optimalizéra je závislé od viacerých faktorov, vrátane interných štatistík. Znamená to aj to, že v prípade nárastu objemu dát sa optimalizér môže rozhodnúť inak.

Dúfam, že som aspoň trochu pomohol programátorom rozhodnúť sa v prípade optimalizácie DML príkazov na tabuľkách.

S pozdravom Alojz Benďák