Understanding Different Index Types in Database Systems
Learn about the various types of indexes in database systems including B-tree index, bitmap index, unique index, composite index, and more. Explore how each index structure works and when to use them effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Indexek WHERE key = 22 Row pointer Key 22 22 Table Index
Types of Indexes These are several types of index structures available to you, depending on the need: A B-tree index is in the form of a binary tree and is the default index type. A bitmap index has a bitmap for each distinct value indexed, and each bit position represents a row that may or may not contain the indexed value. This is best for low- cardinality columns.
B+ fa index Index entry Root Branch Index entry header Key column length Key column value ROWID Leaf
Bitmap Indexek File 3 Table Block 10 Block 11 Block 12 Index Start ROWID End ROWID Key Bitmap <Blue, 10.0.3, 12.8.3, 1000100100010010100> <Green, 10.0.3, 12.8.3, 0001010000100100000> <Red, 10.0.3, 12.8.3, 0100000011000001001> <Yellow, 10.0.3, 12.8.3, 0010001000001000010>
Index Options A unique index ensures that every indexed value is unique. An index can have its key values stored in ascending or descending order. A reverse key index has its key value bytes stored in reverse order. A composite index is one that is based on more than one column. A function-based index is an index based on a function s return value. A compressed index has repeated key values removed.
Bitmap Index Empno Status Region Gender Info 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
Bitmap Indexek hasznlata SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married AND REGION IN ('central','west');
Intervallum lekrdezsek AGE 25 45 50 50 50 70 85 30 25 45 50 60 SALARY 60 60 75 100 120 110 140 260 400 350 275 260 SELECT * FROM T WHERE Age BETWEEN 44 AND 55 AND Salary BETWEEN 100 AND 200; Bitvectors for Age Bitvectors for Salary 25: 100000001000 60: 110000000000 30: 000000010000 75: 001000000000 45: 010000000100 100: 000100000000 50: 001110000010 110: 000001000000 60: 000000000001 120: 000010000000 70: 000001000000 140: 000000100000 85: 000000100000 260: 000000010001 275: 000000000010 350: 000000000100 400: 000000001000
Intervallum lekrdezsek AGE 25 45 50 50 50 70 85 30 25 45 50 60 SALARY 60 60 75 100 120 110 140 260 400 350 275 260 SELECT * FROM T WHERE Age BETWEEN 44 AND 55 AND Salary BETWEEN 100 AND 200; 45: 010000000100 50: 001110000010 OR -> 011110000110 100: 000100000000 110: 000001000000 120: 000010000000 140: 000000100000 OR -> 000111100000 011110000110 000111100000 AND -> 000110000000
Tmrtsk a bitmap-eket A bitvektorban az 1-esek ritk k, sokkal t bb a 0. Szakaszhossz k dol s: A 0- kb l ll , ( s 1-essel v gz d ) i hossz s g szakaszok hossz t k doljuk. 10000001000000000100010000000000001 1. Meghat rozzuk, hogy az i bin risan br zolva h ny bitb l ll: ez lesz a j sz m. 2. Ezt un risan br zoljuk: j-1 db 1-es majd egy 0. 3. M g rjuk i bin ris rt k t.
Tmrtsk a bitmap-eket P lda: 100000000000001 13 db 0- b l ll szakasz j = 4 -> un risan: 1110 i bin risan: 1101 A szakasz k dolva: 11101101
Tmrtsk a bitmap-eket i = 0 eset, amikor k t 1-es van egym s ut n (k dolva: 00) i=1 k dolva: 01 A z r 0- kat nem t roljuk. Visszafejt s: Fejts k vissza: 11101101001011 -> 13, 0, 3 Az eredeti: 0000000000000110001
