多表连接，复合索引，10053

来源：互联网发布：photoshop软件购买编辑：程序博客网时间：2024/06/05 02:21

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

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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 |

| 3 | MERGE JOIN CARTESIAN | | 29 | 4176 | 6 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID |DEPARTMENTS2 | 1 | 43 | 1 (0)| 00:00:01 |

| 5 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| LOCATIONS2 | 1 | 74 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN |L_CITY_IND | 1 | | 1 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | DEPART_ID_IND | 1 | | 0 (0)| 00:00:01 |

| 9 | BUFFER SORT | | 107 | 2889 | 5 (0)| 00:00:01 |

| 10 | TABLE ACCESS FULL |EMPLOYEES2 | 107 | 2889 | 3 (0)| 00:00:01 |

| 11 | TABLE ACCESS FULL |EMPLOYEES2 | 107 | 11449 | 3 (0)| 00:00:01 |

| 12 | TABLE ACCESS FULL |JOBS2 | 19 | 494 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

1 -access("EMP"."JOB_ID"="J"."JOB_ID")

2 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID"AND

"EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

7 -access("L"."CITY"=:LOC)

8 -access("D"."LOCATION_ID"="L"."LOCATION_ID")

1.5建立job_id的索引

SQL> create indexe2_jobid_ind on employees2(job_id);

SQL> create indexjob_jobid_ind on jobs2(job_id);

Execution Plan

----------------------------------------------------------

Plan hash value: 1816985179

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1| 277 | 9 (12)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 277 | 9 (12)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID |JOBS2 | 1 | 26 | 1 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 1 | 250 | 5 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 224 | 4 (0)| 00:00:01 |

| 5 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| LOCATIONS2 | 1 | 74 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN |L_CITY_IND | 1 | | 1 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS2 | 1 | 43 | 1 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN |DEPART_ID_IND | 1 | | 0 (0)| 00:00:01 |

| 10 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES2 | 4 | 428 | 1 (0)| 00:00:01 |

|* 11 | INDEX RANGE SCAN | EMP_DEPART_IND | 4 | | 0 (0)| 00:00:01 |

|* 12 | INDEX RANGE SCAN | JOB_JOBID_IND | 1| | 0 (0)| 00:00:01 |

| 13 | 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")

7 -access("L"."CITY"=:LOC)

9 -access("D"."LOCATION_ID"="L"."LOCATION_ID")

11 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

12 -access("EMP"."JOB_ID"="J"."JOB_ID")

1.6建立employees2的索引

SQL> create indexe2_emp_ind on employees2(EMPLOYEE_ID);

SQL> create indexe2_mgr_ind on employees2(MANAGER_ID);

Execution Plan

----------------------------------------------------------

Plan hash value: 2668283398

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 277 | 6 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID |EMPLOYEES2 | 1 | 27 | 1 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 277 | 6 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 1 | 250 | 5 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 224 | 4 (0)| 00:00:01 |

| 5 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| LOCATIONS2 | 1 | 74 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN |L_CITY_IND | 1| | 1 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS2 | 1 | 43 | 1 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN |DEPART_ID_IND | 1 | | 0 (0)| 00:00:01 |

| 10 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES2 | 4 | 428 | 1 (0)| 00:00:01 |

|* 11 | INDEX RANGE SCAN | EMP_DEPART_IND | 4 | | 0 (0)| 00:00:01 |

| 12 | TABLE ACCESS BY INDEX ROWID |JOBS2 | 1 | 26 | 1 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | JOB_JOBID_IND | 1| | 0 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | E2_EMP_IND | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

7 -access("L"."CITY"=:LOC)

9 -access("D"."LOCATION_ID"="L"."LOCATION_ID")

11 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

13 -access("EMP"."JOB_ID"="J"."JOB_ID")

14 -access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")

2.复合索引

2.1复合索引前缀性

2.1.1建立表，插入数据，建立索引

SCOTT@ prod>create table emp3 as select* from emp;

SCOTT@ prod>begin

2 for i in 1..1000 loop

3 insert into emp3values(7788,'WARD','SALESMAN',7839,sysdate,3000,2000,20);

4 end loop;

5 end;

6 /

SCOTT@ prod>create index ejm_emp_ind onemp(ename,job,mgr);

2.1.2查询语句，查看执行计划

SCOTT@prod>select * from emp where ename='ADAMS' and job='CLERK' and mgr='7788';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------------- ---------- --------------------

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

Elapsed:00:00:00.02

ExecutionPlan

----------------------------------------------------------

Planhash value: 93473824

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |EJM_EMP_IND | 1 | | 1 (0)| 00:00:01 |

复合索引包括了enamel，job，mgr，当查询条件给出对应条件时，就使用了索引

SCOTT@prod>select * from emp where job='CLERK';

ExecutionPlan

----------------------------------------------------------

Planhash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 111 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 3 | 111 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

查询条件没有ename，oracle不使用建立的索引，走全表扫描

2.2复合索引可选性

SYS@ prod>create tableidx_split (a number, b varchar2(1200),c date);

SYS@ prod>create indexidx_split_idx1 on idx_split (a,b) tablespace tbs_2k pctfree 0;

SYS@ prod>create indexidx_split_idx2 on idx_split (b,a) tablespace tbs_2k pctfree 0;

SYS@ prod>begin

2 for i in 1..1000 loop

3 insert into idx_split (a,b,c) values(i,lpad('A',500,'A'),sysdate);

4 end loop;

5 end;

6 /

SYS@ prod>analyze indexidx_split_idx1 validate structure;

SYS@ prod>selectname,height,blocks,br_blks,lf_blks from index_stats;

NAME HEIGHT BLOCKS BR_BLKS LF_BLKS

---------------------------------------- ---------- ---------- ----------

IDX_SPLIT_IDX1 2 80 1 67

选择性高的复合索引的高度比较低

SYS@ prod>analyze indexidx_split_idx2 validate structure;

SYS@ prod>selectname,height,blocks,br_blks,lf_blks from index_stats;

NAME HEIGHT BLOCKS BR_BLKS LF_BLKS

---------------------------------------- ---------- ---------- ----------

IDX_SPLIT_IDX2 3 80 6 67

选择性高的复合索引的高度比较高

SYS@prod>select * from idx_split wherea=10;

Elapsed:00:00:00.01

ExecutionPlan

----------------------------------------------------------

Planhash value: 4130831380

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 512 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| IDX_SPLIT | 1 | 512 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |IDX_SPLIT_IDX1 | 1 | | 2 (0)| 00:00:01 |

这时候索引走的第一个索引，range scan

SYS@prod>drop index idx_split_idx1;

SYS@prod>select * from idx_split wherea=10;

Elapsed:00:00:00.01

ExecutionPlan

----------------------------------------------------------

Planhash value: 924085735

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 512 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| IDX_SPLIT | 1 | 512 | 4 (0)| 00:00:01 |

|* 2 | INDEX SKIP SCAN |IDX_SPLIT_IDX2 | 1 | | 3 (0)| 00:00:01 |

这时候走的是第二个索引，skip scan

3.10053事件跟踪

3.1执行语句

ALTER SESSION SET EVENTS '10053 TRACE NAMECONTEXT FOREVER, LEVEL 1';

var loc varchar2(30)

exec :loc:='South San Francisco'

SELECTemp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,

emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name

from hr.employees emp,hr.employeesmgr,hr.departments d,hr.locations l,hr.jobs j

where l.city=:loc

and emp.manager_id=mgr.employee_id

and emp.department_id=d.department_id

and d.location_id=l.location_id

and emp.job_id=j.job_id;

ALTER SESSION SET EVENTS '10053 TRACE NAMECONTEXT OFF';

执行计划如下

Execution Plan

----------------------------------------------------------

ALTER SESSION SET EVENTS '10053 TRACE NAMECONTEXT OFF'; Plan hash value:4121168346

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 15 | 2580 | 8 (0)| 00:00:01 |

| 1| NESTED LOOPS | | 15 | 2580 | 8 (0)| 00:00:01 |

| 2| NESTED LOOPS | | 15 | 2400 | 6 (0)| 00:00:01 |

| 3| NESTED LOOPS | | 15 | 1995 | 5 (0)| 00:00:01 |

| 4| NESTED LOOPS | | 4 | 268 | 3 (0)| 00:00:01 |

| 5| TABLE ACCESS BY INDEX ROWID|LOCATIONS | 1 | 48 | 2 (0)| 00:00:01 |

|* 6| INDEX RANGE SCAN | LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 |

| 7| TABLE ACCESS BY INDEX ROWID|DEPARTMENTS | 4 | 76 | 1 (0)| 00:00:01 |

|* 8| INDEX RANGE SCAN | DEPT_LOCATION_IX | 4| | 0 (0)| 00:00:01 |

| 9| TABLE ACCESS BY INDEX ROWID |EMPLOYEES | 4 | 264 | 1 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN |EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |

| 11| TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 27 | 1 (0)| 00:00:01 |

|* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |

| 13| TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 12 | 1 (0)| 00:00:01 |

|* 14 | INDEX UNIQUE SCAN |EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

6- access("L"."CITY"=:LOC)

8- access("D"."LOCATION_ID"="L"."LOCATION_ID")

10-access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

12- access("EMP"."JOB_ID"="J"."JOB_ID")

14-access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")

3.2进入udump，查看跟踪文件

[oracle@rh55udump]$ vi prod_ora_2772.trc

表的别名

fro(0): flg=4 objn=51852hint_alias="D"@"SEL$1"

fro(1): flg=4 objn=51857hint_alias="EMP"@"SEL$1"

fro(2): flg=4 objn=51855hint_alias="J"@"SEL$1"

fro(3): flg=4 objn=51847hint_alias="L"@"SEL$1"

fro(4): flg=4 objn=51857hint_alias="MGR"@"SEL$1"

查询语句

SELECT"EMP"."LAST_NAME""LAST_NAME","EMP"."FIRST_NAME""FIRST_NAME","J"."JOB_TITLE""JOB_TITLE","D"."DEPARTMENT_NAME""DEPARTMENT_NAME","L"."CITY""CITY","L"."STATE_PROVINCE""STATE_PROVINCE","L"."POSTAL_CODE""POSTAL_CODE","L"."STREET_ADDRESS""STREET_ADDRESS","EMP"."EMAIL""EMAIL","EMP"."PHONE_NUMBER""PHONE_NUMBER","EMP"."HIRE_DATE""HIRE_DATE","EMP"."SALARY""SALARY","MGR"."LAST_NAME" "LAST_NAME"FROM "HR"."EMPLOYEES""EMP","HR"."EMPLOYEES""MGR","HR"."DEPARTMENTS" "D","HR"."LOCATIONS""L","HR"."JOBS" "J" WHERE"L"."CITY"=:B1 AND"EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID"AND "EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"AND "D"."LOCATION_ID"="L"."LOCATION_ID"AND "EMP"."JOB_ID"="J"."JOB_ID"

系统统计信息

*****************************

SYSTEMSTATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 587 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond(default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

基本统计信息

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: JOBS Alias: J

#Rows: 19 #Blks: 5 AvgRowLen: 33.00

Column (#1): JOB_ID(VARCHAR2)

AvgLen: 8.00 NDV: 19 Nulls: 0 Density:0.052632

Index Stats::

Index: JOB_ID_PK Col#: 1

LVLS: 0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

***********************

Table Stats::

Table: LOCATIONS Alias: L

#Rows: 23 #Blks: 5 AvgRowLen: 49.00

Column (#1): LOCATION_ID(NUMBER)

AvgLen: 3.00 NDV: 23 Nulls: 0 Density:0.043478 Min: 1000 Max: 3200

Index Stats::

Index: LOC_CITY_IX Col#: 4

LVLS: 0 #LB: 1 #DK: 23 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

Index: LOC_COUNTRY_IX Col#: 6

LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

Index: LOC_ID_PK Col#: 1

LVLS: 0 #LB: 1 #DK: 23 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

Index: LOC_STATE_PROVINCE_IX Col#: 5

LVLS: 0 #LB: 1 #DK: 17 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

***********************

Table Stats::

Table: DEPARTMENTS Alias: D

#Rows: 27 #Blks: 5 AvgRowLen: 20.00

Column (#1): DEPARTMENT_ID(NUMBER)

AvgLen: 4.00 NDV: 27 Nulls: 0 Density:0.037037 Min: 10 Max: 270

Column (#4): LOCATION_ID(NUMBER)

AvgLen: 3.00 NDV: 7 Nulls: 0 Density:0.018519 Min: 1400 Max: 2700

Histogram: Freq #Bkts: 7 UncompBkts: 27 EndPtVals: 7

Index Stats::

Index: DEPT_ID_PK Col#: 1

LVLS: 0 #LB: 1 #DK: 27 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

Index: DEPT_LOCATION_IX Col#: 4

LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

***********************

Table Stats::

Table: EMPLOYEES Alias: MGR

#Rows: 107 #Blks: 5 AvgRowLen: 68.00

Column (#1): EMPLOYEE_ID(NUMBER)

AvgLen: 4.00 NDV: 107 Nulls: 0 Density:0.0093458 Min: 100 Max: 206

Index Stats::

Index: EMP_DEPARTMENT_IX Col#: 11

LVLS: 0 #LB: 1 #DK: 11 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00

Index: EMP_EMAIL_UK Col#: 4

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00

Index: EMP_EMP_ID_PK Col#: 1

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00

Index: EMP_JOB_IX Col#: 7

LVLS: 0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 8.00

Index: EMP_MANAGER_IX Col#: 10

LVLS: 0 #LB: 1 #DK: 18 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00

Index: EMP_NAME_IX Col#: 3 2

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 15.00

***********************

Table Stats::

Table: EMPLOYEES Alias: EMP

#Rows: 107 #Blks: 5 AvgRowLen: 68.00

Column (#10): MANAGER_ID(NUMBER)

AvgLen: 4.00 NDV: 18 Nulls: 1 Density:0.004717 Min: 100 Max: 205

Histogram: Freq #Bkts: 18 UncompBkts: 106 EndPtVals: 18

Column (#11): DEPARTMENT_ID(NUMBER)

AvgLen: 3.00 NDV: 11 Nulls: 1 Density:0.004717 Min: 10 Max: 110

Histogram: Freq #Bkts: 11 UncompBkts: 106 EndPtVals: 11

Column (#7): JOB_ID(VARCHAR2)

AvgLen: 9.00 NDV: 19 Nulls: 0 Density:0.0046729

Histogram: Freq #Bkts: 19 UncompBkts: 107 EndPtVals: 19

Index Stats::

Index: EMP_DEPARTMENT_IX Col#: 11

LVLS: 0 #LB: 1 #DK: 11 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00

Index: EMP_EMAIL_UK Col#: 4

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00

Index: EMP_EMP_ID_PK Col#: 1

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00

Index: EMP_JOB_IX Col#: 7

LVLS:0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 8.00

Index: EMP_MANAGER_IX Col#: 10

LVLS: 0 #LB: 1 #DK: 18 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00

Index: EMP_NAME_IX Col#: 3 2

LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 15.00

单表访问

SINGLE TABLE ACCESS PATH

Table: DEPARTMENTS Alias: D

Card: Original:27 Rounded: 27 Computed: 27.00 Non Adjusted: 27.00

Access Path: TableScan

Cost: 3.01 Resp: 3.01 Degree: 0

Cost_io: 3.00 Cost_cpu: 41277

Resp_io: 3.00 Resp_cpu: 41277

Best:: AccessPath:TableScan

Cost: 3.01 Degree: 1 Resp: 3.01 Card: 27.00 Bytes: 0

SINGLE TABLE ACCESS PATH

Table: JOBS Alias: J

Card: Original:19 Rounded: 19 Computed: 19.00 Non Adjusted: 19.00

Access Path: TableScan

Cost: 3.01 Resp: 3.01 Degree: 0

Cost_io: 3.00 Cost_cpu: 38837

Resp_io: 3.00 Resp_cpu: 38837

Best:: AccessPath:TableScan

Cost: 3.01 Degree: 1 Resp: 3.01 Card: 19.00 Bytes: 0

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

***********************

Join order[1]: LOCATIONS[L]#0 JOBS[J]#1 DEPARTMENTS[D]#2 EMPLOYEES[EMP]#3 EMPLOYEES[MGR]#4

***************

Now joining: JOBS[J]#1

***************

NL Join

Outer table: Card:1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 48

Inner table: JOBS Alias: J

Access Path: TableScan

NL Join: Cost: 5.01 Resp: 5.01 Degree: 0

Cost_io: 5.00 Cost_cpu: 53510

Resp_io: 5.00 Resp_cpu: 53510

Best NL cost: 5.01

resc: 5.01resc_io: 5.00 resc_cpu: 53510

resp: 5.01resp_io: 5.00 resp_cpu: 53510

Join Card: 19.00 = outer(1.00) * inner (19.00) * sel (1)

Join Card - Rounded: 19 Computed: 19.00

Best:: JoinMethod: NestedLoop

Cost: 5.01 Degree: 1 Resp: 5.01 Card: 19.00 Bytes: 75

***************

Now joining: DEPARTMENTS[D]#2

***************

NL Join

Outer table: Card:19.00 Cost: 5.01 Resp: 5.01 Degree: 1 Bytes: 75

Inner table:DEPARTMENTS Alias: D

Access Path: TableScan

NL Join: Cost: 27.12 Resp: 27.12 Degree: 0

Cost_io: 27.00 Cost_cpu: 837777

Resp_io: 27.00 Resp_cpu: 837777

Access Path: index(AllEqJoinGuess)

Index:DEPT_LOCATION_IX

resc_io: 1.00 resc_cpu: 9611

ix_sel: 0.14286 ix_sel_with_filters: 0.14286

NL Join: Cost:6.02 Resp: 6.02 Degree: 1

Cost_io: 6.00 Cost_cpu: 130898

Resp_io: 6.00 Resp_cpu: 130898

Best NL cost: 6.02

resc: 6.02resc_io: 6.00 resc_cpu: 130898

resp: 6.02resp_io: 6.00 resp_cpu: 130898

Join Card: 73.29 = outer(19.00) * inner (27.00) * sel (0.14286)

Join Card - Rounded: 73 Computed: 73.29

SM Join

Outer table:

resc: 5.01 card 19.00 bytes: 75 deg: 1 resp: 5.01

Inner table:DEPARTMENTS Alias: D

resc: 3.01 card: 27.00 bytes: 19 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 1Row size: 93 Total Rows: 19

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7049026

Total Temp spaceused: 0

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 1 Row size: 31 Total Rows: 27

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7051174

Total Temp spaceused: 0

SM join: Resc:10.02 Resp: 10.02 [multiMatchCost=0.00]

SM cost: 10.02

resc: 10.02 resc_io:8.00 resc_cpu: 14199587

resp: 10.02 resp_io:8.00 resp_cpu: 14199587

HA Join

Outer table:

resc: 5.01 card 19.00 bytes: 75 deg: 1 resp: 5.01

Inner table:DEPARTMENTS Alias: D

resc: 3.01 card: 27.00 bytes: 19 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

Cost per ptn:0.50 #ptns: 1

hash_area: 0(max=0) Hash join: Resc: 8.51 Resp: 8.51 [multiMatchCost=0.00]

HA cost: 8.51

resc: 8.51 resc_io:8.00 resc_cpu: 3627632

resp: 8.51 resp_io:8.00 resp_cpu: 3627632

Best:: JoinMethod: NestedLoop

Cost: 6.02 Degree: 1 Resp: 6.02 Card: 73.29 Bytes: 94

***************

Now joining: EMPLOYEES[EMP]#3

***************

NL Join

Outer table: Card:73.29 Cost: 6.02 Resp: 6.02 Degree: 1 Bytes: 94

Inner table:EMPLOYEES Alias: EMP

Access Path: TableScan

NL Join: Cost: 88.78 Resp: 88.78 Degree: 0

Cost_io: 88.00 Cost_cpu: 5464074

Resp_io: 88.00 Resp_cpu: 5464074

Access Path: index(AllEqJoinGuess)

Index:EMP_DEPARTMENT_IX

resc_io: 1.00 resc_cpu: 13471

ix_sel: 0.091767 ix_sel_with_filters: 0.091767

NL Join: Cost:13.09 Resp: 13.09 Degree: 1

Cost_io: 13.00 Cost_cpu: 654837

Resp_io: 13.00 Resp_cpu: 654837

Access Path: index(AllEqJoinGuess)

Index: EMP_JOB_IX

resc_io: 1.00 resc_cpu: 11271

ix_sel: 0.052632 ix_sel_with_filters: 0.052632

NL Join: Cost:14.14 Resp: 14.14 Degree: 1

Cost_io: 14.00 Cost_cpu: 953713

Resp_io: 14.00 Resp_cpu: 953713

****** tryingbitmap/domain indexes ******

Access Path: index(AllEqJoinGuess)

Index:EMP_DEPARTMENT_IX

resc_io: 0.00 resc_cpu: 2850

ix_sel: 0.091767 ix_sel_with_filters: 0.091767

NL Join: Cost:6.05 Resp: 6.05 Degree: 1

Cost_io: 6.00 Cost_cpu: 338948

Resp_io: 6.00 Resp_cpu: 338948

Access Path: index(AllEqJoinGuess)

Index: EMP_JOB_IX

resc_io: 0.00 resc_cpu: 2050

ix_sel: 0.052632 ix_sel_with_filters: 0.052632

NL Join: Cost:6.04 Resp: 6.04 Degree: 1

Cost_io: 6.00 Cost_cpu: 280548

Resp_io: 6.00 Resp_cpu: 280548

Access path: Bitmapindex - rejected

Cost: 24.29 Cost_io:24.18 Cost_cpu: 823256 Sel: 0.0019311

Not believed to beindex-only

Best NL cost: 13.09

resc: 13.09resc_io: 13.00 resc_cpu: 654837

resp: 13.09resp_io: 13.00 resp_cpu: 654837

Join Card: 15.14 = outer(73.29) * inner (107.00) * sel (0.0019311)

Join Card - Rounded: 15 Computed: 15.14

SM Join

Outer table:

resc: 6.02 card 73.29 bytes: 94 deg: 1 resp: 6.02

Inner table:EMPLOYEES Alias: EMP

resc: 3.01 card: 107.00 bytes: 66 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 2 Row size: 114 Total Rows: 73

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7065748

Total Temp spaceused: 0

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 2 Row size: 83 Total Rows: 107

Initial runs: 1 Merge passes: 0IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7077889

Total Temp spaceused: 0

SM join: Resc:11.04 Resp: 11.04 [multiMatchCost=0.00]

SM cost: 11.04

resc: 11.04 resc_io:9.00 resc_cpu: 14347592

resp: 11.04 resp_io:9.00 resp_cpu: 14347592

HA Join

Outer table:

resc: 6.02 card 73.29 bytes: 94 deg: 1 resp: 6.02

Inner table:EMPLOYEES Alias: EMP

resc: 3.01 card: 107.00 bytes: 66 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

Cost per ptn:0.50 #ptns: 1

hash_area: 0(max=0) Hash join: Resc: 9.53 Resp: 9.53 [multiMatchCost=0.00]

HA cost: 9.53

resc: 9.53 resc_io:9.00 resc_cpu: 3748300

resp: 9.53 resp_io:9.00 resp_cpu: 3748300

Best:: JoinMethod: Hash

Cost: 9.53 Degree: 1 Resp: 9.53 Card: 15.14 Bytes: 160

***************

Now joining: EMPLOYEES[MGR]#4

***************

NL Join

Outer table: Card:15.14 Cost: 9.53 Resp: 9.53 Degree: 1 Bytes: 160

Inner table:EMPLOYEES Alias: MGR

Access Path: TableScan

NL Join: Cost: 27.67 Resp: 27.67 Degree: 0

Cost_io: 27.00 Cost_cpu: 4747858

Resp_io: 27.00 Resp_cpu: 4747858

Access Path: index(UniqueScan)

Index: EMP_EMP_ID_PK

resc_io: 1.00 resc_cpu: 8461

ix_sel: 0.0093458 ix_sel_with_filters: 0.0093458

NL Join: Cost:11.55 Resp: 11.55 Degree: 1

Cost_io: 11.00 Cost_cpu: 3875222

Resp_io: 11.00 Resp_cpu: 3875222

Access Path: index(AllEqUnique)

Index: EMP_EMP_ID_PK

resc_io: 1.00 resc_cpu: 8461

ix_sel: 0.0093458 ix_sel_with_filters: 0.0093458

NL Join: Cost:11.55 Resp: 11.55 Degree: 1

Cost_io: 11.00 Cost_cpu: 3875222

Resp_io: 11.00 Resp_cpu: 3875222

Best NL cost: 11.55

resc: 11.55resc_io: 11.00 resc_cpu: 3875222

resp: 11.55resp_io: 11.00 resp_cpu: 3875222

Join Card: 15.00 = outer(15.14) * inner (107.00) * sel (0.0092585)

Join Card - Rounded: 15 Computed: 15.00

SM Join

Outer table:

resc: 9.53 card 15.14 bytes: 160 deg: 1 resp: 9.53

Inner table:EMPLOYEES Alias: MGR

resc: 3.01 card: 107.00 bytes: 12 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 1 Row size: 186 Total Rows: 15

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7048030

Total Temp spaceused: 0

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

resc: 9.53 card 15.14 bytes: 160 deg: 1 resp: 9.53

Inner table:EMPLOYEES Alias: MGR

resc: 3.01 card: 107.00 bytes: 12 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 1 Row size: 186 Total Rows: 15

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7048030

Total Temp spaceused: 0

SORT resource Sort statistics

Sort width: 67 Area size: 131072 Max Area size: 12163072

Degree: 1

Blocks to Sort: 1 Row size: 24 Total Rows: 107

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost:0 Total CPU sort cost: 7077889

Total Temp spaceused: 0

SM join: Resc:14.55 Resp: 14.55 [multiMatchCost=0.00]

SM cost: 14.55

resc: 14.55 resc_io:12.00 resc_cpu: 17940856

resp: 14.55 resp_io:12.00 resp_cpu: 17940856

HA Join

Outer table:

resc: 9.53 card 15.14 bytes: 160 deg: 1 resp: 9.53

Inner table:EMPLOYEES Alias: MGR

resc: 3.01 card: 107.00 bytes: 12 deg: 1 resp: 3.01

using dmeth: 2 #groups: 1

Cost per ptn:0.50 #ptns: 1

hash_area: 0(max=0) Hash join: Resc: 13.04 Resp: 13.04 [multiMatchCost=0.00]

HA cost: 13.04

resc: 13.04 resc_io:12.00 resc_cpu: 7350582

resp: 13.04 resp_io:12.00 resp_cpu: 7350582

Best:: JoinMethod: NestedLoop

Cost: 11.55 Degree: 1 Resp: 11.55 Card: 15.00 Bytes: 172

经过计算得到的最好的连接方式

***********************

Best so far: Table#: 0 cost: 2.0021 card: 1.0000 bytes: 48

Table#:1 cost: 5.0076 card: 19.0000 bytes: 1425

Table#:2 cost: 6.0186 card: 73.2857 bytes: 6862

Table#:3 cost: 9.5320 card: 15.1429 bytes: 2400

Table#:4 cost: 11.5500 card: 15.0013 bytes: 2580

***********************

0 0