10g索引的作用实验1

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');        

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>

5）在object_name上创建索引idx_tab_idx_test

CREATE INDEX    idx_tab_idx_test
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>

分析：

1）从Cost上来看，如果不在OBJECT_NAME创建索引，则

COST为142，CPU占用为3%，Rows为7，Bytes为581

2）如果在OBJECT_NAME创建索引，则

COST为2，CPU占用为0%，Rows为2，Bytes为166