Queries that specify a range of values form select a

Info icon This preview shows pages 51–53. Sign up to view the full content.

View Full Document Right Arrow Icon
Queries that specify a range of values Form: select A 1 , A 2 , ..., A n from r where A i c and A i c
Image of page 51

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
48 Preferred Scheme: Indexing. Analysis: 1. Using an index structure, we can find the bucket for value c 1 , and then follow the pointer chain to read the next buckets in alphabetic (or numeric) order until we find c 2 . 2. If we have a hash structure instead of an index, we can find a bucket for c 1 easily, but it is not easy to find the next bucket in sorted order . Because: a. A good hash function assigns values randomly to buckets. b. Each bucket may be assigned many search key values, so we cannot chain them together. Conclusion: Index methods are preferable where a range of values is specified in the query. Multiple-Key Indices Problem with Multiple Single-Key Indices If there are two indices on account file, one on branch-name and one on balance , then suppose we have a query like: select loan-no from account where branch-name = Perryridge and balance = 1000 There are 3 possible strategies to process this query: 1. Use the index on branch-name to find all records pertaining to Perryridge branch. Examine them to see if balance = 1000. 2. Use the index on balance to find all records pertaining to accounts with balances of 1000. Examine them to see if branch-name = ‘Perryridge’. 3. Use the index on branch-name to find pointers to records pertaining to ‘Perryridge’ branch. Also, use the index on balance to find pointers to records pertaining to 1000. Take the intersection of these two sets of pointers. The third strategy takes advantage of the existence of multiple indices. This may still not work well if all the following conditions hold: a. There are a large number of Perryridge records AND b. There are a large number of 1000 records AND c. Only a small number of records pertain to both Perryridge and 1000. To speed up the intersection operation, special structures such as bitmap indices can be used. Advantages of Using Multiple-Key Indices Suppose we have an index on combined search-key ( branch-name , balance ). With the where clause where branch-name = ’Perryridge’ and balance = 1000 , the index on the combined search-key will fetch only records that satisfy both conditions. Using separate indices in less efficient – we may fetch many records (or pointers) that satisfy only one of the conditions. It can also efficiently handle where branch-name = ’Perryridge’ and balance < 1000 . However, it cannot efficiently handle where branch-name < ’Perryridge’ and balance = 1000 . For each value of branch-name that is less than “Perryridge” in alphabetic order, the system locates records with a balance value of 1000. However, each record is likely to be in a different disk block, because of the ordering of records in the file, leading to many I/O operations. To speed up the processing of general multiple search-key queries (involving one or more comparison operations), we can use special structures like grid file , R-tree etc.
Image of page 52
49 Index Definitions in SQL Creating an Index create [ unique ] index index-name on relation-name ( attribute-list ) The attribute-list is the list of attributes in relation r that form the search key for the index.
Image of page 53
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern