Examine the following command: Which query transformation technique is used by the optimizer in this case?

Question

Exhibit

Options

• AView merging
• BFilter push-down
• CPredicate pushing
• DPredicate move-around

• A
  17% (5)
• B
  7% (2)
• C
  73% (22)
• D
  3% (1)

Explanation

In predicate pushing, the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters. For example, suppose you create a view that references two employee tables. The view is defined with a compound query that uses the UNION set operator, as follows: CREATE VIEW all_employees_vw AS ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees ) ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers ); You then query the view as follows: SELECT last_name FROM all_employees_vw WHERE department_id = 50; Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the accessing statement by pushing its predicate, the WHERE clause condition department_id=50, into the view's compound query. The equivalent transformed query is as follows: SELECT last_name FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM WHERE department_id=50 SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers WHERE department_id=50 );

No community discussion yet for this question.