like order by top组合语句的优化是怎样的

今天就跟大家聊聊有关like order by top组合语句的优化是怎样的，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

环境信息

基本信息

操作系统：CentoOS 7.2

数据库版本：DM Database Server x64 V7. 1.5.202-Bu1 1d(2017.07.17-82922 ) ENT

页大小：16K

大小写敏感：是

字符集：GB18030

数据库基本参数

MAX_OS_MEMORY

50

MEMORY_POOL

80

MEMORY_TARGET

0

MEMORY_MAGIC_CHECK

0

BUFFER

10000

BUFFER_POOLS

19

RECYCLE

64

RECYCLE_POOLS

1

MAX_BUFFER

10000

HJ_BUF_GLOBAL_SIZE

500

HJ_BUF_SIZE

50

DICT_BUF_SIZE

5

VM_POOL_SIZE

54

SESS_POOL_SIZE

16

USE_PLN_POOL

1

VIEU_PULLUP_FLAG

0

OPTIMIZER_MODE

0

OLAP_FLAG

2

TEMP_SIZE

10

CACHE_POOL_SIZE

10

PURGE_DEL_OPT

0

COMPATIBLE_MODE

0

表结构

CREATE TABLE "XYGX"."XYGX_GS_TYSHXYDM_FR"

(

"UUID" NUMERIC(36,6) NOT NULL,

"QYMC" VARCHAR(200),

"TYSHXYDM" VARCHAR(50),

"SCJYD" VARCHAR(300),

"ZCXS" VARCHAR(2),

"JYZT" VARCHAR(6),

"WZ" VARCHAR(6),

"SCJYDXZQH" VARCHAR(1000),

"ZCH" VARCHAR(50),

"QYLXDM" VARCHAR(4),

"ZHYCNBSJ" DATETIME(6),

"JYFW" VARCHAR(4000),

"ZCZB" NUMERIC(38,8),

"ZCDZSZXXQH" VARCHAR(1000),

"HZRQ" DATETIME(6),

"ZS" VARCHAR(300),

"ZCDYB" VARCHAR(30),

"FZRQ" DATETIME(6),

"CLRQ" DATETIME(6),

"HBZL" VARCHAR(30),

"CYRS" NUMERIC(36,6),

"HYDM" VARCHAR(50),

"YYQXZHI" DATETIME(6),

"YYQXZI" DATETIME(6),

"XXCZLX" VARCHAR(12),

"YWLX" VARCHAR(12),

"DJJG" VARCHAR(200),

"ZHYCNBND" NUMERIC(38,8),

"FDDBR" VARCHAR(300),

"ZTID" NUMERIC(38,8),

NOT CLUSTER PRIMARY KEY("UUID")) STORAGE(ON "XYGX", CLUSTERBTR) ;

CREATE  INDEX "XYGX_GS_TYSHXYDM_FR_INDEX" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("QYMC" ASC,"FDDBR" ASC,"TYSHXYDM" ASC,"ZCH" ASC,"HZRQ" ASC) STORAGE(ON "XYGX", CLUSTERBTR) ;

CREATE  INDEX "IDX_HZRQ_QYMC" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC" DESC) STORAGE(ON "XYGX", CLUSTERBTR) ;

表数据量

查询表的数据量

select count(*) from XYGX.XYGX_GS_TYSHXYDM_FR

查询结果如下，该表工三百二十多万条数据。

count(*)

3216107

原语句

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,yyyy-MM-dd)ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,/*TO_CHAR(HZRQ,yyyy-MM-dd)*/HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,yyyy-MM-dd)FZRQ,TO_CHAR(CLRQ,yyyy-MM-dd)CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,yyyy-MM-dd)YYQXZHI,TO_CHAR(YYQXZI,yyyy-MM-dd)YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like 星际% order by HZRQ desc limit 0,50;

原执行计划

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= 星际 AND XTGX_GS_TYSHXYDM. QYMC <星%)

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

分析过程

基本思路

排序消耗的了大量资源，可以通过索引消除排序的方式进行优化。

创建索引

创建相关索引，把排序列放在前，筛选列放在后。

CREATE  INDEX "IDX_HZRQ_QYMC" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC" DESC) ;

创建索引且清除执行计划缓存后执行计划没有变化。

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= 星际 AND XTGX_GS_TYSHXYDM. QYMC <星%)

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

清除执行计划

查出语句相关的计划信息，获取计划的CACHE_ITEM

select * from v$CACHEPLN where sqlstr like ‘%where QYMC like 星际%’;

根据CACHE_ITEM 清除执行计划

call sp_clear_plan_cache(139845509285864);

检查TOP_ORDER_OPT_FLAG

检查TOP_ORDER_OPT_FLAG：

select * from v$dm_ini where para_name like ‘%TOP%’;

查到结果是TOP_ORDER_OPT_FLAG当前只为0，未开启排序优化。

参数说明：

改参数是动态参数，当语句内含有TOP + ORDER，且ORDER BY列属于索引前导列时，如果该值为1，则根据ORDER BY列对应的基本信息，减少估算的行数从而减少代价计算。当该值为0怎不进行优化。

动态调整参数：

sp_set_para_value(1,TOP_ORDER_OPT_FLAG1);

调整后执行计划依然不变，另外开窗口并再清除执行计划也不变。

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= 星际 AND XTGX_GS_TYSHXYDM. QYMC <星%)

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

简化语句测试

通过简化发现如果把语句总order by列的to_char函数去掉，执行计划就正常走索引，消除了排序，在order和to_char同时使用一个字段时通过索引消除排序方式不可用

优化思路，通过使用嵌套的方式消除了这种问题，改造后语句：

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,yyyy-MM-dd)HZRQ,ZS,ZCDYB,FZRQ,CLRQ,HBZL,CYRS,HYDM,YYQXZHI,YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from (

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,yyyy-MM-dd)ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,/*TO_CHAR(HZRQ,yyyy-MM-dd)*/HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,yyyy-MM-dd)FZRQ,TO_CHAR(CLRQ,yyyy-MM-dd)CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,yyyy-MM-dd)YYQXZHI,TO_CHAR(YYQXZI,yyyy-MM-dd)YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like 星际% order by HZRQ desc limit 0,50)

 ;

改造后执行计划

1 #NSET2: [0, 50, 1142]

2   #RJT2: [0, 50, 1142]; exp_num(31), is_atom FALSE)

3     #PRJT2:[0, 50, 1142]: kexp_num(31), is_atom FALSE)

4       #TOPN2: [0, 50, 1142]; top nun50), top_off(0)

5         #SLCT2: [0, 100, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= 星际 AND XTGX_GS_TYSHXYDM. QYMC <星%)

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

二次分析

第一次进行优化后，执行计划得到了改善，执行时间也大大缩短。但因用户需求，对昨天的语句增加了条件，导致语句运行缓慢，需要再次运行。

原语句：

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,yyyy-MM-dd)HZRQ,ZS,ZCDYB,FZRQ,CLRQ,HBZL,CYRS,HYDM,YYQXZHI,YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID from (

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,yyyy-MM-dd)ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,yyyy-MM-dd)FZRQ,TO_CHAR(CLRQ,yyyy-MM-dd)CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,yyyy-MM-dd)YYQXZHI,TO_CHAR(YYQXZI,yyyy-MM-dd)YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like 星际% or TYSHXYDM like 星际%

 order by HZRQ desc limit 0,50);

原执行计划：

1   #NSET2: [0, 50, 1142]

2     #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

3       #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

4         #TOPN2: [0, 50, 1142]; top_num(50), top_off(0)

5           #SLCT2: [0, 100, 1142]; (exp11 > 0 OR exp11 > 0)

6             #BLKUP2: [0, 100, 1142]; IDX_HZRQ_QYMC(XYGX_GS_TYSHXYDM_FR)

7               #SSCN: [0, 100, 1142]; IDX_HZRQ_QYMC(XYGX_GS_TYSHXYDM_FR)

由于新增了条件字段，另外创建了索引，执行计划未得到改善

简化语句

根据新语句创建索引：

CREATE  INDEX "IDX_HZRQ_QYMC_TYSHXYDM" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC","TYSHXYDM");

执行计划：

1   #NSET2: [0, 50, 1142]

2     #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

3       #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

4         #TOPN2: [0, 50, 1142]; top_num(50), top_off(0)

5           #SLCT2: [0, 100, 1142]; (exp11 > 0 OR exp11 > 0)

6             #BLKUP2: [0, 100, 1142]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

7               #SSCN: [0, 100, 1142]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

较少字段进行覆盖索引消除回表后计划较好，但因使用的字段较多且有函数使用，无法对所有字段进行覆盖索引。

通过ROWID消除回表

通过ROWID，简化和修改语句，尝试消除回表，消除回表后执行计划较好且执行速度提升，改造后语句：

    select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR

where QYMC like %星际联盟%

                        or

                      TYSHXYDM like %星际联盟% 

                       order by

                        HZRQ desc 

                         limit 0,50;

执行计划：                                                            

1   #NSET2: [0, 50, 116]

2     #PRJT2: [0, 50, 116]; exp_num(1), is_atom(FALSE)

3       #TOPN2: [0, 50, 116]; top_num(50), top_off(0)

4         #SLCT2: [0, 100, 116]; (exp11 > 0 OR exp11 > 0)

5           #SSCN: [0, 100, 116]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

改造语句验证

改造原语句，使用ROWID做子查询进行关联消除回表，外部查询通过聚集索引数据定位,得到了优化；

select

        UUID

,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,yyyy-MM-dd)ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,yyyy-MM-dd)HZRQ,ZS,ZCDYB,

TO_CHAR(FZRQ,yyyy-MM-dd)FZRQ,TO_CHAR(CLRQ,yyyy-MM-dd)CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,yyyy-MM-dd)YYQXZHI,TO_CHAR(YYQXZI,yyyy-MM-dd)YYQXZI,

XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR

WHERE ROWID IN

(

select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR

                where QYMC like %星际联盟%

                        or

                      TYSHXYDM like %星际联盟% 

                       order by

                        HZRQ desc 

                         limit 0,50          

        );

执行计划

1   #NSET2: [457, 50, 1258]

2     #PRJT2: [457, 50, 1258]; exp_num(31), is_atom(FALSE)

3       #NEST LOOP INDEX JOIN2: [457, 50, 1258]

4         #PRJT2: [446, 50, 116]; exp_num(1), is_atom(FALSE)

5           #DISTINCT: [446, 50, 116]

6             #PRJT2: [444, 50, 116]; exp_num(1), is_atom(FALSE)

7               #TOPN2: [444, 50, 116]; top_num(50), top_off(0)

8                 #SLCT2: [444, 160805, 116]; (exp11 > 0 OR exp11 > 0)

9                   #SSCN: [444, 3216107, 116]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

10        #CSEK2: [2, 1, 0]; scan_type(ASC), INDEX33570817(XYGX_GS_TYSHXYDM_FR), scan_range[DMTEMPVIEW_19959271.colname,DMTEMPVIEW_19959271.colname]

看完上述内容，你们对like order by top组合语句的优化是怎样的有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。