SQL> set autotrace traceonly;
1.INDEX SKIP SCAN
CREATE TABLE test
AS
SELECT DECODE(MOD(ROWNUM,2), 0, '1', '2' ) a,
ROWNUM-1 b,
ROWNUM-2 c,
ROWNUM-3 d,
ROWNUM-4 e
FROM all_objects
/
表已创建。
SQL> set autotrace off
SQL> select distinct a from test;
A
--
1
2
--A列只有两个唯一值
SQL> CREATE INDEX test_idx ON test(a,b,c)
2 /
索引已创建。
SQL> ANALYZE TABLE test COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS
/
表已分析。
SQL> set autotrace traceonly explain
SQL> SELECT * FROM test WHERE b = 99
2 /
SQL> SELECT * FROM test WHERE b = 99;
执行计划
----------------------------------------------------------
Plan hash value: 2705879578
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 23 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_IDX | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
2.INDEX RANGE SCAN
SQL> select empno from emp where empno > 4567;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 1567865628
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
3.INDEX UNIQUE SCAN
SQL> select empno from emp where empno = 7521;
执行计划
----------------------------------------------------------
Plan hash value: 56244932
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
4.TABLE ACCESS BY USER ROWID
SQL> select * from emp where rowid = 'AAAIwdAAEAAAAAiAAA';
执行计划
----------------------------------------------------------
Plan hash value: 1116584662
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
5.TABLE ACCESS FULL
SQL> select * from emp;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
6.INDEX FULL SCAN
SQL> select empno from emp;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
7.INDEX FAST FULL SCAN
SQL> select count(empno) from emp_1;
执行计划
----------------------------------------------------------
Plan hash value: 2411118849
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FAST FULL SCAN| IDX_EMP_1 | 28672 | 86016 | 23 (0)| 00:00:01 |
-----------------------------------------------------------------------------------