Rule Based Optimizer   «Prev  Next»

Oracle WHERE-Clause Selectivity and Indexes: Exercise

Objective: Inspect indexed columns and data distribution, and explain why rearranging WHERE predicates does not prescribe execution order in Oracle 26ai.

This optional exercise replaces historical predicate-ordering advice with evidence-based reasoning. Complete both tasks in the single answer box.

Self-assessment: 10 points

  • 3 points: identify indexed columns and their positions for STUDENT.
  • 2 points: count distinct non-null values in both columns.
  • 3 points: count all rows, BLUE matches, SWEDEN matches, and rows matching both.
  • 2 points: explain selectivity, the limits of these counts, and why predicate order does not guarantee execution order.

No automatic SQL grading occurs. Compare your response with the model solution and award points using this rubric. Submitted SQL is displayed as text, not executed.

Background and assumptions

Assume your current schema owns an ordinary table named STUDENT, created with unquoted identifiers. EYE_COLOR and NATIONAL_ORIGIN are character columns, and the example values are stored as uppercase text. For index inspection, consider ordinary column indexes, not function-based indexes. You may write the SQL without running it if this training table is unavailable.

SELECT s.last_name
FROM student s
WHERE s.eye_color = 'BLUE'
  AND s.national_origin = 'SWEDEN';

Both conditions must be true. Their textual order does not guarantee evaluation order or index choice. Do not infer this table's distributions from assumptions about a country's population.

Tasks

  1. Inspect indexes. Write a query listing index name, column name, and column position for indexes on STUDENT. Explain how the output shows whether EYE_COLOR and NATIONAL_ORIGIN occur in an index, separately or in a composite index. Do not create or disable indexes.
  2. Measure data distribution. Write SQL that returns the number of distinct non-null values for each column. Also count total rows, rows with BLUE eyes, rows with SWEDEN as national origin, and rows matching both filters. Explain how to identify the more selective individual filter from these counts, why distinct-value counts alone are insufficient, and why moving that filter in the WHERE clause is not a tuning guarantee.

Hints

Use USER_IND_COLUMNS for indexed column names and positions in your schema. USER_INDEXES describes indexes, but does not list their component column names. COUNT(DISTINCT column) counts distinct non-null values. COUNT(*) counts rows; conditional COUNT expressions can count matching subsets. No sample data or measured execution plan is supplied, so do not invent numeric results or claim a faster access path.