一组统计SQL的优化的经典案例

drop table test;
create table test (id int , day date default sysdate);
insert into test select rownum,sysdate-rownum from dual connect by rownum<=100000;
Predicate Information (identified by operation id):
---------------------------------------------------
3、凡是涉及到类似到where (sysdate - b.exit_date) * 24 * 60 < 10的写法，请修改为b.exit_date >sysdate-10/24/60 ，如果不这么改写，索引建了也没用！
4、连接两个远程表，比连接两个本地表要昂贵的多！所以建议先在远程环境做好连接后，在远程的机器上建立一个视图，我们本地再访问这个视图。(你可以想像要先把数据读过来，再连接)，这是以前在ASKTOM上看来的，TOM大师强调过的论点。
where a.indent_state = '100'
and a.agreement_id = b.agreement_id
and b.operate_tache = 6
and b.deal_flag = 1
and (sysdate - b.exit_date) * 24 * 60 < 10
select * from test;
drop index idx_day;
--最近1万分钟以内的记录
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
从生活常识便可得知，这些数据的海洋中，最近10分钟的数据应该是占总数据量非常少的！实验如下
select count(*) from HBNEW97OSS3.oss_xml_hist
where (sysdate - acc_date) * 24 * 60 <= 10; --返回记录数134条
|* 2 | TABLE ACCESS FULL| TEST | 3 | 27 | 87 (21)| 00:00:02 |
---------------------------------------------------------------------------
由此可以得知，根据这个exit_date的条件将返回很少的一部分记录，占全库很小的比例，属于选择性很高的列，非常适合建立索引。其他列都不能与之比拟，比如
select count(*) from hbnew97oss3.process_inst
where sub_type = 'SBPMRA'返回了500多万行，而按10分钟的条件才返回前面的203行
select count(*) from hbnew97oss3.process_inst
where sts_date>sysdate-10/24/60 ; --返回记录数203条
select count(*) from HBNEW97OSS3.Bpm_Process_Inst
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
2、由于大量的COUNT和SUM的聚合语句，建议为该列建NOT NULL属性，等于告诉ORACLE可以把索引当“瘦表”来用而不会出错！
alter table cust_indent_tache_t modify exit_date not null;
3、去掉数据链写法@A2SVC2,在远程环境直接建视图
| 0 | SELECT STATEMENT | | 1 | 9 | 87 (21)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
SQL> connect ljb/ljb
已连接。
SQL> set autotrace on
SQL> select count(*) from test where (sysdate - day) * 24 * 60 < 10000;
COUNT(*)
----------
6
1、 HBNEW97OSS3.oss_xml_hist ---记录数21965662条
2、hbnew97oss3.process_inst; ---记录数29357919条
3、HBNEW97OSS3.Bpm_Process_Inst ---记录数 24926324条
三、解决方案
通过以上的分析我们知道，现在我们着手完成如下四步，即可调优成功，整体性能应该可以大幅度提升。
1、所有SQL中涉及到确定时间范围为最近10分钟的SQL语句的时间列，必须要索引
2、为了能有可能进行索引直接回答问题，建议上一步骤继续深入，把这些时间字段列标记为NOT NULL属性（我想这个时间字段列基本可以确认有这个属性吧，如果可以确认，请加上这个NOT NULL属性，对COUNT和SUM的聚合只有百利而无一害，因为增加了索引变身“瘦表”的可能）
现在综合举一个例子如下：
比如如下SQL
select a.partition_id_region 本地网编码,
count(a.agreement_id) 十分钟内竣工环节处理量
from ls65_crm2.cust_indent_t@A2SVC2 a,
ls65_crm2.cust_indent_tache_t@A2SVC2 b
ls65_crm2.cust_indent_tache_t b
where a.indent_state = '100'
and a.agreement_id = b.agreement_id
and b.operate_tache = 6
and b.deal_fБайду номын сангаасag = 1
Create or replace view v_cust_ident as
select a.partition_id_region 本地网编码,
count(a.agreement_id) 十分钟内竣工环节处理量
from ls65_crm2.cust_indent_t a,
3、 绝大多数SQL是统计10分钟的数据（代码随处可见SysDate - b.exit_date) * 24 * 60 <= 10的写法说明了这点）
进一步分析这些SQL，发现写法集中在如下几张表中，并且记录数都在千万以上，其中ls65_crm2.cust_indent_tache_t记录过亿
四、总结
1、对选择性高的列要敏感（什么样的查询能从大数据量返回少量值，这个查询列非常适宜建索引）
2、SQL的写法要注意列避免被运算，这个可作为SQL开发规范来规范。
3、远程多表连接开销很大，尽量用视图封装，TOM大师强调过，也可考虑将来做为开发规范。
本案例中，主要是高选择性的索引列未建，其次是注意SQL写法，否则即便建了索引也用不到，这个可以用一个简单的例子发出来，我构造的。
group by a.partition_id_region
order by a.partition_id_region;
该如何调优呢
1、首先在CRM的机器上建索引
Create index idx_ exit_date on cust_indent_tache_t(exit_date);
where (sysdate - start_date) * 24 * 60 < 10; --返回记录数117条
select count(*) from ls65_crm2.cust_indent_tache_t b
where (sysdate - b.exit_date) * 24 * 60 < 10 ; --返回记录数1518条
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
b.exit_date >sysdate-10/24/60
group by a.partition_id_region
order by a.partition_id_region;
4、我们网管监控系统直接用调用远程视图
如：
Select * from v_cust_ident@A2SVC2；
经查询发现涉及到用到日期范围的字段如ls65_crm2.cust_indent_tache_t 表的exit_date 或HBNEW97OSS3.oss_xml_hist表的acc_date 都没有建索引！这一系列共性SQL监控语句中，放着如此高选择性的列未建索引，非常可惜，如果能有效的利用到该列的索引，应该性能脚本上能得到十倍甚至百倍以上的提高！