CS634 – Homework 2

Released Feb 8, Due Mon., Feb. 22

Intro to Indexes

 

Instructions: The homework is due BEFORE CLASS on the duedate. Please hand in paper copies (either typeset or hand-written copies are fine, as long as the hand writing is clear).

Note that some of these are solved online in the student answer set.

Problem 1 Using Indexes for simple queries. Best to do this early to have Oracle to yourself when timing.  Check for other use by using the "uptime" command. Here is what it looks like when dbs2 is idle: the last three numbers are measures of system load currently, over the last minute, the last 5 minutes, and the last 15 minutes. They should be under 0.1 when you start to do measurements.

dbs2(17)% uptime
  2:36pm  up 83 day(s), 16:23,  2 users,  load average: 0.00, 0.02, 0.03

In Oracle on dbs2, a large table eoneil.bench is public for querying and large enough to take seconds to scan.
a. Find out its columns using describe--show the output. Also find out how many rows the table has.
b. Find out what indexes are on the table, and which one is unique. This information is available in the all_indexes catalog table. Show your query and its results.  Find how many (8KB) blocks the table has (from table all_tables), and thus calculate how many MB of table data there is.
c. Write a query that needs a full scan of the table. Show your query and give its time, by setting "set timing on" in sqlplus.  From this and the size from part b., calculate the average transfer rate from disk. Note this is using the sequential layout of table data on disk, so there are few seeks during the access.
d. Write a simple query that can use the index on K500K (and the table itself) and time it as in c. For better timings, run a full-scan query before this one, to flush out the buffer pool (itself smaller than the table data size).
e. Write the similar query on column K250K (which has no index) and time it as in c, after running the full-scan query. Explain what's happening in this case.

Problem 2

Exercise 8.4 in the textbook. Ignore the "order of entries" question, since it seems unclear.  Show the first data entry and the last. Use RIDs (1,1), (1,2), (1,3), (2,1), and so on.

Problem 3

Exercise 8.10 in the textbook

Problem 4

Exercise 9.5 in the textbook

Problem 5

Exercise 9.6 in the textbook.

Problem 6

Exercise 10.2 in the textbook (only questions 1-5).

Problem 7

Exercise 10.8 in the textbook.