单表查询-分页提前获取数据
1、 问题
以下的例子如何优化呢？
SELECT * FROM(SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT t1.* FROM ( SELECT * FROM T1 ) t1 WHERE 1 = 1 ORDER BY T1.TTIME DESC)INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0
这个语句是单表全表查询排序后分页获取数据，似乎看起来没什么优化空间。分页获取前25行数据，相当于从按ttime排序后，从这批有序的数据中获取前25行数据，这里我们可以想到索引是有序的，而要提前获取数据，在达梦数据库里需要用到以下两个参数TOP_ORDER_OPT_FLAG/TOP_ORDER_ESTIMATE_CARD
2、TOP_ORDER_OPT_FLAG/TOP_ORDER_ESTIMATE_CARD
| 参数 | 参数含义 |
|---|---|
| TOP_ORDER_OPT_FLAG | 优化带有 TOP 和 ORDER BY 子句的查询，使得 SORT 操作符可以省略。优化的效果是尽量使得 ORDER BY 的排序列所对应的基表可以使用包含排序列的索引，从而可以移除排序 SORT 操作符，减少排序操作。如果排序列不属于同一个基表，或者排序列不是基表列，则无法进行优化。0：不启用该优化； 1：对最优索引进行优化； 2：优先选择与排序列一致的可以消除排序的索引进行优化 |
| TOP_ORDER_ESTIMATE_CARD | TOP_ORDER_OPT_FLAG=1/2 命中优化时，设置叶子节点的预估扫描行数。下层操作符不是简单的BT时，如果优化器预估行数不准，可帮助优化器调整，以便选择最优计划。默认300。 |
以上摘自《达梦数据库管理员手册》
接下来我们来看看其优化效果
首先我们创建order by中列的索引
CREATE OR REPLACE INDEX "IDX_DM_T1" ON "T1"("TTIME" DESC)global；
然后我们使用hint看看其效果
SELECT /*+TOP_ORDER_OPT_FLAG(2)*/* FROM(SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT t1.* FROM ( SELECT * FROM T1 ) t1 WHERE 1 = 1 ORDER BY T1.TTIME DESC)INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0
这里提前获取300行数据，然后再分页取得25行，300也就是TOP_ORDER_ESTIMATE_CARD的配置。
3、小结
假设单表大表排序分页提前获取前几十行数据。通过增加排序列索引加上top_order_opt_flag=2即可达到优化效果。