• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Query Performance Optimization
  5. Optimization Cases
  6. Case: Creating an Appropriate Index

Case: Creating an Appropriate Index

Symptom

Query the information about all personnel in the sales department.

SELECT staff_id,first_name,last_name,employment_id,state_name,city 
FROM staffs,sections,states,places 
WHERE sections.section_name='Sales' 
AND staffs.section_id = sections.section_id 
AND sections.place_id = places.place_id 
AND places.state_id = states.state_id 
ORDER BY staff_id;

Optimization Analysis

The original execution plan is as follows before creating the places.place_id and states.state_id indexes:

The optimized execution plan is as follows (two indexes have been created on the places.place_id and states.state_id columns):