SELECT Bars.address FROM Drinkers, Bars WHERE Drinkers.name = 'joe' AND Drinkers.frequents = Bars.name;we might use an index on Drinkers.name to help us find the tuple for drinker Joe quickly. We might also like an index on Bars.name, so we can take all the bars Joe 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 csindx;Note:
If the attribute list 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 csindx; CREATE INDEX BarInd ON Bars(name) TABLESPACE csindx;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.
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.
Author (firstName, lastName, address)
Book (title, copyright, firstName, lastName)
Edition (isbn, pubDate, listPrice, publisher, title, copyright)
Bookstore (name, address)
Stock (storeName, storePrice, isbn)
The Author relation give the author's first and last names and address. Each first name - last name pair is unique. The Book relation gives the title, copyright date, and the first and last names of the author. A book can have more than one author. The Edition relation give the ISBN number, publicatin date, list price recommended by the publisher, publisher name, and the title and copyright of the book the edition is of. Each edition has a unique ISBN number. The Bookstore relation gives the name and address of a book. The Stock relation give the bookstore name, the price, and the ISBN number of the edition. listPrice and storePrice are real numbers. copyright and pubDate are integers (representing the year). All other attributes are strings.