Homework assignment #5
CS 643, Spring 2002
Due: Wednesday, March 13, 2002
Total points: 100

In class, You're be asked to perform the following exercises with the tables in the SH schema. Save operations in "queries.sql". In addition, save the result in "result.txt". Submit both files along with your homework report. Make sure that you give proper documentation. This section can be shared by two students.

  1. Find and execute "UTLXPLAN.SQL" so that PLAN_TABLE can be created to record execution plans. 
  2. Use the "EXPLAIN PLAN FOR" statement or "SET AUTOTRACE TRACEONLY" to view the execution plan for the following queries. 
  1. Write a query on a table of your choice. Specify the condition as a value of an index attribute.
  2. Write a query on a table of your choice. Specify the condition as a value of a non-index attribute.
  3. Write a query on a table of your choice. Specify the condition as either greater than or less than a value of an index attribute.
  4. Write a query on a table of your choice. Specify the condition as either greater than or less than a value of a non-index attribute.
  5. Write a query of equijoin between two tables. Specify additional condition on one of or both of the tables to see if there's any change of the execution plan.
  6. Write a query of non-equijoin between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  7. Write a query of outer join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  8. Write a query of full outer join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  9. Write a query of Cartesian join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  10. Write a query of anti-join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  11. Write a query of semi-join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  12. Write a star query between a fact table and two lookup tables. Specify additional condition on either type of the tables to see if there's any change of the execution plan.
  1. Apply various optimizer hints to some of the above queries to see if there's any change of the execution plan.

For the homework assignment, you're asked to answer the following questions. This section needs to be individual work.

  1. Review Question 20.5
  2. Review Question 20.11
  3. Exercise 20.15
  4. Exercise 20.17 (b)
  5. Exercise 20.18 - S1, J5, P3

Back to CS643 schedule