Improving Query Performance Using Indexes in TPCHR Database

Task 3 Indexing

Task 3 Indexing
a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR.
An index ordersIdx(o_orderStatus, o_clerk, o_totalPrice) has been created over the relational table

ORDERS.
Construct SELECT statements that will use the index ordersIdx in the following ways:
i. Execution of the first SELECT statement must traverse the index vertically and it MUST NOT access a relational table ORDERS.
ii. Execution of the second SELECT statement must traverse the index vertically and then horizontally at the leaf level of the index and it MUST NOT access the relational table ORDERS.
iii. Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it MUST NOT access the relational table ORDERS.
iv. Execution of the fourth SELECT statement must traverse the index vertically and it MUST access the relational table ORDERS.
v. Execution of the fifth SELECT statement must traverse the index vertically and then horizontally and it MUST access a relational table ORDERS.

Deliverables
A file Task3a.pdf with CREATE INDEX statements that improve the performance of the queries listed (i, ii, iii, iv, and v above) and the execution plan showing how the queries uses the created index to speed up the queries. Use ‘Explain plan for …’ to generate the execution plan for each of the SELECT statements that you have proposed and created. Next use the ‘SELECT * FROM TABLE(dbms_xplan.display) to display the generated execution plan to show that your proposed
SELECT statements indeed perform according to the specification.
b. Referring to the same TPCHR conceptual schema, we would like to improve performance of the following group of queries:
i. Find the total number of orders that include at least one item (relational schema LINEITEM) with tax rate = 0.1 from orders made in year ‘1998’.
ii. Find the comment (L_COMMENT) of all items (relational schema LINEITEM) included in the orders that were handle by a clerk with the number ‘clerk#000000136’.
iii. Find the names of customers who submitted orders in a given year.

Find index that speeds up processing of the statements in the best possible way listed above, and create the index. You are only allowed to create ONE index per query. The best possible way means the database system will execute a query and uses the index proposed by you.

Use ‘Explain plan for …’ to generate the execution plan for each of the SELECT statements that you have proposed and created. Next use the ‘SELECT * FROM TABLE(dbms_xplan.display) to display the generated execution plan to show that your proposed SELECT statements indeed perform according to the specification.