SQL优化一例（INListITERATOR） - 数据库编程

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 优化”分支太空了，写点东西充实一下它）

SQL优化一例（INListITERATOR）(二)