RelationalDBDesign
SiteMap
Advanced Tuning
SQL Tuning
Perform Tuning
Tuning Instance
Tuning Basics
External Environment
Rule Based Optimizer
«Prev
Next»
Oracle SQL Tuning
Tuning Oracle SQL
Extension ANSI SQL
SQL Tuning Tool Set
Executing Oracle SQL
Explain Plan Utility
SQL Optimizer Modes
Rule Based Optimizer
SQL Tuning Hints
ANSI SQL Extensions
Built in Functions
Oracle null Value BIF
Decode Function
Supplied Extensions
Extensions Conclusion
SQL Tuning Tools
DBA SQL Roles
Developers Write SQL
Inside Stored Procedures
SQL Tuning Tools
Explain Plan Utility
SQL Declarative Language
Oracle Plan Table
Explain Plan Statement
Full Table Scan
Diagnose Table Joins
Non Correlated Subqueries
Index Range Scans
Analyze Explain Plan
SQL Explain Plan Conclusion
SQL Optimizer Modes
Optimizer Modes
Default Optimizer Mode
SQL Optimizer Modes
Rule Based Optimizer
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:
Write a query to verify that indexes exist for the eye_color and national_origin columns of the student table.
Submit
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.