Indexek

1. Indexek

2. Data Pages Page 8 Page 9 Con ... ... ... ... ... Rudd Akhtar Smith Martin Ganio ... Page 4 Page 5 Page 6 Page 7 Funk ... ... ... ... ... White Funk Ota Phua Jones ... White ... ... ... ... ... Barr Smith Jones Jones Hall ... ... ... ... ... ... ... ... Martin ... Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... Indexek • Hogyan tároljuk az adatokat • A sorokat adatlapokon tároljuk • Az adatlapok egy halomszerű (heap) struktúrát alkotnak • Hogyan érjük el az adatokat • Az összes adatlap elérése • Egy index segítségével, ami az adatra mutat

3. Indexeljünk vagy ne? • Indexeljünk, mert • Gyorsabban érhetjük el az adatainkat • Biztosítja az adatok egyediségét • Ne indexeljünk, mert • Diszk területet használ • Többletmunkát igényel

4. Fizikai index struktúra • Az indexeket fizikailag különbözőképpen szervezhetjük meg a háttértárolón • Bináris fa • B-fa • B+-fa

5. Fizikai index struktúra • Logikailag az index egy rendezett lista • Fizikailag a rendezett sorrendet táblába rendezett mutatók biztosítják • Fa-struktúrájú indexek • Bináris fa • B-fa • B+-fa Fa Struktúra GYÖKÉR CSOMÓ PONT CSOMÓ PONT CSOMÓ PONT LEVELEK Csomópont: elágazási pont

6. Bináris fa • Minden egyes index-bejegyzés a fa egy csomópontja • Az egy olyan táblázat, amelynek négy mezője van: • az index mező, kulcsérték és cím • baloldali, vagy kisebb mutató, amely egy kisebb kulcsértékű csomópontra mutat • jobboldali vagy nagyobb mutató, amely egy nagyobb kulcsértékű csomópontra mutat Baloldali mutató Kulcsérték Mutató az adatfájlban egy címre Jobboldali mutató A bináris fa egy csomópontja

7. Példa a bináris fára Adatfájl Az index táblázatként - (csak a kulcsértékek) 1 2 3 4 5 6 7 LP Key Add RP 16 87 13 54 22 35 39 Gyökér 1 3 16 1 2 2 4 87 2 16 Gyökér 3 13 3 1 4 5 54 4 5 22 5 6 13 87 3 2 6 35 6 7 7 39 7 54 4 22 35 39 5 6 7

8. Problémák a bináris fával • Az mutatók a fa minden szintjére kiterjednek. Ennek eredménye: • Különböző elérési idők • Komplex fa-keresési programozás • A bináris fa általában kiegyenlítetlen: • Ahhoz, hogy a fát kiegyenlítsük (egyenlő hosszú ágak), a kulcsértéknek a maradék fa rész értékeinek a középértéke kell, hogy legyen • Ez tulajdonképpen lehetetlen, mivel fentről töltjük fel a fát a kulcsértékek érkezési sorrendjében, így a fa nem kiegyenlített, és ez különböző elérési időt eredményez

9. A kiegyenlítetlenség megoldása az index fákban • Töltsük fel a fát “alulról”. Vagyis egy bizonyos számú kulcsérték után válasszuk ki a középértéket és tegyük egy szinttel feljebb. Ezzel a bal és jobb oldala egyenlő lesz. • Ennek az eredménye • többértékű csomópontok, vagyis több kulcsérték sorrendben tárolása minden egyes csomóponton • Csomópont-szakadás: egy túlcsordult csomópontot ketté osztjuk mégpedig úgy, hogy vesszük az alsó és felső határértéket

10. A B-fa egy csomópontja • Egy csomóponthoz több kulcsérték • K1<K2<K3 - a kulcsértékek sorban • A mutatók más csomópontokra mutatnak, és így az összes kulcsértékre az adott csomóponton Baloldali mutató - olyan csomópontokra mutat, amelyek értéke kisebb, mint K1 K1 K2 K3 A1 A2 A3 Jobboldali mutató Olyan csomópontokra mutat, amelyek értéke >K1, de <K2

11. B-fa csomópont-szakadás Létező csomópont értékek: 12 23 27 38 A beszúrandó új érték: 19 A 23-as kulcsérték felkerül a következő szintre, ahonnan erre a két csomópontra mutat Szakadás: 12 19 23 27 38 Ezek az értékek a régi csomóponton maradnak Ezek az értékek alkotják az új csomópontot

12. B-fa csomópont-szakadás példa Az adatfájlban két rekord van - a gyökér tele van. Adatfájl: Gyökér: 1 2 3 4 36 87 36 87 2 1 Egy új rekord, aminek kulcsértéke 27 bekerül a 3. cellába Új gyökér 36 2 A szakadás: Felkerül 27 87 27 36 87 3 1

13. Az index jelenlegi állapota K1 A1 K2 A2 Gyökér 1 2 36 2 3 2 27 3 3 87 1 36 4 2 27 87 3 1

14. B-fák előnyei és hátrányai • Kiegyenlített - minden ág egyenlő hosszú, vagyis ugyanazon a szinten fejeződik be. Ezért • kiküszöbölik a változó elérési időket, amik a bináris fákban megfigyelhetők • Bár a kulcsértékek, (és a hozzájuk kapcsolódó címek), még mindig a fa minden szintjén megtalálhatók, ennek eredménye: • egyenlőtlen elérési utak, és egyenlőtlen elérési idő, valamint • komplex fa-keresési algoritmus az adatfájl logikailag soros olvasására

15. A kulcsértékek eloszlásának kiküszöbölése • Nem engedjük meg adatfájl címek tárolását a levélszint felett • Ebből következően: • minden elérés ugyanolyan hosszú utat vesz igénybe, aminek egyenlő elérési idő az eredménye • egy logikailag soros olvasása az adatfájlnak a levélszint elérésével megoldható. Nincs szükség komplex fa-keresési algoritmusra

16. A megoldás implementálása • Mivel minden kulcsérték a levélszinten jelenik meg, ezért egyes kulcsértékek többször jelennek meg az indexben, ezért • a felsőbb szintű csomópontoknak nincs szükségük címmezőkre, és a levélszinten nincs szükség lefelé mutató index mutatókra • a középérték, amit a felsőbb szintre kell tolni, amikor a csomópont szakad, valamelyik ‘félhez’ kell, hogy tartozzon: legjobboldali érték a bal oldalon (kisebb-egyenlő mutatók), vagy a legbaloldalibb érték a jobb oldalon (nagyobb egyenlő mutatók).

17. A B+-fa Az adatfájl: 5 1 2 3 4 25 56 9 72 41 34 Gyökér 41 41 72 9 34 56 Levél szint 2 5 4 1 3 A baloldali csomópont szakad, amikor a 25-öt beszúrjuk. A felső fele átmegy a jobboldali csomópontra

18. 9 25 34 41 B+-faEgy rekord beszúrása 25-ös kulcsértékkel Az adatfájl: 1 2 3 4 56 9 72 41 34 25 Gyökér 25 41 Szakadás 9 25 56 72 34 41 2 6 1 3 5 4

19. SQL Server Index Architektúra

20. Index Pages Index Pages Non-Leaf Level Non-leaf Level Akhtar Akhtar ... ... Martin Martin Page 37 Page 37 Page 28 Page 28 Page 12 - Root Page 12 - Root SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Martin' AND 'Rudd' Akhtar Akhtar Martin Martin Ganio Ganio Smith Smith ... ... ... ... Leaf Level(Key Value) Leaf Level(Key Value) Page 41 Page 41 Page 51 Page 51 Page 61 Page 61 Page 71 Page 71 Martin Martin Martin 470801 470801 470801 Akhtar Akhtar 470601 470601 Smith Ganio Smith Ganio 470901 470603 470603 470901 Martin 470604 Martin Martin 470604 470604 Barr Barr 470503 470503 Hall Hall Smith Smith 470804 470904 470804 470904 Ota Ota 470702 470702 Con Con 470401 470401 Jones Jones Smith Smith 470902 470701 470902 470701 Ota 470702 Phua Phua 470802 470802 Funk Funk 470602 470602 White Jones White Jones 470803 470403 470403 470803 Phua 470802 Rudd Rudd 470501 470501 Funk Funk 470402 470402 White White Jones Jones 470703 470703 470502 470502 Rudd 470501 Data Pages Data Pages Page 8 Page 8 Page 9 Page 9 Page 4 Page 4 Page 5 Page 5 Page 6 Page 6 Page 7 Page 7 Con Con ... ... 470401 470401 Rudd Rudd ... ... 470501 470501 Akhtar Akhtar ... ... 470601 470601 Smith Smith ... ... 470701 470701 Martin Martin Martin ... ... ... 470801 470801 470801 Ganio Ganio ... ... 470901 470901 Rudd ... 470501 Funk Funk ... ... 470402 470402 White White ... ... 470502 470502 Funk Funk ... ... 470602 470602 Ota Ota ... ... 470702 470702 Phua Phua Phua ... ... ... 470802 470802 470802 Jones Jones ... ... 470902 470902 Ota ... 470702 White White ... ... 470403 470403 Barr Barr ... ... 470503 470503 Smith Smith ... ... 470603 470603 Jones Jones ... ... 470703 470703 Jones Jones ... ... 470803 470803 Hall Hall ... ... 470904 470904 ... ... ... ... ... ... ... ... ... ... ... ... Martin Martin ... ... 470604 470604 ... ... ... ... ... ... Smith Smith ... ... 470804 470804 ... ... ... ... ... ... Martin ... 470604 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Adatok egy bizonyos tartományának elérése Martin Martin

21. Csoportosított (Klaszter) indexek használata • Minden egyes táblának maximum egy csoportosított (klaszter) indexe lehet • A sorok fizikai sorrendje megegyezik a sorok sorrendjével az indexben • A kulcsértékek egyediségét automatikusan biztosítja (explicit vagy implicit)

22. Index Pages Index Pages 1234 1234 Non-Leaf Level Non-leaf Level ... ... 5678 5678 5678 Page 37 Page 37 Page 28 Page 28 Page 12 - Root Page 12 - Root 5678 5678 5678 7678 7678 SELECT lastname, firstname FROM member WHERE member_no BETWEEN 5678 AND 6078 ... ... 1234 Akhtar Akhtar 1234 Page 41 Page 41 Page 51 Page 51 Page 61 Page 61 Page 71 Page 71 Leaf Level(Key Value) Leaf Level(Key Value) Ganio 2234 Ganio 2234 1234 1234 Martin Martin 2234 2234 White White 5678 5678 Barr Barr 7678 7678 Ganio Ganio 1234 1234 ... ... ... ... 1334 1334 Funk Funk 2334 2334 Akhtar Akhtar 5778 5778 Smith 8721 Smith 8721 7778 7778 Martin 7206 Martin 7206 1434 1434 Smith Smith 2434 2434 Jones Jones 5878 5878 Ota Ota 7878 7878 Phua Phua 5678 Barr 1534 1534 Funk4705 Funk4705 2534 2534 Con Con 5978 5978 Jones 5788 Jones 5788 7978 7978 Smith 8772 Smith 8772 5778 Smith 8721 1634 1634 White9355 White9355 2634 2634 Jones6002 Jones6002 6078 6078 6078 Rudd Rudd Rudd 8078 8078 Hall Hall 5878 Ota 5978 Jones 5788 Clustered Index Clustered Index Akhtar Akhtar Akhtar ... ... Martin Martin Martin Page 141 Page 141 Page 145 Page 145 Page 140 - Root Page 140 - Root Martin Martin Martin Akhtar Smith Smith Smith Ganio ... ... ... 5878 5678 Barr Ota ... ... 5778 5978 Smith Jones ... ... 8721 5788 2334 2334 Akhtar Akhtar ... 7678 ... Ganio ... 7678 Ganio ... 1234 1234 Martin Martin ... ... 1434 Smith ... 5678 5678 Barr Barr ... 8078 ... Hall ... 8078 Hall ... 7778 7778 Martin Martin ... ... 7206 7206 5778 Smith ... 8721 2534 2534 Con Con ... 2434 ... Jones ... 2434 Jones ... 5878 5878 Ota Ota ... ... 7978 Smith ... 8772 1434 Smith ... 1334 1334 Funk Funk 5978 ... ... Jones ... 5978 5788 Jones ... 5788 7878 7878 Phua Phua ... ... 2234 White ... 5778 Smith ... 8721 6078 Rudd ... ... 4705 ... 4705 ... 6002 ... 6002 ... ... ... 9355 1534 1534 Funk Funk 2634 Jones 2634 Jones 6078 6078 Rudd Rudd 1634 White 7978 Smith ... 8772 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 2234 White ... Page 100 Page 100 Page 110 Page 110 Page 120 Page 120 Page 130 Page 130 ... 9355 1634 White ... ... ... ... Adatok egy bizonyos tartományának elérése csoportosított kulccsal

23. Nem csoportosított indexek használata • Az SQL Server alapértelmezett indexe a nem csoportosított index • Már létező nem csoportosított indexeket újra kell építeni, ha • Egy létező csoportosított indexet törlünk • Csoportosított indexet hozunk létre • A DROP_EXISTING opciót használjuk, amikor megváltoztatjuk az index oszlopait

24. Indexek létrehozása SQL Serveren • Indexek létrehozása és törlése • Egyedi indexek létrehozása • Összetett indexek létrehozása • Információ létező indexekről

25. Indexek létrehozása és törlése • A CREATE INDEX utasítás • A PRIMARY KEY és UNIQUE korlátozások automatikusan indexet hoznak létre • Nézetekre nem lehet indexet létrehozni • A DROP INDEX utasítás USE library CREATE CLUSTERED INDEX cl_lastnameON library..member (lastname) USE library DROP INDEX member.cl_lastname

26. USE library CREATE UNIQUE INDEX title_ident ON title (title_no) title title_no title author synopsis 10 11 12 The Night-Born Lemon Walking Jack London Motojirou Henry David Thoreau ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 12 Le Petit Prince Antoine de Saint-Exupery 12 Walking Henry David Thoreau ~ ~ ~ Dupla kulcsértékek beszúrása nem megengedett Egyedi indexek létrehozása

27. loan isbn copy_no title_no member_no out_date 342 342 343 5 10 4 35 35 35 3744 5278 3445 1998-01-06 1998-01-04 1998-01-04 Összetett indexek létrehozása USE library CREATE UNIQUE INDEX loan_ident ON loan (isbn, copy_no) 1. oszlop 2. oszlop Összetett index

28. Információ létező indexekről • Az sp_helpindex rendszer tárolt eljárás segítségével • Az sp_help táblanév rendszer tárolt eljárás segítségével USE library EXEC sp_helpindex member

29. Indexelési útmutató • Indexelendő oszlopok • Elsődleges és idegen kulcsok • Gyakori adattartomány keresés • Rendezett elérés • Nem indexelendő oszlopok • Ritkán használt • Kevés különböző értékkel rendelkező oszlopok • BLOB

30. Oracle index-típusai • Egyszerű és összetett indexek • Függvény-alapú indexek • Bittérkép (bitmap) indexek • Fordított kulcsú (reverse key) indexek • Alkalmazás-specifikus (application domain) indexek • Csoport (Cluster) indexek

31. Egyszerű és összetett indexek • B*-fa • Kiegyensúlyozott • Táblamérettől függetlenül használható • Egyszerű index • Csak egy oszlopot tartalmaz • Összetett index • Több oszlopot tartalmaz • ABC • A • AB • ABC

32. Függvény-alapú indexek • Kifejezés értéke előre kiszámolva • Matematikai kifejezések • Oracle függvények • determinisztikus • CREATE INDEX idx ON table1 (a+b*(c-1),a,b) • SELECT a FROM table1 WHERE a+b*(c-1)<100;

33. CUSTOMER # MARITAL_ STATUS REGION GENDER INCOME_ LEVEL 101 single east male bracket_1 102 married central female bracket_4 103 married west female bracket_2 104 divorced west male bracket_4 105 single central female bracket_2 106 married central female bracket_3 REGION='east' REGION='central' REGION='west' 1 0 0 0 1 0 0 0 1 0 0 1 0 1 0 0 1 0 Bittérkép (bitmap) indexek

34. Lekérdezés bitmap index-szel SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');

35. Fordított kulcsú (reverse key) indexek • Oszlopon belül bájtok sorrendje megfordítva • Oszlopok sorrendje ugyanaz • Logikailag közeli értékek egymástól távol • Gyors módosítás - párhuzamos futtatás • Tartomány keresése nem lehetséges

36. Indexelt-szervezésű tábla (Index-organized table) • Adatok tárolása az index-szel együtt • Csak elsődleges kulccsal rendelkező tábla esetén • Ebben az esetben az index nem az index-szegmensben jön létre • Célszerű ha legtöbbször elsődleges kulcs szerint keresünk

37. Csoport-index (cluster) • Gyakran összekapcsolt táblák esetén • Fizikailag egymáshoz közel tárolja a két táblát • Cluster-index nélkül nem elérhető a tábla

38. Csoport-index (cluster)

39. Csoport létrehozása CREATE CLUSTER stu_enrol(stunum varchar2(3)); Create table student ( stunum varchar2(3), surname varchar2(10), street varchar2(10), primary key(stunum)) cluster stu_enrol (stunum); Create table enrol ( stunum varchar2(3), course_id char(7), primary key(stunum,course_id)) cluster stu_enrol (stunum); CREATE INDEX ind_enrol ON CLUSTER stu_enrol;

40. Indexek létrehozása • CREATE INDEX i ON t (a,b,c) • CREATE UNIQUE INDEX i ON t (a,b,c) • CREATE BITMAP INDEX i ON t (a,b,c) • CREATE INDEX i ON t (a,b,c) REVERSE • CREATE INDEX i ON t(a+b*(c-1),a,b)

41. Indexek kezelése • ALTER INDEX i REBUILD • DROP INDEX i