1.多表连接
1.1建立所有索引的查询
SQL> SELECTemp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,
2 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name
3 from hr.employeesemp,hr.employees mgr,hr.departments d,hr.locations l,hr.jobs j
4 where l.city=:loc
5 andemp.manager_id=mgr.employee_id
6 andemp.department_id=d.department_id
7 and d.location_id=l.location_id
8 and emp.job_id=j.job_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1518052872
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2580 | 12 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 15 | 2580 | 12 (9)| 00:00:01 |
|* 2 | HASH JOIN | | 15 | 2400 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID |EMPLOYEES | 4 | 264 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 15 | 1995 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 268 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 48 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN |LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 76 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN |DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL |JOBS | 19 | 513 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL |EMPLOYEES | 107 | 1284 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
1 -access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")
2 -access("EMP"."JOB_ID"="J"."JOB_ID")
7 -access("L"."CITY"=:LOC)
9 -access("D"."LOCATION_ID"="L"."LOCATION_ID")
10 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
1.2没有索引的查询
SQL> SELECTemp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,
2 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name
3 fromhr.employees2 emp,hr.employees2 mgr,hr.departments2 d,hr.locations2 l,hr.jobs2j
4 where l.city=:loc
5 andemp.manager_id=mgr.employee_id
6 andemp.department_id=d.department_id
7 andd.location_id=l.location_id
8 andemp.job_id=j.job_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1871751231
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 17 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 277 | 17 (12)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 250 | 13 (8)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 224 | 10 (10)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN| | 25 | 4525 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL |LOCATIONS2 | 1 | 74 | 3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 107 | 11449 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES2 | 107 | 11449 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DEPARTMENTS2| 27 | 1161 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | JOBS2 | 19 | 494 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL |EMPLOYEES2 | 107 | 2889 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
1 -access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")
2 -access("EMP"."JOB_ID"="J"."JOB_ID")
3 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"AND
"D"."LOCATION_ID"="L"."LOCATION_ID")
5 -filter("L"."CITY"=:LOC)
全部通过全表扫描进行搜索
1.3建立第一个索引
根据之前的执行计划,可以在locations2的city上简历索引
SQL> create indexl_city_ind on locations2(city);
Execution Plan
----------------------------------------------------------
Plan hash value: 2475540708
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 16 (13)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 277 | 16 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 250 | 12 (9)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 224 | 9 (12)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 25 | 4525 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| LOCATIONS2 | 1 | 74 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | L_CITY_IND | 1 | | 1 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 107 | 11449 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL |EMPLOYEES2 | 107 | 11449 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL |DEPARTMENTS2 | 27 | 1161 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL |JOBS2 | 19 | 494 | 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL |EMPLOYEES2 | 107 | 2889 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
1 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")
2 -access("EMP"."JOB_ID"="J"."JOB_ID")
3 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"AND
"D"."LOCATION_ID"="L"."LOCATION_ID")
6 -access("L"."CITY"=:LOC)
1.4建立第二个索引:
根据对上一个执行计划的分析,可以在deparments2的LOCATION_ID上建立索引
SQL> create indexdepart_id_ind on DEPARTMENTS2(LOCATION_ID);
Execution Plan
----------------------------------------------------------
Plan hash value: 2878015583
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 277 | 13 (8)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 251 | 10 (10)| 00:00:01 |