SQL优化一例（INList ITERATOR）

浏览数：33 / 时间：2015年06月12日

今天闲的看awr，发现一条SQL 每次执行40多秒，语句如下

SELECT a.bill_class AS pol_code , b.bill_name AS pol_name , a.bill_no AS card_no , ‘网站‘ AS buy_path , a.rev_clerk_code AS agent_code , a.rev_clerk_type AS agent_type , to_char(a.regist_date, ‘yyyy-mm-dd‘) AS recip_date , to_char(a.chk_date, ‘yyyy-mm-dd‘) AS pay_date , to_char(a.invalid_date, ‘yyyy-mm-dd‘) AS invalid_date , CASE WHEN chk_stat = ‘0‘ THEN ‘未核销‘ WHEN chk_stat = ‘1‘ AND autochkflag = ‘1‘ THEN ‘已自动核销‘ WHEN chk_stat = ‘1‘ AND autochkflag = ‘0‘ THEN ‘已人工核销‘ WHEN chk_stat = ‘2‘ THEN ‘丢失‘ WHEN chk_stat = ‘3‘ THEN ‘作废‘ WHEN chk_stat = ‘4‘ THEN ‘回缴核销‘ WHEN chk_stat = ‘5‘ THEN ‘已销毁‘ WHEN chk_stat = ‘A‘ THEN ‘待核销‘ END AS recip_stat , rev_branch_no , b.bill_literal_price AS prem 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 ( ‘1001‘, ‘1093‘, ‘1096‘, ‘1097‘, ‘1098‘, ‘1099‘, ‘1100‘, ‘1302‘, ‘1303‘, ‘1305‘, ‘1306‘, ‘1910‘, ‘1911‘, ‘1912‘, ‘1913‘, ‘1914‘, ‘1915‘, ‘1916‘, ‘1917‘ , ‘1918‘, ‘1919‘, ‘1922‘, ‘1923‘, ‘1924‘, ‘1925‘, ‘1926‘, ‘1927‘, ‘1928‘, ‘1929‘, ‘1930‘, ‘1931‘, ‘1935‘, ‘1936‘, ‘1937‘, ‘1938‘, ‘1939‘, ‘1940‘, ‘1941‘, ‘1942‘, ‘1943‘, ‘1944‘, ‘1945‘, ‘5232‘, ‘5233‘, ‘5234‘, ‘5252‘, ‘5255‘, ‘5258‘, ‘5265‘, ‘5260‘, ‘5276‘, ‘5277‘, ‘5278‘, ‘5285‘, ‘5290‘, ‘5301‘, ‘5304‘, ‘5309‘, ‘5311‘, ‘5329‘, ‘5330‘, ‘5359‘ , ‘5376‘, ‘5507‘, ‘5512‘, ‘5514‘, ‘5516‘, ‘5122‘, ‘5126‘, ‘5130‘, ‘5132‘, ‘5141‘, ‘5142‘, ‘5156‘, ‘5157‘, ‘5161‘, ‘5162‘, ‘5163‘, ‘5164‘, ‘5165‘, ‘5166‘, ‘5167‘, ‘5168‘, ‘5558‘, ‘5564‘, ‘5507‘ )

看这种SQL，一种方法，是通过PL/SQL DEV直接格式化下，第二种方法，放到txt文件中，搜索 FROM，直接把前面的全删了（非标量子查询时）。

SELECT ……

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 ( ……）;

这里也没有绑定变量，所以explain plan for的执行计划是没有误差的

当前执行计划如下

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2990887684

---------------------------------------------------------------------------------------
| Id  | Operation	   | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		      |  6452K|   553M|   151K	(5)| 00:30:19 |
|*  1 |  HASH JOIN	   |		      |  6452K|   553M|   151K	(5)| 00:30:19 |
|*  2 |   TABLE ACCESS FULL| BILL_CLASS       |   206 |  5150 |     3	(0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| BILL_GRANT_CHECK |  7321K|   453M|   151K	(5)| 00:30:19 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."BILL_CLASS"="B"."BILL_CLASS")
   2 - filter("B"."BILL_CLASS"=‘1001‘ OR "B"."BILL_CLASS"=‘1093‘ OR
	      "B"."BILL_CLASS"=‘1096‘ OR "B"."BILL_CLASS"=‘1097‘ OR "B"."BILL_CLASS"=‘1098‘
	      OR "B"."BILL_CLASS"=‘1099‘ OR "B"."BILL_CLASS"=‘1100‘ OR
	      "B"."BILL_CLASS"=‘1302‘ OR "B"."BILL_CLASS"=‘1303‘ OR "B"."BILL_CLASS"=‘1
。
。
。
。

这个看起来很简单的，7321k=7.3M 7.3M/453M =1.6% 这个结果集，完全可以走索引，而且这里使用的in，并且里面用的是常量，完全可以走 INList ITERATOR

我创建一个虚索引，看一下执行计划，如果效果确实不错，再提个申请，真实创建这个索引。

SQL> 
create index billgrant_class on shcvms.bill_grant_check(bill_class) nosegment;

Index created.

SQL> alter session set "_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。在统计信息正确的情况下 cost是具有参考价值的（不正确的话就别看cost了）

现在可以提申请了。真实创建这个索引。

一个简单的例子完事，（虽然这么简单的，不想往这贴，但是我blog中“Oracle优化之SQL 优化”分支太空了，写点东西充实一下它）

SQL优化一例（INList ITERATOR）,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

SQL优化一例（INList ITERATOR）