ORACLE 执行计划的选择
---------------------环境创建----------------------------------1.创建序列
SQL> create sequence seq_num minvalue 1 maxvalue 9999 start with 1 increment by 1 nocache cycle;
Sequence created.
2.创建表
SQL> create table test_emp as select * from emp where 1=2;
Table created.
3.给表插数据
SQL>declare
s_num number;
begin
s_num:=1;
loop
exit when s_num > 8888;
insert into test_emp(empno,deptno,ename,job,mgr) select seq_num.NEXTVAL ,a.deptno,a.ename,a.job,a.mgr from emp a;
s_num:=s_num+1;
end loop;
end;
4.修改mgr列适应测试
SQL> update test_emp set mgr = '22' where empno < 3000;
38987 rows updated.
SQL> commit;
Commit complete.
SQL> update test_emp set mgr = '33' where empno >= 3000 and empno commit;
Commit complete.
SQL> update test_emp set mgr = '44' where empno >= 6000 ;
48000 rows updated.
SQL> commit;
Commit complete.
5.创建索引
SQL> create index index1 on test_emp(empno);
Index created.
SQL> create index index2 on test_emp(deptno);
Index created.
SQL> create index index3 on test_emp(mgr);
Index created.
-------------------测试开始--------------------------------------
SQL> set autotrace traceonly explain;
1.查看查询empno的执行计划
SQL> select * from test_emp where empno = '44';
Execution Plan
----------------------------------------------------------
Plan hash value: 339831789
--------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 |1131 | 15 (0)| 00
:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST_EMP| 13 |1131 | 15 (0)| 00
:00:01 |
|*2 | INDEX RANGE SCAN | INDEX1 | 13 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
2 - access("EMPNO"=44)
Note
-----
- dynamic sampling used for this statement
可以看出，走了索引INDEX1，COST=15,Rows=13,Bytes=1131
SQL> select * from test_emp where deptno = '7092';
Execution Plan
----------------------------------------------------------
Plan hash value: 3737976355
--------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00
:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST_EMP | 1 | 87 | 1 (0)| 00
:00:01 |
|*2 | INDEX RANGE SCAN | INDEX2 | 1 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
2 - access("DEPTNO"=7092)
Note
-----
- dynamic sampling used for this statement
可以看出，走了索引INDEX2，COST=1,Rows=1,Bytes=87
SQL> select * from test_emp where mgr = '44';
Execution Plan
----------------------------------------------------------
Plan hash value: 242355602
------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 34299 |2914K| 143 (4)| 00:00:02 |
|*1 |TABLE ACCESS FULL| TEST_EMP | 34299 |2914K| 143 (4)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("MGR"=44)
Note
-----
- dynamic sampling used for this statement
--
可以看出，没走索引，COST=143,Rows=34299，Bytes=2914k
---------------------------------------
会发现：
走索引耗费数据库资源的对比：INDEX2(DEPTNO) < INDEX1(EMPNO) < INDEX3(MGR)
SQL> select * from test_emp where deptno = '7902' and empno = '7777';
Execution Plan
----------------------------------------------------------
Plan hash value: 3737976355
--------------------------------------------------------------------------------
|> me |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |1044 | 2 (0)| 00
:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID| TEST_EMP | 12 |1044 | 2 (0)| 00
:00:01 |
|*2 | INDEX RANGE SCAN | INDEX2 | 7 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("EMPNO"=7777)
2 - access("DEPTNO"=7902)
Note
-----
- dynamic sampling used for this statement
可以看出，走了索引INDEX2，回表时候索引ROWID，COST=2,Rows=12，Bytes=1044
SQL> select * from test_emp where deptno = '7902' and mgr = '44';
Execution Plan
----------------------------------------------------------
Plan hash value: 3737976355
--------------------------------------------------------------------------------
|> me |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |1044 | 2 (0)| 00
:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID| TEST_EMP | 12 |1044 | 2 (0)| 00
:00:01 |
|*2 | INDEX RANGE SCAN | INDEX2 | 7 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("MGR"=44)
2 - access("DEPTNO"=7902)
Note
-----
- dynamic sampling used for this statement
可以看出，走了索引INDEX2，回表时候索引ROWID，COST=2,Rows=12，Bytes=1044
SQL> select * from test_emp where empno = '7902' and mgr = '44';
Execution Plan
----------------------------------------------------------
Plan hash value: 339831789
--------------------------------------------------------------------------------
|> me |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |1044 | 9 (0)| 00
:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID| TEST_EMP | 12 |1044 | 9 (0)| 00
:00:01 |
|*2 | INDEX RANGE SCAN | INDEX1 | 7 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("MGR"=44)
2 - access("EMPNO"=7902)
Note
-----
- dynamic sampling used for this statement
可以看出，走了索引INDEX1，回表时候索引ROWID，COST=9,Rows=12，Bytes=1044
所以在条件中可能用到两个索引的时候：
1 deptno,empno 优化器选择了INDEX2(DEPTNO)
2 deptno,mgr 优化器选择了INDEX2(DEPTNO)
3 empno,mgr 优化器选择了INDEX1(EMPNO)
页:
[1]