t "_use_nosegment_indexes"=true;
Session altered. 然后使用explain plan for 来解析执行计划
explain plan for SELECT /*+ index(a billgrant_class ) */ N多个列
FROM shcvms.bill_grant_check a , shcvms.bill_class b
WHERE a.regist_date IS NOT NULL AND
a.bill_class = b.bill_class
AND a.bill_class IN (......N多常量 )
我们看看执行计划
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2116188717
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6452K| 553M| 207 (1)| 00:00:03 |
| 1 | MERGE JOIN | | 6452K| 553M| 207 (1)| 00:00:03 |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| BILL_GRANT_CHECK | 7321K| 453M| 203 (1)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | BILLGRANT_CLASS | 7321K| | 16 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 206 | 5150 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | BILL_CLASS | 206 | 5150 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."REGIST_DATE" IS NOT NULL)
4 - access("A"."BILL_CLASS"='1001' OR "A"."BILL_CLASS"='1093' OR
"A"."BILL_CLASS"='1096' OR "A"."BILL_CLASS"='1097' OR "A"."BILL_CLASS"='1098' OR
"A"."BILL_CLASS"='1099' OR "A"."BILL_CLASS"='1100' OR "A"."BILL_CLASS"='1302' OR
看Cost。从之前的151k 降到了现在的207，速度提升为原来的1/1000。 在统计信息正确的情况下 cost是具有参考价值的（不正确的话就别看cost了）
现在可以提申请了。真实创建这个索引。
一个简单的例子完事，（虽然这么简单的，不想往这贴，但是我blog中“Oracle优化之SQL 优化”分支太空了，写点东西充实一下它）