1)授权SCOTT DBA权限
grant dba to scott;
2)SCOTT创建测试大表
create table tab_idx_test
as
select * from dba_objects
where owner in('PUBLIC','SCOTT','SYS','SYSTEM');
as
select * from dba_objects
where owner in('PUBLIC','SCOTT','SYS','SYSTEM');
3)打开执行计划
SET AUTOTRACE TRACE EXPLAIN
4)查询指定条件数据
SQL>SELECT OWNER,OBJECT_NAME FROM TAB_IDX_TEST WHERE OBJECT_NAME='DBA_INDEXES';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2925858188
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 581 | 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_IDX_TEST | 7 | 581 | 142 (3)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( "OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2925858188
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 581 | 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_IDX_TEST | 7 | 581 | 142 (3)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( "OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
5)在object_name上创建索引idx_tab_idx_test
CREATE INDEX idx_tab_idx_test
on tab_idx_test(object_name);
on tab_idx_test(object_name);
6)再次查询指定条件数据
SQL> SELECT OWNER,OBJECT_NAME FROM TAB_IDX_TEST WHERE OBJECT_NAME='DBA_INDEXES';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3112998378
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 166 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_IDX_TEST | 2 | 166 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TAB_IDX_TEST | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access( "OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3112998378
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 166 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_IDX_TEST | 2 | 166 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TAB_IDX_TEST | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access( "OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
分析:
1)从Cost上来看,如果不在OBJECT_NAME创建索引,则
COST为142,CPU占用为3%,Rows为7,Bytes为581
2)如果在OBJECT_NAME创建索引,则
COST为2,CPU占用为0%,Rows为2,Bytes为166