Please use the on-line submit procedure, as described in Homework 3 to turn in your PDA.
Also, please go to http://www-db.stanford.edu/~ullman/fcdb/oracle.html and look over the topics for which we provide information. We're not expecting you to memorize the list, but it pays to look at the titles once, so if you ever encounter a problem there is some chance your memory will be jogged, and you'll go look at this document again. Similarly, please peruse the tables of contents for the currently relevant documents: or-intro.html, or-faq.html, or-nulls.html, or-time.html, and or-nonstandard.html . Again, the idea is that if you've seen it once, you will probably remember its existence if you need it. Especially, the document on ways that Oracle differs from the SQL standard (or-nonstandard) will save a lot of aggrievation when you encounter things like "Oracle won't allow the word EXCEPT."
SELECT Bars.address FROM Drinkers, Bars WHERE Drinkers.name = 'fred' AND Drinkers.frequents = Bars.name;we might use an index on Drinkers.name to help us find the tuple for drinker Fred quickly. We might also like an index on Bars.name, so we can take all the bars Fred frequents and quickly find the tuples for those bars to read their addresses.
In Oracle, you can get an index by the command:
CREATE INDEX <IndexName> ON <RelName>(<Attribute List>) TABLESPACE INDX;Note:
If the attribute list for an index contains more than one attribute, then the index requires values for all the listed attributes to find a tuple. That situation might be helpful if the attributes together form a key, for example.
An illustration of the CREATE INDEX command is
CREATE INDEX DrinkerInd ON Drinkers(name) TABLESPACE INDX; CREATE INDEX BarInd ON Bars(name) TABLESPACE INDX;which creates the two indexes mentioned above. To get rid of an index, you can say DROP INDEX followed by the name of the index. Notice that each index must have a name, even though we only refer to the name if we want to drop the index.
In order to get Oracle to use its indexes, you often need to tell it to use the better of the two query optimizers that it has available, the so-called "cost-based optimizer." Before taking any measurements of query running time, you must say to sqlplus:
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
Now, you are ready to run your experiments. Create at least two useful indexes for your PDA. Run your queries from part (1) on your large database with the indexes and without the indexes. To time your commands, you may issue the following commands to sqlplus:
Naturally these times may be affected by external factors such as system load, etc. Still, you should see a dramatic difference between the execution times with indexes and the times without. Turn in a script showing your commands to create indexes, and showing the relative times of query execution with and without indexes.
Note: Often, students discover that indexes appear to slow down the execution of queries. There are two issues you should consider: