Rule Based Optimizer   «Prev  Next»

Oracle Join Conditions and Join Order: Exercise

Objective: Write a correct customer-order join and explain what evidence is needed to evaluate its execution plan in Oracle 26ai.

This optional exercise replaces historical RBO table-positioning rules with modern join reasoning. There is no universally correct first or last table position that guarantees the best plan.

Self-assessment: 10 points

  • 4 points: use the correct join relationship, EAST filter, aliases, and requested columns.
  • 2 points: distinguish the 50,000 filtered customers from the 20,000 matching order rows.
  • 2 points: explain why these counts and FROM-clause position do not establish the best join order.
  • 2 points: identify relevant access-path information and representative execution evidence needed to evaluate a plan.

This is a self-assessment, not an automatic SQL grader. Compare your answer with the model solution. Submitted SQL is displayed as text and is not executed.

Background

Return the customer name and order number for each order belonging to a customer in the EAST region. Assume these ordinary, unquoted table and column names:

  • CUSTOMER: CUSTOMER_ID is the primary key; CUSTOMER_NAME and CUSTOMER_REGION are character columns.
  • ORDERS: ORDER_NUMBER uniquely identifies an order; CUSTOMER_ID is a non-null foreign key referencing CUSTOMER.CUSTOMER_ID.

Use ORDERS for this exercise. The legacy name ORDER conflicts with SQL keyword usage. Each order belongs to one customer; a customer may have zero, one, or many orders. The schema is illustrative and need not exist to write your answer.

The following incomplete query filters customers but fails to relate each order to its customer:

SELECT c.customer_name, o.order_number
FROM customer c, orders o
WHERE c.customer_region = 'EAST';

Assume the following exact counts describe one consistent dataset. They are supplied facts, not optimizer estimates or benchmark results:

  1. ORDERS contains 100,000 rows.
  2. CUSTOMER contains 500,000 rows.
  3. 50,000 customers are in the EAST region.
  4. 20,000 order rows belong to EAST customers.

Tasks

  1. Correct the query. Use an explicit JOIN with its ON condition and the EAST filter. Return CUSTOMER_NAME and ORDER_NUMBER, with one row per qualifying order. Explain the error in the incomplete query.
  2. Evaluate the join-order claim. Explain what the 50,000 and 20,000 counts represent. Can you conclude that ORDERS must drive the join because 20,000 is smaller than 50,000? State whether putting ORDERS first or last in FROM guarantees a modern Oracle join order. Identify information and execution evidence you would need before recommending a plan.

Hints

The relationship uses CUSTOMER_ID. The region attribute belongs to CUSTOMER, so the 20,000 qualifying orders are identified through that relationship. Distinguish final result cardinality from the work needed to find those rows. In a nested loops plan, the outer row source drives access to the inner row source; other join methods have different mechanics. No hint is required for this exercise.

Submit your response

Enter your SQL and explanation for both tasks in the single box below.