ORACLE / SQL: To perform the following activity, refer to the tables in the Just
ID: 3729593 • Letter: O
Question
ORACLE / SQL:
To perform the following activity, refer to the tables in the JustLee Books database.
Using the training you have received and speculating on query needs, determine appropriate uses for indexes and sequences in the JustLee Books database. Assume all tables will grow quite large in the number of rows. Identify at least three sequences and three indexes that can address needed functionality for the JustLee Books database. In a memo to management, you should identify each sequence and index that you propose and the rationale supporting your suggestions. You should also state any drawbacks that might affect database performance if the changes are implemented. (Book: Oracle 12c: SQL by Casteel; Chapter 6, Advanced Challenge)
Explanation / Answer
Answer:
Index: Indexes defined as shown below will help as mentioned therein.
a) Clustered Index on Customer# On CUSTOMERS Table: As this table will mostly be involved in the selection queries, this index will help in easy and fast retrieval of data. Usually selections from this master table will involve data sorted on Customer#. A clustered index will have the column physically sorted thereby reducing the overhead of sorting by the optimizer after data from a large customers table is selected.
b) Index on ZIP Column of CUSTOMERS table: This will also help in easy retrieval of records from Customer table based on the area codes. In the absence of such an index, the selection process will involve large table scan requiring the optimizer to scan all rows of table and then sort based on the area code.
c) An index on Category column of BOOKS table: This will help in the easy selection of books based on the category. This sort of selection queries could be a very common and frequent. Hence, indexing of this column will definitley add to the enhanced performance. Adding any columns, that are usual candidates of the queries, to an index is always advisable.
d) An index each on Customer#, Orderdate, Shipdate and ShipZip columns of the ORDERS table: Assuming that most queries target the columns Customer#, Orderdate, Shipdate and ShipZip, indexing these columns will help in improving the performance as the table scan can be reduced.
Drawbacks of having these indexes:
Though the indexes defined above enhances the performance, there are still some drawbacks associated with these.
a) Disk Space: Creation of indexes mean that the DB engine has to store the indexes separately thereby affecting the disk space which also involves the cost overhead.
b) DML operation performance: Though indexes help in selecting the data faster, it also affect the performance when there are multiple deletions or insertions happening on tables. For example, an update query which changes a column which has been indexed, involves modifying the index table by deleting the exisitng index row and creating (inserting) a new row. On large tables where frequent modifications happen, this will have considerable effect on the performance.
Sequences:
Since creating sequences help in generating numbers automatically by the system, these can be used for the columns that are defined as Primary Keys.
a) Order# in the ORDERS table can have a sequence to generate the values for it. As orders be placed by multiple users, this will ensure that each user gets a unique Order#.
b) The Customer# column of CUSTOMERS table can have a sequence to generate values for it.
c) The ISBN column in Books table can also have a sequence.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.