CS634 – Homework 2

Released Feb 8, Due Wed., Feb. 21 (20 points)

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 for our main text.

Each student now has an additional Oracle account joex for user joe with the additional privilege given by the DBA using, for example:
grant select_catalog_role to joex;

This allows you to query the system tables such as v$session, to see other users logged in, etc.  To see current users:
select distinct username from v$session;

Problem 1 Using Indexes for simple queries. Best to do this early to have Oracle to yourself when timing.  Check for other use by doing the above query on v$session. In Oracle on dbs3, a large table setq_db.bench250 is public for querying and large enough to take over a minute to scan, as shown in class. The smaller table setq_db.bench is also there, and easily fits in memory. They have the same columns.

a. Find out its columns using describe--show the output. Also find out how many rows each table has.
b. Find out what indexes are on the larger table, and which one is unique. This information is available in the all_indexes catalog view. Find its columns using "describe all_indexes." Show your query on all_indexes and its results.  Find how many (8KB) blocks the table has (from table all_tables), and thus calculate how many GB of table data there is.
c. Write a query that needs a full scan of the larger table, but only returns a small amount of output. 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 (actually a hardware RAID set). 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 (along with the larger 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.