Home » Oracle » 1z0-117 » Which two statements are true regarding the execution plan?
Examine the query and its execution plan:
Which two statements are true regarding the execution plan?
A. For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.
B. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.
C. The data is aggregated from the ORDERS table before joining to CUSTOMERS.
D. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.
Correct Answer: BD
Explanation/Reference:
B:An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Note:
*All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does not regain control until the stored procedure completes. You don’t care if you have to wait to get the first row if the last row gets back to you twice as fast. In a client server/interactive application you may well care about that.
*The optimizer uses nested loop joins to process an outer join in the following circumstances:
/ It is possible to drive from the outer table to inner table.
/ Data volume is low enough to make the nested loop method efficient.
*First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
