Software Development and CASE Tools - Download Now PowerPoint

							              Lecture 4




           • Index Structures

           • SQL




DBST 652                        4-1
              Index - Small Group


• Assume you have B+ trees on your table

• You are asked to put an index on a new column

• What are the advantages and disadvantages of
   using another index?




DBST 652                                          4-2
              Small Group Exercise


• You have 100 values in the data (1-100)

• You can store 2 values per index record

• There is the “root” level and one “leaf” level

• The third level is the level that points to the data

• Lay out the index

• See p. 480 for help, but don’t leave empty slots

DBST 652                                                 4-3
                       More...


• Values 10 thru 30 are deleted.

• Rebalance the tree




DBST 652                           4-4
                   Small Group


• You are asked to assess a new feature on an
   index

• The locking protocol has been changed to lock
   the whole index tree when it needs to rebalance

• Would this help or hurt your performance?




DBST 652                                             4-5
               Small Group Exercise


• You have B+ tree of order p=4(with root, leaf, and
   data pointer levels)

• Each data block holds 10 records and the file for
   the table is sorted.

• Rows 1 thru 160 are inserted into the table

• draw the B+ index and the file


DBST 652                                              4-6
               Small Group cont.


• Now rows 20 thru 39 are deleted

• Rows 161 thru 200 are added

• Show the data file and the B+ tree now




DBST 652                                   4-7
                     Agenda


           • Single-level Ordered Indexes

           • Multilevel Indexes

           • Dynamic Multilevel Indexes

           • Loading data

           • SQL Queries



DBST 652                                    4-8