Indexing

1. Indexing

2. Indexing • Index ? What is it ?

3. Indexing • Index ? What is it ? A database index is a persistent memory data structure that improves efficiency of data lookup to rows of a table by a keyed access method

4. Indexing • Why do we need it ?

5. Indexing • Why do we need it ? The objective of indexing in database systems is to improve performance of query processing

6. Indexing • Does it have any impact on the other database operations ?

7. Indexing • Does it have any impact on the other database operations ? YES !!!

8. Indexing • Does it have any impact on the other database operations ? YES !!! Indexing decreases (!!!) performance of insert update and delete operations

9. Indexing • Does it have any impact on the other database operations ? YES !!! Indexing decreases (!!!) performance of insert update and delete operations Poorly designed indexing schema maydecrease performance of query processing

10. Indexing • Sequential search vs. index based search

11. Indexing • Sequential search vs. index based search disk storage Supplier ...

12. Indexing • Sequential search vs. index based search disk blocks disk storage Supplier ...

13. Indexing • Sequential search vs. index based search disk blocks disk storage Supplier ... rows (tuples)

14. Indexing • Sequential search vs. index based search All rows such that city = ‘Sydney’ Supplier ...

15. Indexing • Sequential search vs. index based search All rows such that city = ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’

16. Indexing • Sequential search vs. index based search All rows such that city = ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’ To find all suppliers from Sydney we have to read entire table - all data blocks !

17. Indexing • Sequential search vs. index based search Index on attribute city All rows such that city = ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’

18. Indexing • Sequential search vs. index based search Index on attribute city All rows such that city = ‘Sydney’ ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’

19. Indexing • Sequential search vs. index based search Index on attribute city All rows such that city = ‘Sydney’ ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’ To find all suppliers from Sydney we have to read 4 data blocks + index data blocks (<4)

20. Indexing • Syntax

21. Indexing • Syntax CREATE INDEX empx ON Supplier( city );

22. Indexing • Syntax CREATE INDEX empx ON Supplier( city ); index name

23. Indexing • Syntax CREATE INDEX empx ON Supplier( city ); index name relational table name

24. Indexing • Syntax CREATE INDEX empx ON Supplier( city ); index name relational table name attribute name(s)

25. Indexing • Index selection problem

26. Indexing • Index selection problem Find a smallest set of indexed attributes that maximises the search benefits and minimises the costs of insert, update, delete operations

27. Indexing • “ To index or not to index ? That’s the question ! ”

28. Indexing • “ To index or not to index ? That’s the question ! ” • Indexing an attribute which is NOT USED in WHERE clauses entails overhead and yields no benefits

29. Indexing • “ To index or not to index ? That’s the question ! ” • Indexing an attribute which is NOT USED in WHERE clauses entails overhead and yields no benefits • Indexing an attribute which is frequently updated may cause huge time overhead

30. Indexing • “ To index or not to index ? That’s the question ! ” • Indexing an attribute which is NOT USED in WHERE clauses entails overhead and yields no benefits • Indexing an attribute which is frequently updated may cause huge time overhead • Indexes on small tables can do more harm than good

31. Indexing • “ To index or not to index ? That’s the question ! ” • Indexing an attribute which has low selectivityis a bad idea

32. “ To index or not to index ? That’s the question ! ” • Indexing an attribute which has low selectivityis a bad idea Selectivity of attribute A = total number of values in the domain of A total number of rows

33. Indexing • Implementation of indexes

34. Indexing • Implementation of indexes • B-Tree based

35. Indexing • Implementation of indexes • B-Tree based • Hash based

36. Indexing • Few interesting facts

37. Indexing • Few interesting facts • All primary keys are automatically indexed

38. Indexing • Few interesting facts • All primary keys are automatically indexed • Height of B-Tree index is not larger than 4

39. Indexing • Few interesting facts • All primary keys are automatically indexed • Height of B-Tree index is not larger than 4 • Implementation of hash-based index needs NO persistent storage

40. Indexing • Few interesting facts • All primary keys are automatically indexed • Height of B-Tree index is not larger than 4 • Implementation of hash-based index needs NO persistent storage • Hash-based index is better than B-tree based index if all (majority) of index keys are know in advance

41. Indexing • Non-clustered vs. clustered indexes

42. Indexing • Non-clustered vs. clustered indexes non-clustered B-tree index . . . . . .

43. Indexing • Non-clustered vs. clustered indexes clustered B-tree index non-clustered B-tree index . . . . . . ..... . . . . . .

44. Indexing • Non-clustered vs. clustered indexes Index on attribute city All rows such that city = ‘Sydney’ ‘Sydney’ Supplier ... SELECT * FROM Supplier\ WHERE city = ‘Sydney’ To find all suppliers from Sydney we have to read 2 data blocks + index data blocks (<4)

45. Clustering • Cluster ? What is it ?

46. Clustering • Cluster ? What is it ? A cluster is a group of tables that share the same data blocks because they share common columns and are often used together

47. Clustering • Why do we need it ?

48. Clustering • Why do we need it ? The objective of clustering in database systems is to improve performance of query processing and to remove redundant data

49. Clustering • Does it have any impact on the other database operations ?

50. Clustering • Does it have any impact on the other database operations ? YES !!!