Virtual University of Pakistan Data Warehousing Lecture27 Need

Virtual University of Pakistan Data Warehousing Lecture-27 Need for Speed: Special Indexing Techniques Ahsan Abdullah Assoc. Prof. & Head Center for Agro-Informatics Research www. nu. edu. pk/cairindex. asp National University of Computers & Emerging Sciences, Islamabad Email: ahsan 1010@yahoo. com DWH-Ahsan Abdullah 1

Special Index Structures § Inverted index § Bit map index § Cluster index § Join indexes 2 DWH-Ahsan Abdullah

Sample table 3 DWH-Ahsan Abdullah

Inverted index: Concept 4 DWH-Ahsan Abdullah

Inverted Index: Example-1 D 1: M. Asalm BS Computer Science Lahore Campus D 2: Sana Aslam of Lahore MS Computer Engineering with GPA 3. 4 Karachi Campus Inverted index for the documents D 1 and D 2 is as follows: 3. 4 Asalm BS Campus Computer Engineering GPA [D 2] [D 1, D 2] [D 2] Karachi Lahore M. MS of Sana Science with [D 2] [D 1, D 2] [D 1] [D 2] [D 1] [D 2] 5 DWH-Ahsan Abdullah

. . . Inverted Index: Example-2 B-tree Index r 500 afridi inverted index 19 CS data records 6 DWH-Ahsan Abdullah

Inverted Index: Query § Query: § Get students with age = 20 and tech = “telecom” § List for age = 20: r 4, r 18, r 34, r 35 § List for tech = “telecom”: r 5, r 35 § Answer is intersection: r 35 7 DWH-Ahsan Abdullah

Bitmap Indexes: Concept 8 DWH-Ahsan Abdullah

Bitmap Indexes: Example § The index consists of bitmaps, with a column for each unique value: Index on City (larger table): Index on Tech (smaller table): 9 DWH-Ahsan Abdullah

Bitmap Index: Query § Query: § Get students with age = 20 and campus = “Lahore” § List for age = 20: 1101100000 § List for campus = “Lahore”: 1010000001 § Answer is AND : 100000 § § Good if domain cardinality is small Bit vectors can be compressed § Run length encoding 10 DWH-Ahsan Abdullah

Bitmap Index: Compression Basic Concept Case-1 11110000001111100000011111 INPUT 14#04#14#06#15 OUTPUT 101010101010101010 INPUT Case-2 Case-3 11#01#11#01#… OUTPUT 1111111100000000 INPUT 117#017 OUTPUT 11 DWH-Ahsan Abdullah

Bitmap Index: More Queries § “Which students from Lahore are enrolled in ‘CS’? ” § “How many students are enrolled in ‘CS’? ” 12 DWH-Ahsan Abdullah

Bitmap Index: Adv. 13 DWH-Ahsan Abdullah

Bitmap Index: Performance Points 14 DWH-Ahsan Abdullah

Bitmap Index: Dis. Adv. 15 DWH-Ahsan Abdullah

Cluster Index: Concept 16 DWH-Ahsan Abdullah

Cluster Index: Example Cluster indexing on AGE One indexing column at a time Cluster indexing on TECH 17 DWH-Ahsan Abdullah

Cluster Index: Issues 18 DWH-Ahsan Abdullah

Join Index: Example The rows of the table consist entirely of such references, which are the RIDs of the relevant rows. PROGRAM join index CAMPUS 19 DWH-Ahsan Abdullah

Materialized views 20 DWH-Ahsan Abdullah