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

This article introduces the relevant knowledge of "how sql uses the index to eliminate sorting". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.1.1 description of phenomenon

We often come across this kind of SQL:

Select * from tab where col1 >'x 'order by col2 desc limit 0pm 5

It takes a long time to execute and needs optimization.

We often encounter this kind of problem when we show it on the home page. Here we introduce two classic optimization ideas.

1.1.2 processing method 1.1.2.1 use environment

Database version: DM Database Server x64 V8.1.0.156-Build (2019.05.05-106384) SEC

Environment: personal pc environment

1.1.2.2 Construction data

Description:

Create a new table with 50 columns and insert 5w data.

Code:

Drop table t_test_1

-- create a table with 50 columns

Declare

Tb_s int default 1

Col_s int default 50

A_sql clob default 'create table tact testworthy'

-- b_sql clob default 'create huge table ht_test_'

Begin

For rrs in 1..tb_s loop

A_sql:=a_sql | | rrs | |'('

-- b_sql=b_sql | | rrs | |'('

For rs in 1..col_s-1 loop

A_sql:=a_sql | | 'col_' | | rs | |' varchar (50),'

-- b_sql=b_sql | | 'col_' | | rs | |' varchar (50),'

End loop

-- a_sql:=a_sql | | 'col_' | | col_s | |' int);';-- without primary key

A_sql:=a_sql | | 'col_' | | col_s | |' int primary key,col_end datetime)'

-- b_sql=b_sql | | 'col_' | | col_s | |' int primary key)'

Execute IMMEDIATE a_sql

-- dbms_output.put_line (a_sql)

-- execute immediate b_sql

-- select cast (b_sql as varchar)

A_sql:='create table tours testworthy'

-- b_sql='create huge table ht_test_'

End loop

NULL

End

-- generate 50000 test data

-- initialize basic data

Declare

Tb_s int default 1

Col_s int default 50

ROW_S INT DEFAULT 50000

A_sql clob default 'INSERT INTO tact testworthy'

Commit_i int default 10000

Begin

For rrs in 1..tb_s loop

-- for rrs in 9..tb_s loop

FOR RRRS IN 1..ROW_S LOOP

A_sql:=a_sql | | rrs | | 'VALUES ('

For rs in 1..col_s-1 loop

A_sql:=a_sql | | 'dbms_random.string (' 'Xerogramma 50),'

End loop

A_sql:=a_sql | | RRRS | |', sysdate-dbms_random.value (1000000))'

EXECUTE immediate a_sql

-- dbms_output.put_line (a_sql)

-- select cast (a_sql as varchar)

A_sql:='INSERT INTO tours testworthy'

Commit_i:=commit_i-1

If (commit_i = 0) then

Commit

Commit_i:=10000

End if

END LOOP

End loop

NULL

End

1.1.2.3 View Plan and Optimization statement 1.1.2.4 SQL, Plan, and execution time to query

Description: all the execution here does not take the first execution time; the execution ratio is [the execution time of the current method / the minimum execution time of all methods]

Description

Deal with

Statement

plan

Execution time (seconds)

Execution ratio

A0 [primitive sentence and plan and execution time]

Null

Select * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0pm 5

1 # NSET2: [217, 5, 2376]

2 # PRJT2: [217,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [2376, 5, 217]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # SLCT2: [38, 2500, 2376]; T_TEST_1.COL_14 >'1'

5 # CSCN2: [38, 50000, 2376]; INDEX33559006 (T_TEST_1)

0.078

9.75

A1 [general optimization 1, adding indexes to col_14]

Create index idx_t_test_1_1 on t_test_1 (col_14)

Select * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0pm 5

1 # NSET2: [190, 5, 2376]

2 # PRJT2: [190,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [190,5, 2376]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1 (T_TEST_1)

5 # SSEK2: [11, 2500, 2376]; scan_type (ASC), IDX_T_TEST_1_1 (T_TEST_1), scan_range

0.324

40.5

B1 [some people will say, the optimization here is wrong, we should bring col_end with us]

Create index idx_t_test_1_2 on t_test_1 (col_14,col_end)

Select * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0pm 5

1 # NSET2: [190, 5, 2376]

2 # PRJT2: [190,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [190,5, 2376]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1 (T_TEST_1)

5 # SSEK2: [11, 2500, 2376]; scan_type (ASC), IDX_T_TEST_1_1 (T_TEST_1), scan_range

0.324

40.5

B2 [col_end desc]

Create index idx_t_test_1_3 on t_test_1 (col_14,col_end desc)

Select * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0pm 5

1 # NSET2: [190, 5, 2376]

2 # PRJT2: [190,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [190,5, 2376]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1 (T_TEST_1)

5 # SSEK2: [11, 2500, 2376]; scan_type (ASC), IDX_T_TEST_1_1 (T_TEST_1), scan_range

0.324

40.5

B2 [adjust the order on the basis of B2]

Create index idx_t_test_1_4 on t_test_1 (col_end desc,col_14)

Select * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0pm 5

1 # NSET2: [190, 5, 2376]

2 # PRJT2: [190,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [190,5, 2376]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1 (T_TEST_1)

5 # SSEK2: [11, 2500, 2376]; scan_type (ASC), IDX_T_TEST_1_1 (T_TEST_1), scan_range

0.324

40.5

B3

Let's see if there are any relevant parameters.

Select * from A8 where para_name like'% TOP%'

Let's change what looks like a related parameter to 1 and take a look at the plan and execution time.

Select/*+TOP_ORDER_OPT_FLAG (1) * / * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0Pol 5

1 # NSET2: [0, 5, 2376]

2 # PRJT2: [0, 5, 2376]; exp_num (52), is_atom (FALSE)

3 # TOPN2: [0, 5, 2376]; top_num (5), top_off (0)

4 # SLCT2: [0,100,2376]; T_TEST_1.COL_14 >'1'

5 # BLKUP2: [0,100,2376]; IDX_T_TEST_1_4 (T_TEST_1)

6 # SSCN: [0,100,2376]; IDX_T_TEST_1_4 (T_TEST_1)

0.008

one

B4

Select/*+TOP_ORDER_OPT_FLAG (1) no_index (tactile testament 1 recording IDX recording Testament Testament 1 # 4) * / * from t_test_1 where T_TEST_1.COL_14 >'1' order by col_end desc limit 0meme 5

1 # NSET2: [190, 5, 2376]

2 # PRJT2: [190,5, 2376]; exp_num (52), is_atom (FALSE)

3 # SORT3: [190,5, 2376]; key_num (1), is_distinct (FALSE), top_flag (1), is_adaptive (0)

4 # BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1 (T_TEST_1)

5 # SSEK2: [11, 2500, 2376]; scan_type (ASC), IDX_T_TEST_1_1 (T_TEST_1), scan_range

0.324

40.5

B5

Select/*+TOP_ORDER_OPT_FLAG (1) * / * from t_test_1 where Tests Tests 1.COLLTHEROUBG 14 records 1 'order by col_end desc limit 0pr 5

1 # NSET2: [0, 5, 2376]

2 # PRJT2: [0, 5, 2376]; exp_num (52), is_atom (FALSE)

3 # TOPN2: [0, 5, 2376]; top_num (5), top_off (0)

4 # SLCT2: [0,100,2376]; T_TEST_1.COL_14 ='1'

5 # BLKUP2: [0,100,2376]; IDX_T_TEST_1_4 (T_TEST_1)

6 # SSCN: [0,100,2376]; IDX_T_TEST_1_4 (T_TEST_1)

0.008

one

B6

Select/*+TOP_ORDER_OPT_FLAG (1) no_index (from t_test_1 where Tests 1 recording IDX recording Tests Tests 1 4) * / * from t_test_1 where Tests Tests 1 Colling 14 colors 1 'order by col_end desc limit 0Pol 5

1 # NSET2: [5, 5, 2376]

2 # PRJT2: [5,5,2376]; exp_num (52), is_atom (FALSE)

3 # TOPN2: [5, 5, 2376]; top_num (5), top_off (0)

4 # BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_3 (T_TEST_1)

5 # SSEK2: [5, 1250, 2376]; scan_type (ASC), IDX_T_TEST_1_3 (T_TEST_1), scan_range

0

B7

Update t_test_1 set col_14='1' where rownum

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.