Exercise 10

1 Query Processing

Exercises 12.2, 12.3, 12.4 and 12.5 from "Database System Concepts".

2 Plan Generation and Interpretation in Oracle

Generate the execution plans for the queries you evaluated in exercise 11. To do so you have to generate a table plan_table first. The corresponding DDL statement is available in the file create_tables.sql.

2.1 Query Plans

For each query of Exercise 9 determine the access plan Oracle generates. A sample explain statement is
  EXPLAIN PLAN SET statement_id = 'Q1a' for
    SELECT unique2 
    FROM r2 s
    WHERE s.unique1=8530;
The explain statements can be found in the file queries.sql.

Compare the access plans with the explanations you came up with in exercise 11.

2.2 The ANALYZE Statement

Update the statistics for tables r1 and r2. Use the ANALYZE command for this purpose, e.g. ANALYZE TABLE r COMPUTE STATISTICS;

Now generate again access plans and compare them with the previous ones.

Best regards,
Kristian Torp