You execute the following query: Which statement is true about the usage of these hints in the query?

The correct answer is A. The optimizer pushes the join predicate into the inline view. The PUSH_PRED hint forces pushing of a join predicate into the view. SELECT /+ NO_MERGE(v) PUSH_PRED(v) / * FROM employees e, (SELECT manager_id WHERE e.manager_id = v.manager_id(+) AND e.employee_id = 100; When the PUSH_PRED hint is used without an argument, it should be…

Understanding and Influencing the Optimizer

Question

You execute the following query:

Which statement is true about the usage of these hints in the query?

Exhibit

Options

AThe optimizer pushes the join predicate into the inline view.
BThe optimizer evaluates the subquery as a first and then filters out rows.
CThe optimizer performs a join operation first and then filters out the rows.
DThe hint will have no effect because one of the join resultsets is an inline view.

How the community answered

(17 responses)

A
71% (12)
B
6% (1)
C
6% (1)
D
18% (3)

Explanation

The PUSH_PRED hint forces pushing of a join predicate into the view. SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ * FROM employees e, (SELECT manager_id WHERE e.manager_id = v.manager_id(+) AND e.employee_id = 100; When the PUSH_PRED hint is used without an argument, it should be placed in the view query block. When PUSH_PRED is used with the view name as an argument, it should be placed in the surrounding query.

Topics

#join predicate push-down#inline view#query hints#optimizer transformation

Community Discussion

No community discussion yet for this question.

Full 1Z0-117 Practice