EXPLAIN PLAN   «Prev  Next»

Detecting Index during Oracle Range Scans - Exercise

Analyze an EXPLAIN PLAN output

Objective: Interpret an EXPLAIN PLAN output.

Background and Overview

You have been asked to investigate a suspicious SQL statement that the end user complains is taking too long to execute. Your task is to inspect the EXPLAIN PLAN output that the developer has provided and make a tuning recommendation.

Instructions

The EXPLAIN PLAN output is shown below. While it indicates a full-table scan, you need to make sure that the full table scan is causing a slowdown. You have checked the DBA_INDEXES view and verified that there are no indexes that could be used to service this query, and you are considering creating an index on the BIRTH_COUNTRY column. 

Select
Student_name
From
Student
Where
Birth_country = ‘USA’;
  1. What questions should you ask the SQL developer? Are there any queries that you can run to determine if an index will help? Prepare a short statement about what you could try next.
  2. Write an SQL query that will display the number of distinct values in birth_country and counts of the rows for each value.

Submitting your Exercise

Once you have answered the question and created your SQL statement in the text box, click the Submit button to submit the exercise.