Delphi - Indexek létrehozása

Transact SQL 15. rész

forráskód letöltése
Az adatbázis jobb teljesítményének eléréséhez érdemes indexeket létrehozni. Ebben a részben azt vizsgáljuk meg, hogy erre milyen lehetőségeink vannak
Indexet létrehozni a CREATE INDEX utasítással tudunk. Az INDEX kulcsszó után megadhatunk három kulcsszót, ezek a következők:
  • UNIQUE: egyedi index létrehozása. Ez azt jelenti, hogy nem lehet két olyan rekord, melyeknek az indexben szereplő mezőiben ugyanazok az adatok találhatók. Mielőtt ezt a kulcsszót használnák, meg kell bizonyosodnunk arról, hogy nincsenek az indexelt mezőkben egyforma adatokat tartalmazó rekordok.
  • CLUSTERED: fizikailag is rendezi a rekordokat, illetve a rekordok mindig az indexben meghatározott sorrendben lesznek tárolva. Ebből következik, hogy táblánként csak egy ilyen típusú index lehet. Ha ilyen indexet készítünk, akkor a tábla egészen addig zárolva lesz, amíg az indexelés véget nem ér, hiszen a rekordokat fizikailag is mozgatni kell.
  • NONCLUSTERED: az előző ellentéte. Ezt a kulcsszót nem kell használni, mert ha a CLUSTERED nincs megadva, akkor ez az alapértelmezett.
Az INDEX kulcsszó után kell megadni az index nevét. Ezután jön az ON kulcsszó, majd a tábla neve, majd zárójelben vesszővel elválasztva az indexben szereplő mezők (oszlopok) nevei, szóközzel elválasztva. Maximum 16 mezőt adhatunk meg, de a mezők együttes hossza semmiképpen sem haladhatja meg a 900 bájtos határt.
A WITH kulcsszó után különböző kapcsolókat adhatunk meg. A PADINDEX kapcsoló határozza meg, hogy egy index belső csomópontjai hány sort tartalmazzanak. Ez nem lehet kevesebb, mint két sor. A PADINDEX kapcsoló használatának a FILLFACTOR kapcsolóval együtt van értelme, mivel az abban megadott százalékarányt használja. A FILLFACTOR kapcsolóval állíthatjuk be, hogy az SQL Server mennyi helyet hagyjon fent az indexlapokon a még várható sorok számára. Azoknál a tábláknál növelheti a teljesítményt, amelyekbe gyakran szúrunk be adatokat. A FILFFACTOR utáni szám azt határozza meg, hogy az indexlapok hány százalékát kell szabadon hagyni. A kisebb érték azoknál a tábláknál használható, amelybe még biztosan több adat kerül. Olyan tábláknál, ahol az adatok nem változnak, a kapcsoló értékét akár 100-ra is állíthatjuk.
Az IGNORE_DUP_KEY kapcsoló azt határozza meg, hogy az egyedi index készítésekor (UNIQUE) hogyan viselkedjen az SQL Server, amikor két olyan rekordot talál, melyeknek az indexelt mezőkben lévő adataik megegyeznek. Ha ezt a kapcsolót megadjuk, akkor nem kapunk hibaüzenetet.
A SORTED_DATA használatával a CLUSTERED indexek esetén növelhetjük meg az index elkészítésének hatékonyságát. Akkor adjuk meg, ha biztosak vagyunk benne, hogy az indexelendő adatok már az indexnek megfelelő fizikai sorrendben vannak. Az SQL Server ellenőrizni fogja, hogy az adatok tényleg a helyes sorrendben vannak-e, és ha nem, akkor hibaüzenetet küld. Ellenkező esetben a kapcsoló használata jelentősen meggyorsítja az index elkészítését, hiszen az SQL Servernek nem kell rendeznie az adatokat, és ehhez kevesebb helyre is lesz szüksége.
A SORTED_DATA_REORG kapcsoló a nem CLUSTERED típusú index esetén is fizikailag sorba rendezi az adatokat az indexnek megfelelően, és eközben csökkenti az adatok töredezettségét is, miáltal jelentősen javul a teljesítmény.
Az IGNORE_DUP_ROW kapcsoló a nem egyedi indexszel (UNIQUE), de a CLUSTERED opcióval elkészített indexeknél használható. Ha az index elkészítése közben kettőződött adatokat talál az indexelt mezőkben, akkor törli a kettőzött értékeket, majd hibaüzenetet küld. A ROLLBACK-kel a törölt adatokat visszaállíthatjuk.
Ha az ALLOW_DUP_ROW kapcsolót használjuk nem egyedi, CLUSTERED indexek esetén, a kettőzött adatok megengedettek lesznek. Értelemszerűen az IGNORE_DUP_ROW kapcsolóval nem használható együtt.
Ha már volt előzőleg létrehozott CLUSTERED index, akkor a DROP_EXISTING kapcsolóval töröltethetjük, majd újra létrehozhatjuk azt. Az összes többi index is frissítésre kerül.
A STATISTICS_NORECOMPUTE kapcsolóval kikapcsolhatjuk az elosztási statisztikák frissítését. Ezt a beállítást az UPDATE STATISTICS paranccsal vonhatjuk vissza.
Az ON kulcsszó után megadhatjuk azt a fájlcsoportot, amelyet az index számára szeretnénk kijelölni. Ezzel a nem CLUSTERED indexek teljesítményét lehet javítani. CLUSTERED index esetén nem csak az index, hanem az összes adat is átkerül a megadott fájlcsoportba.
Most nézzük, hogy a példaprogramban mit is csináltunk.
01.sql
Létrehozunk egy teszt adatbázist.
02.sql
Létrehozunk egy táblát három mezővel. Ennek a táblának az adatait fogjuk indexelni.
03.sql
A táblába beszúrunk néhány rekordot, hogy lássuk majd az indexelés eredményét. A beszúrt adatok jelenleg még nincsenek rendezve.
04.sql
Létrehozunk egy egyedi, CLUSTERED (nyaláboló) indexet a tábla A mezőjére.
CREATE UNIQUE CLUSTERED INDEX TestIndex
ON Table1(A)
WITH FILLFACTOR = 10;
Ezáltal az adatok fizikailag is rendezve lesznek az A mezőben tárolt adatok szerint.
05.sql
Töröljük az indexet a DROP INDEX utasítással. Az utasítás után meg kell adni az index nevét, a tábla nevével bevezetve. Mivel az index úgy készült, hogy az adatok fizikailag is rendezve lettek, így a rekordok sorrendje továbbra sem változik meg, de ha új rekordot szúrnánk be, akkor a rendezés már nem történne meg.
DROP INDEX Table1.TestIndex;
06.sql
Töröljük a teszt adatbázist.

Transact SQL cikksorozat