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

數據庫基本參數

表結構

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_FLAG'1);

調整后執行計劃依然不變，另外開窗口并再清除執行計劃也不變。

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組合語句的優化是怎樣的有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。