Rule Based Optimizer   «Prev  Next»

Positioning statements in WHERE clause - Exercise

Change Order of items in WHERE clause

Objective:Sequence items in the WHERE clause.

Exercise scoring

This exercise is worth a total of 10 points. To receive full credit, you'll need to write a query that correctly orders items in the WHERE clause. Once you have completed your answer, you'll submit your answer.

Background

Here is a query that looks for students who have blue eyes and who are from Sweden.
SELECT
last_name
FROM
STUDENT
WHERE
eye_color = ‘BLUE'
AND
national_origin = 'SWEDEN';

Your job as DBA is to tune this query to ensure that the Boolean predicates are in their proper order from least restrictive to most restrictive.

Submitting your exercise

Enter your queries in the two text boxes below and click Submit button to submit your responses. Remember, you must fill in both text boxes before clicking the Submit button.

Instructions


For this exercise, you need to perform the following tasks:
  1. Write a query to verify that indexes exist for the eye_color and national_origin columns of the student table.

  1. Write an SQL query that counts the number of distinct values for eye_color and national_origin.

Hints

Oracle keeps indexes column information in the DBA_INDEXES view. DBA_INDEXES also contains a TABLE_NAME column.