前些天优化过一个分页函数,回头整理的时候发现了一个问题。让我做了一周多的实验才找到原因:
相关表的信息:CCCCCC_FUUU_INFO表90w+条数据 DDDDD_INFO表11条数据 SYS_SEQ_ID是主键。TRAN_TIME的选择性也非常高。DDDDC_ID是表DDDDD_INFO的主键
实验一:
DROP INDEX IDX_CCCCCC_FUUU_INFO;
CREATE INDEX IDX_CCCCCC_FUUU_INFO ON CCCCCC_FUUU_INFO(TRAN_TIME DESC,SYS_SEQ_ID DESC) ONLINE;
SELECT *
FROM (SELECT /*+ GATHER_PLAN_STATISTICS INDEX(C,IDX_CCCCCC_FUUU_INFO) USE_NL(C,PI) LEADING(C) */
C.*, PI.CN_NAME
FROM CCCCCC_FUUU_INFO C, DDDDD_INFO PI
WHERE PI.DDDDC_ID = C.DDDDC_ID
ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC)
WHERE ROWNUM <= 20;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,NULL,'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 30 |
| 2 | VIEW | | 1 | 20676 | 20 |00:00:00.01 | 30 |
| 3 | NESTED LOOPS | | 1 | 20676 | 20 |00:00:00.01 | 30 |
| 4 | TABLE ACCESS BY INDEX ROWID| CCCCCC_FUUU_INFO | 1 | 914K| 20 |00:00:00.01 | 25 |
| 5 | INDEX FULL SCAN | IDX_CCCCCC_FUUU_INFO | 1 | 914K| 20 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | IDX_DDDDD_INFO | 20 | 1 | 20 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
6 - access("PI"."DDDDC_ID"="C"."DDDDC_ID")
实验二:修改order by顺序和组合索引的顺序
DROP INDEX IDX_CCCCCC_FUUU_INFO;
CREATE INDEX IDX_CCCCCC_FUUU_INFO ON CCCCCC_FUUU_INFO(SYS_SEQ_ID DESC,TRAN_TIME DESC) ONLINE;
SELECT *
FROM (SELECT /*+ GATHER_PLAN_STATISTICS INDEX(C,IDX_CCCCCC_FUUU_INFO) USE_NL(C,PI) LEADING(C) */
C.*, PI.CN_NAME
FROM CCCCCC_FUUU_INFO C, DDDDD_INFO PI
WHERE PI.DDDDC_ID = C.DDDDC_ID
ORDER BY C.SYS_SEQ_ID DESC, C.TRAN_TIME DESC)
WHERE ROWNUM <= 20;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,NULL,'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:05.98 | 782K|
| 2 | VIEW | | 1 | 20676 | 20 |00:00:05.98 | 782K|
| 3 | NESTED LOOPS | | 1 | 20676 | 20 |00:00:05.98 | 782K|
| 4 | TABLE ACCESS BY INDEX ROWID| CCCCCC_FUUU_INFO | 1 | 914K| 852K|00:00:03.41 | 782K|
| 5 | INDEX FULL SCAN | IDX_CCCCCC_FUUU_INFO | 1 | 914K| 852K|00:00:00.01 | 5846 |
|* 6 | INDEX RANGE SCAN | IDX_DDDDD_INFO | 852K| 1 | 20 |00:00:02.03 | 6 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
6 - access("PI"."DDDDC_ID"="C"."DDDDC_ID")
在预估的PLAN里面两种执行计划一模一样,在真实的执行计划中返回的真实行数A-Rows却是千差万别
由上面的SQL语句及创建索引的语句可以看出来无非就是ORDER BY两个字段的顺序变了,但是和索引保持一致
这种问题,我以前从未遇到,为了解决问题,我做了如下实验来猜想我哪一个环节出了问题
1.看字段名称和类型是否有影响。(类型设一致。名称互换。结果:无影响)
2.看字段的长度是否有影响。(长度设置成一样、长度互换。结果:无影响)
3.看字段内容的长度是否有影响。(字段长度设置成一样、字段长度互换。结果:无影响)
4.去掉关联表,只对一个表分页。(结果:有影响)实验如下 :
DROP INDEX IDX_CCCCCC_FUUU_INFO;
CREATE INDEX IDX_CCCCCC_FUUU_INFO ON CCCCCC_FUUU_INFO(TRAN_TIME DESC,SYS_SEQ_ID DESC) ONLINE;
SELECT *
FROM (SELECT *
FROM CCCCCC_FUUU_INFO C
ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC)
WHERE ROWNUM <= 20;
--结果:0.1S响应,逻辑读24
DROP INDEX IDX_CCCCCC_FUUU_INFO;
CREATE INDEX IDX_CCCCCC_FUUU_INFO ON CCCCCC_FUUU_INFO(SYS_SEQ_ID DESC,TRAN_TIME DESC) ONLINE;
SELECT *
FROM (SELECT *
FROM CCCCCC_FUUU_INFO C
ORDER BY C.SYS_SEQ_ID DESC,C.TRAN_TIME DESC)
WHERE ROWNUM <= 20;
--结果:0.1S响应,逻辑读24
由此得出。可能是关联表DDDDD_INFO的原因:
SELECT A.DDDDC_ID,A.EN_NAME,A.CN_NAME,A.ADDRESS FROM DDDDD_INFO A
可以看出上面的数据很乱。根本就是手工输入进去的测试数据,也就是说有很多数据很可能都匹配不到结果
所以我猜测实验2里面,排序之后的前N行(N<=1000)都匹配不到数据。
我们看真实的plan里面返回852K就,大胆的猜测一下:可能索引返回852K行的时候才在PI表中匹配到数据
对实验一验证:
SELECT CC.DDDDC_ID, SYS_SEQ_ID, TRAN_TIME, PI.DDDDC_ID,NUM1 "行数"
FROM (SELECT DDDDC_ID,
SYS_SEQ_ID,
TRAN_TIME,
ROW_NUMBER() OVER(ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC) NUM1
FROM CCCCCC_FUUU_INFO C) CC,
DDDDD_INFO PI
WHERE PI.DDDDC_ID = CC.DDDDC_ID;
对实验二的验证:
SELECT CC.DDDDC_ID, SYS_SEQ_ID, TRAN_TIME, PI.DDDDC_ID, NUM1 "行数"
FROM (SELECT DDDDC_ID,
SYS_SEQ_ID,
TRAN_TIME,
ROW_NUMBER() OVER(ORDER BY C.SYS_SEQ_ID DESC, C.TRAN_TIME DESC) NUM1
FROM CCCCCC_FUUU_INFO C) CC,
DDDDD_INFO PI
WHERE PI.DDDDC_ID = CC.DDDDC_ID;
由上可得:实验1索引返回数据的前20条都在参数表PI中能够匹配到,扫描20条数据就停止。所以能够很快响应
实验2索引返回的数据前20条都没有能在参数表PI中匹配到,所以只能往下继续扫描第21,22,23,……一直到852824行的时候才开始匹配到结果
也就是说实验2的分页是从第852824行开始取20条数据返回。这就是坑爹的原因!!!!
我们可能更多的是去尝试怎么优化,而很少去关注业务数据。所以有时候我们还是需要稍微的关注一下业务数据!
其实正常情况下是不会出现这种问题的,因为业务的明细数据去关联参数表(维度表),都是100%可以匹配上的。这是最坑爹的地方就是测试环境的数据有问题
但是有种类似情况的分页语句优化还是需要引起注意:
比如:分页函数里面的表关联的结果返回只有19条数据,而rownum <= 20。这种情况比上述情况还要恶劣,因为不够20行就会一直扫描,直到扫描驱动表所有的行
这种结果在多表关联的分页函数里面还是会出现的。这种优化方法也很简单就是改SQL:rownum <= 20 改成 rownum <= 19 。搞定!!!