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