Given the following explain plan, describe the steps and their order of execution.
SELECT /*+ RULE */ c.course_no, c.description,
i.instructor_id
FROM course c, section s, instructor i
WHERE prerequisite = 30
AND c.course_no = s.course_no
AND s.instructor_id = i.instructor_id
Execution Plan
--------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'COURSE'
4 3 INDEX (RANGE SCAN) OF 'CRSE_CRSE_FK_I'
(NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SECTION'
6 5 INDEX (RANGE SCAN) OF
'SECT_CRSE_FK_I' (NON-UNIQUE)
7 1 INDEX (UNIQUE SCAN) OF 'INST_PK' (UNIQUE)
Solution:
This is a three-table join of the COURSE, SECTION, and INSTRUCTOR tables. The rule hint causes the rule-based optimizer to be used.
The first step executed in the execution plan is the access of the index CRSE_CRSE_FK_I. This index is based on the PREREQUISITE column and, therefore, retrieves the ROWIDs of those records that satisfy the condition WHERE prerequisite = 30. Then the rows with these ROWIDs are retrieved from the COURSE table.
The next step is a nested loop join with the SECTION table. For each of the retrieved COURSE rows, the index SECT_CRSE_FK_I is probed based on join criteria of s.course_no = c.course_no. Lastly, this result is now used for another nested loop join with the INSTRUCTOR index.
Note that the query only requires the use of the INSTRUCTOR_ID column, which is also the only column in the SELECT list. Therefore, only a lookup of the value in the index is required, not the INSTRUCTOR table.
Describe the steps of the following execution plan.
UPDATE enrollment e
SET final_grade =
(SELECT NVL(AVG(numeric_grade),0)
FROM grade
WHERE e.student_id = student_id
AND e.section_id = section_id)
WHERE student_id = 1000
AND section_id = 2000
0 rows updated.
Execution Plan
--------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
(Cost=2 Card=1 Bytes=47)
1 0 UPDATE OF 'ENROLLMENT'
2 1 INDEX (UNIQUE SCAN) OF 'ENR_PK' (UNIQUE)
(Cost=1 Card=1 Bytes=47)
3 0 SORT (AGGREGATE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'GRADE'
(Cost=3 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'GR_PK' (UNIQUE)
(Cost=2 Card=1)
Solution:
This SQL UPDATE statement is a correlated subquery. You can generate explain plans for SQL statements other than SELECT statements. The explain plan shows the cost-based optimizer is used to execute the statement. The WHERE clause of the UPDATE statement refers to the primary key columns, which in turn utilizes the primary key of the ENROLLMENT table (ENR_PK).
Then inner query utilizes the index GR_PK, the primary key index, and accesses the GRADE table via the ROWID. Because the subquery specifies the AVG function, the step SORT (AGGREGATE) is executed.
Note, you will not see a difference in the execution plan between a correlated UPDATE statement and an UPDATE statement with a non- correlated subquery. You need to keep in mind, however, that the correlated update will repeatedly execute the inner query for every row retrieved by the outer query. This is in contrast to the non- correlated subquery that executes the inner query only once. The correlated subquery combined with an UPDATE statement is a very fast way to update data without having to write a program to compute records for each step.
The following SQL statement has an error in the hint. Correct the statement so Oracle can use the hint.
SELECT /*+ INDEX (student stu_pk) */ * FROM student s WHERE last_name = 'Smith'
Solution:
The hint does not specify the table alias. When a table alias is used in the statement, the hint needs to reference the alias otherwise the hint is ignored. Correct the statement as follows:
SELECT /*+ INDEX (s stu_pk) */ /*
FROM student s
WHERE last_name = 'Smith'
Execution Plan
--------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=826 Card=5 Bytes=500)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STUDENT'
(Cost=826 Card=5 Bytes=500)
2 1 INDEX (FULL SCAN) OF 'STU_PK' (UNIQUE)
(Cost=26 Card=5)