Oracle 26ai · SQL Tuning · Module 8

Module 8

Optimizer Statistics: Exercise

Objective: Prepare a bounded statistics-maintenance plan for a training schema.

Assume you own the ordinary, nonpartitioned STUDENT, REGISTRATION, and CLASS tables and have permission to gather their statistics. They were created with unquoted names. Do not assume index names prove which columns are indexed.

SELECT s.student_name, c.class_name
FROM student s
JOIN registration r ON r.stu_num = s.stu_num
JOIN class c ON c.class_num = r.class_num;

Tasks

  1. List indexed columns and collection metadata for these three tables.
  2. Write DBMS_STATS calls limited to the three training tables, using automatic sampling and an appropriate index-statistics policy.
  3. Explain how you would check collection and query behavior, and why these calls are not a universal instruction to refresh all objects.

Write an answer without running it if the training schema is unavailable. Do not submit credentials or production data.

Self-assessment: 10 points

Award 3 points for index/statistics inspection, 4 for bounded DBMS_STATS calls, and 3 for verification and maintenance reasoning. No automatic SQL grading is performed.

Your answer is not stored or sent to a tutor. It is displayed safely on the result page. SQL is not executed.