Assume that the rows of a table, R, are stored in an unsorted heap file, F, having M pages.
(a) If no index is available, how many I/O operations on average will be required to com- pute the result of a SELECT statement with WHERE condition a = 100, where a is an attribute of R?
(b) If a secondary static hash index on attribute a is now added for accessing F, how would you calculate the cost of the query in (6a)?
(c) What is a clustered index? Is the index of (6b) clustered?
(d) Extendable hashing uses the concepts of global depth and local depth.
(a) If no index is available, how many I/O operations on average will be required to com- pute the result of a SELECT statement with WHERE condition a = 100, where a is an attribute of R?
Solution
If a is a key then at most one row satisfies condition. Thus if the row is in R then M/2 and if not then M. If a is not a key then any number of rows might satisfy the condition, hence all of F must be scanned and M I/O operations are required.
(b) If a secondary static hash index on attribute a is now added for accessing F, how would you calculate the cost of the query in (6a)?
Solution
The cost of the query is equal to the cost of finding the index entry in the overflow chain of bucket (which depends on how long the chain is and whether a is a key) plus one additional I/O operation for getting the row from F.
(c) What is a clustered index? Is the index of (6b) clustered?
Solution:
The rows in the data file are ordered in same way as index entries in index file. The index of (6b) not clustered.
(d) Extendable hashing uses the concepts of global depth and local depth.
i. Which of these two is related to the size of the directory?
Solution - global
ii. All entries in a bucket agree on the last (local or global?) depth bits of the result of the hash.
Solution - local
iii. If a bucket overflows, the directory might have to be expanded. What is the condition on local and global depth that determines whether expansion is required?
Solution - global depth = local depth of bucket that overflowed
iv. How would you calculate the I/O cost of the query in (6a) assuming an extendable hash index on attribute a has been created for F
Solution - Assuming the directory is not in memory: one I/O to get the directory page + one I/O to get the index entry from the bucket + one I/O to get the row from F
You might also like to view...
Match each term with the correct statement below.
A. A perimeter that incorporates all items that will be secured B. Explicit behaviors implemented to achieve a desired outcome C. The generic organizing and control principles that an organization uses to underwrite the management of its information function D. Assurance that the security actions of the organization directly support its goals E. The tendency for organizational activities to become disorganized over time due to competitive pressure and technological change F. The level of assessed competence of the management process G. Decision making that is supported by empirically derived data H. Quantitative measures of security performance I. The prescribed activities to achieve the long-range intentions of the organization
You can set up a WordPress website within minutes in Azure. The more time-consuming piece is which of the following?
A) Implementing the networking components B) Installing custom plug-ins and designing the “look and feel” of the site C) Removing Custom Style Scenes (CSS) D) Configuring the Linux distributions
In a producer/consumer relationship, the ________ portion of an application generates data and stores it in a shared object, and the ________ portion of an application reads data from the shared object.
a. consumer, producer b. producer, consumer c. outputter, inputter d. None of the above.
A video projector shows the image from a laptop, but everything is colored blue. Which of the following is the MOST likely the cause?
A. Dead pixels B. Video resolution C. Failing lamp D. VGA connection