Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the optimization of the like order by top combination statement. Many people may not know much about it. In order to let everyone know more, Xiaobian summarized the following contents for everyone. I hope everyone can gain something according to this article.

Environmental information Basic information

Operating System: CentoOS 7.2

Database version: DM Database Server x64 V7. 1.5.202-Bu1 1d(2017.07.17-82922 ) ENT

Page Size: 16K

Case Sensitive: Yes

Character Set: GB18030

Basic parameters of database

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

table structure

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) ;

table data volume

Query the amount of data in the table

select count(*) from XYGX.XYGX_GS_TYSHXYDM_FR

The query results are as follows. The table contains more than 3.2 million pieces of data.

count(*)

3216107

original sentence

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 'Interstellar %' order by HZRQ desc limit 0,50;

Original implementation plan

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 )= 'Interstellar' AND XYGX_GS_TYSHXYDM. QYMC 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)

Overwrite indexing fewer fields is a good plan after elimination back to the table, but because there are many fields used and functions used, it is impossible to overwrite index all fields.

Elimination of back tables by ROWID

Through ROWID, simplify and modify the statement, try to eliminate the table back, after eliminating the table back, the execution plan is better and the execution speed is improved, and the modified statement:

select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR

where QYMC like '% Star Alliance %'

or

TYSHXYDM like '% Star Alliance %'

order by

HZRQ desc

limit 0,50;

Implementation plan:

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)

modification statement verification

The original sentence is reformed, ROWID is used as sub-query to eliminate association back to table, and the external query is optimized by clustering index data positioning;

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 '% Star Alliance %'

or

TYSHXYDM like '% Star Alliance %'

order by

HZRQ desc

limit 0,50

);

plan of implementation

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]

After reading the above, do you have any further understanding of how to optimize the like order by top combination statement? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.