Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.
Which three statements are true regarding the execution plan?
A.
The view operator collects all rows from a query block before they can be processed but higher
operations in the plan.
B.
The in-line query in the select list is processed as a view and then joined.
C.
The optimizer pushes the equality predicate into the view to satisfy the join condition.
D.
The optimizer chooses sort-merge join because sorting is required for the join equality
predicate.
E.
The optimizer chooses sort-merge join as a join method because an equality predicate is used
for joining the tables.
Explanation:
E is incorrect:
https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94690
A hash join requires one hash table and one probe of this table, whereas a sort merge join requires two sorts. The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:
The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, , or >=.
In contrast to sort merges, hash joins require an equality condition.
Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.
If an index exists, then the database can avoid sorting the first data set. However, the database always sorts the second data set, regardless of indexes.
A,B are correct
C not correct because no push predicate occured in execution plan. If push predicate had been used, we’ve obtained the following execution plan:
SQL> select /*+ push_pred(v) */ v.*,d.department_name from
2 (SELECT department_id, sum(salary) sum_sal from hr.employees group by department_id) v, hr.departments d where v.department_id = d.department_id;
11 ligne(s) sÚlectionnÚe(s).
Plan d’exÚcution
———————————————————-
Plan hash value: 1905630447
—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | 11 | 319 | 57 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 11 | 319 | 57 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | SORT AGGREGATE | | 1 | 7 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |
—————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – filter(COUNT(*)>0)
7 – access(“DEPARTMENT_ID”=”D”.”DEPARTMENT_ID”)
D should be correct because index on departement is accessed and it’s already sorted. So the seconde set (employee) needs to be sorted too in order to merge.
E is not correct (explain in the previous comment)