Description
In this Lab session you will learn how to work with indexes. You will investigate the e ect indexes have on query execution time and on data modi cation operations. You have to do the following:
-
Create new tables in your TPCH database:
customer index, customer noindex with exactly the same schema as customer supplier index, supplier noindex with exactly the same schema as supplier lineitem index, lineitem noindex with exactly the same schema as lineitem orders index, orders noindex with exactly the same schema as orders
-
Create the following indexes:
customer name index on c name attribute from customer index table
supplier acctbal index on s acctbal attribute from supplier index table orders orderdate index on o orderdate attribute from orders index table lineitem discount index on l discount attribute from lineitem index table
-
Copy all the data from customer to customer index and customer noindex using a single INSERT statement. Measure the time each INSERT statement takes. Compare them. Do the same for the other 3 tables: supplier to supplier index and supplier noindex; lineitem to lineitem index and lineitem noindex; orders to orders index and orders noindex. Report the 8 execution times for the 8 INSERT statements. (8 execution times)
-
Create the following indexes:
customer mktsegment index on c mktsegment attribute from customer index table lineitem returnflag index on l returnflag attribute from lineitem index table orders priority index on o orderpriority attribute from orders index table
Measure the time it takes to create the indexes and report them. (3 execution times)
-
Execute the 15 queries from Lab 3 on the *** index tables and measure the execution time for each query. In other words, replace each table for which there is an equivalent index table with the index table. Execute the 15 queries from Lab 3 on the *** noindex tables and measure the execution time for each query. In other words, replace each table for which there is an equivalent noindex table with the noindex table. Report the query execution time for every query. (30 execution times)
-
Write an UPDATE statement that increases the discount by 0.05 for every line item. Execute this statement on lineitem index and lineitem noindex, respectively. Measure and report the execution times. (2 execution times)
-
Write an UPDATE statement that increases the account balance by 1000 for every supplier. Execute this statement on supplier index and supplier noindex, respectively. Measure and report the execution times. (2 execution times)
You are required to submit a le containing the execution times you obtain for the 45 statements and discuss the di erences you observe for the corresponding pair statements. (1 pt for every 3 execution times for a total of 15 points)
