Starting from:
$25
CSE111-Lab 8 Indexes Solved
In this you will learn how to work with indexes. You will investigate the effect indexes have on query execution time and on data modification operations. You have to do the following:
1. Create new tables in your TPCH database:
• customer index, customernoindex with exactly the same schema as customer
• supplier index, suppliernoindex with exactly the same schema as supplier
• lineitem index, lineitemnoindex with exactly the same schema as lineitem
• orders index, orders noindex with exactly the same schema as orders
2. Create the following indexes:
• customer name index on c name attribute from customer index table
• supplier acctbal index on s acctbal attribute from supplierindex table
• orders orderdate index on o orderdate attribute from orders index table
• lineitem discount index on l discount attribute from lineitem index table
3. 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)
4. 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)
5. 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)
6. 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)
7. 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)
1. Create new tables in your TPCH database:
• customer index, customernoindex with exactly the same schema as customer
• supplier index, suppliernoindex with exactly the same schema as supplier
• lineitem index, lineitemnoindex with exactly the same schema as lineitem
• orders index, orders noindex with exactly the same schema as orders
2. Create the following indexes:
• customer name index on c name attribute from customer index table
• supplier acctbal index on s acctbal attribute from supplierindex table
• orders orderdate index on o orderdate attribute from orders index table
• lineitem discount index on l discount attribute from lineitem index table
3. 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)
4. 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)
5. 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)
6. 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)
7. 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)
1 file (31.6KB)
