Ville det ikke være skønt, hvis man kunne se om accessvejen for et SQL statement ville ændre sig med et nyt index. Det kan sagtens lade sig gøre ved at oprette indexet. Problemet er, at det har du måske ikke autorisation til, og et andet problem er, at et nyt index straks kan påvirke accessvejen for eksisterende SQL statements. I DB2 version 9 opfandt IBM så virtuelle indexer og i dette tip vil jeg gennemgå, hvordan du kan opfinde nye indekser, nedlægge eksisterende og se hvordan det påvirker accessvejen for dit SQL statement uden at påvirke nogen andre end dig.

For det første skal du bruge en tabel kaldet DSN_VIRTUAL_INDEXES. Den skal du oprette med samme creator, som den PLAN_TABLE, hvor du gemmer resultatet. Og så er det vist allerede på tide at nævne, at virtuelle indekser indtil videre kun virker for SQL statements, du laver EXPLAIN PLAN på. Du kan altså ikke se hvilken påvirkning en virtuel ændring af indekser har på en BIND PACKAGE med EXPLAIN(YES). Virtuelle indekser anvendes selvfølgelig heller ikke ved eksekvering af dit SQL statement.

Sandsynligvis findes der allerede en udgave af DSN_VIRTUAL_INDEXES på dit DB2-subsystem med creator DB2OSC. Hvis det er tilfældet kan du lave en CREATE DSN_VIRTUAL_INDEXES LIKE DB2OSC.DSN_VIRTUAL_INDEXES for at oprette tabellen. Hvis ikke, så skal du kigge i SDSNSAMP datasettet member DSNTIJOS. Hvis du ikke ved, hvordan du får fat i SDSNSAMP datasettet, så snak med en DBA'er eller en DB2 systemprogrammør.

Når tabellen er på plads og oprettet med samme creator som din PLAN_TABLE, så er du klar til at lege. Kør en EXPLAIN af dette SQL statement med dit foretrukne værktøj til formålet (jeg håber, du har SELECT autorisation til SYSIBM.SYSTABLES):

SELECT *
FROM SYSIBM.SYSTABLES
WHERE OWNER = 'SYSIBM'

Prøv så for eksempel denne insert:

INSERT INTO DSN_VIRTUAL_INDEXES
( TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE
, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
, PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
, COLNO1, ORDERING1, COLNO2, ORDERING2
)
SELECT TBCREATOR, TBNAME, CREATOR, 'MYVIRTUALINDEX', 'Y', 'C'
     , UNIQUERULE, COLCOUNT, 'Y', NLEAF, NLEVELS, INDEXTYPE
     , PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
     , 53, 'A', 28, 'A'
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SYSIBM'
  AND NAME = 'DSNDTX03'

Og udfør din EXPLAIN igen for at se, om DB2 gider bruge indekset. Det skulle helst være tilfældet. Læg mærke til, at indekset opbygges af kolonnenumre (53 og 28) i stedet for kolonnenavne. Det indeks, jeg kopierer har to kolonner, så derfor har jeg ikke hardcoded værdien af COLCOUNT, som skal stemme med de kolonner i indekset, du angiver. Jeg har sat CLUSTERING til Y i dette eksempel, for ellers kan det være, at optimizeren ikke gider bruge indekset.

Og for at lave lidt sjov i gaden, så forsøg med en EXPLAIN af dette SQL statement:

SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
  AND NAME LIKE 'SYS%'

Prøv så denne insert:

INSERT INTO DSN_VIRTUAL_INDEXES
( TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE
, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
, PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
)
SELECT TBCREATOR, TBNAME, CREATOR, NAME, 'Y', 'D'
     , UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
     , PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SYSIBM'
  AND NAME = 'DSNDTX01'

Og kør EXPLAIN af det sidstnævnte SQL statement igen. Nu skulle DB2 meget gerne enten bruge et andet index eller lave tablespacescan, fordi den sidste insert simulerede, at vi fjernede indekset.

Det er en rigtig god ide at tage udgangspunkt i et eksisterende indeks på den tabel, man gerne vil ændre accessvejen til. Hvis der ikke er noget indeks, så kopier et, der ligner, fra en anden tabel. Jeg benytter INSERT ... SELECT ... FROM ... til at kopiere med, primært for at få nogle nogenlunde fornuftige værdier i de kolonner, der vedrører RUNSTATS informationer.

De kolonner, der ikke findes i forvejen på SYSIBM.SYSINDEXES, styrer, hvordan EXPLAIN PLAN skal bruge det virtuelle indeks, og hvilke kolonner, der er i indekset (ikke nødvendigt, hvis du vil simulere et indeks, der skal droppes). Kolonnen ENABLE sættes til Y, når EXPLAIN PLAN skal tage hensyn til det virtuelle indeks og N, når der ikke skal tages hensyn til det. Med en UPDATE DSN_VIRTUAL_INDEXES SET ENABLE = 'N' slår du samtlige virtuelle indekser fra, og EXPLAIN PLAN vil opføre sig, som om du ikke havde oprettet DSN_VIRTUAL_INDEXES. Kolonnen MODE sættes til C, når du vil simulere et nyt indeks, og til D, når du skal simulere, at indekset ikke længere findes.

Kolonnerne COLNO1 til COLNOn udfyldes med nummeret på den eller de kolonner fra tabellen, som indekset skal bestå af. Højest mulig værdi for n er 64. ORDERING1 til ORDERINGn udfyldes med, om kolonnen skal være A for ascending eller D for descending. Hvis dit indeks skal bestå af kolonne 7 og 11 fra tabellen, så udfylder du COLNO1 med 7 og COLNO2 med 11, mens du lader være med at fylde noget i de resterende COLNO-kolonner. Du gør det tilsvarende med ORDERING-kolonnerne.

Til sidst skal du lige vide, at DSN_VIRTUAL_INDEXES-tabellen egentlig hører til et IBM-produkt, så "handle with care". Du kan læse mere om virtuelle indekser på denne blog skrevet af en af hjernerne bag DB2.