今天闲的 看awr，发现一条sql 每次执行40多秒，语句如下
?
1
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的执行计划是没有误差的
当前执行计划如下
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
我创建一个虚索引，看一下执行计划，如果效果确实不错，再提个申请，真实创建这个索引。
?
1
2
3
4
5
6
7
8
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 来解析执行计划
?
1
2
3
4
5
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多常量 )
我们看看执行计划
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
plan_table_output
-----------------------------------------------------------------------------------------------------------------------------------------
plan hash value: 2116188717
--------------------------------------------------------------------------------------------------