1 / 11

15.6 Index Based Algorithms

15.6 Index Based Algorithms. Akshay Reddy Chada CS 257. Contents. Clustering and non-clustering indexes Index based Selection Joining using an index Joining using a sorted index. Algorithms are useful for the selection operator.

magnar
Download Presentation

15.6 Index Based Algorithms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 15.6 Index Based Algorithms Akshay Reddy Chada CS 257

  2. Contents • Clustering and non-clustering indexes • Index based Selection • Joining using an index • Joining using a sorted index

  3. Algorithms are useful for the selection operator. • In a clustered relation tuples are packed roughly as few blocks, as they can possibly hold those tuples.

  4. Clustering And Nonclustering Indexes • Clustering indexes are on an attribute or attributes such that all the tuples with a fixed value for the search key of this index appear on roughly as few blocks as can hold them. • A relation that isn’t clustered cannot have a clustering index

  5. Index-based Selection • For a selection σC(R), suppose C is of the form a=v, where a is an attribute • For clustering index R.a: the number of disk I/O’s will be B(R)/V(R,a)

  6. Index-based Selection • The actual number may be higher: 1. index is not kept entirely in main memory 2. they spread over more blocks 3. may not be packed as tightly as possible into blocks

  7. Example • B(R)=1000, T(R)=20,000 number of I/O’s required: • 1. clustered, not index 1000 • 2. not clustered, not index 20,000 • 3. If V(R,a)=100, index is clustering 10 • 4. If V(R,a)=10, index is nonclustering 2,000

  8. Joining by using an index • Natural join R(X, Y) S S(Y, Z) Number of I/O’s to get R Clustered: B(R) Not clustered: T(R) Number of I/O’s to get tuple t of S Clustered: T(R)B(S)/V(S,Y) Not clustered: T(R)T(S)/V(S,Y)

  9. Example • R(X,Y): 1000 blocks S(Y,Z)=500 blocks Assume 10 tuples in each block, so T(R)=10,000 and T(S)=5000 V(S,Y)=100 If R is clustered, and there is a clustering index on Y for S the number of I/O’s for R is: 1000 the number of I/O’s for S is10,000*500/100=50,000

  10. Joining Using a Sorted index • Natural join R(X, Y) S (Y, Z) with index on Y for either R or S • Example: relation R(X,Y) and R(Y,Z) with index on Y for both relations search keys (Y-value) for R: 1,3,4,4,5,6 search keys (Y-value) for S: 2,2,4,6,7,8

  11. Joining using a sorted index • Used when the index is a B-tree, or structure from which we easily can extract the tuples of a relation in sorted order.

More Related