Examine the query and its execution plan: Which two statements are true regarding the execution plan?

Question

Exhibit

Options

• AFor every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table
• BAn outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows
• CThe data is aggregated from the ORDERS table before joining to CUSTOMERS.
• DThe NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to

• A
  17% (11)
• B
  73% (46)
• C
  10% (6)

Explanation

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. * 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.

No community discussion yet for this question.