create index语句的NOSEGMENT选项表明这个索引是“虚拟的”-----就是没有与之相关联的实际索引段。如果优化器认为这个索引有用,参数_use_nosegment_indexes将指示数据库可以在执行计划中使用这些索引。下面我们来看看如果真的创建这些索引,它们是否值得使用:
SQL> explain plan for select count(*) from bigtab a,smalltab b where a.object_name=b.table_name;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1254475829
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 19 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | HASH JOIN | | 99838 | 3899K| 19 (6)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| SMALL_IND | 15311 | 299K| 9 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| BIG_IND | 85284 | 1665K| 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
16 rows selected.
执行计划显示,这两个索引确实被认为有用,优化器估算的成本也下降到19了。所以如果需要优化语句可以建议创建这些索引。
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12