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

This article mainly introduces the SQL optimization of how to use the index, the article is very detailed, has a certain reference value, interested friends must read it!

The following sql executes the result in 30 seconds. Look at the like in the sql predicate. We know that such a statement in the predicate is indexed (the table name and some columns have been renamed to protect the customer's privacy).

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

SELECT / * + 1 percent /

CHECKNUM AS PINGZBSM

CHECKDATE

XXXMODE

XXXRESULT

(SELECT RESULT

FROM (select ID,to_char (WMSYS.WM_CONCAT (xxxnum | | xxxtype | | xxxmode | | xxxresult)) RESULT

From OOOO_XXXCHECKLOG

WHERE CHECKDATE BETWEEN DATE'2018-05-04 'AND DATE'2018-05-04' and xxxtype like'% PAR'

GROUP BY ID

) b where b.id=a.id

) RESULT

CLERKNUM AS CHECKNUM

FROM OOO_XXXECHECKLOG A

Logic reading more than 6 million. Check the index as follows

The amount of data returned by table filtering is as follows:

one

two

three

four

five

six

SQL > select count (*) from OOOO_XXXCHECKLOG

2799616

Select count (*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04 'AND DATE'2018-05-04' and xxxtype like'% PAR'

12856

Select count (*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04'

197984

According to the data returned from the query, because xxxtype does not walk through the index, you have to return to the table 197984 times through the index, and only 12856 times if you leave the index.

Let's set up the REVERSE index IDX_ID_TYPE_RE

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

SELECT / * + OOOO_XXXCHECKLOG index (IDX_ID_TYPE_RE) 2 hours /

CHECKNUM AS PINGZBSM

CHECKDATE

XXXMODE

XXXRESULT

(SELECT RESULT

FROM (select ID,to_char (WMSYS.WM_CONCAT (xxxnum | | xxxtype | | xxxmode | | xxxresult)) RESULT

From OOOO_XXXCHECKLOG

WHERE CHECKDATE BETWEEN DATE'2018-05-04 'AND DATE'2018-05-04' and REVERSE (xxxtype) like 'RAP%'

GROUP BY ID

) b where b.id=a.id

) RESULT

CLERKNUM AS CHECKNUM

FROM OOO_XXXECHECKLOG A

Check the execution plan as follows, the logical read will be 3 million, but the time will remain at 18 seconds, the fundamental reason is that the index has been accessed 7 million times due to scalar quantum query problems.

Let's rewrite sql as follows

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

SELECT / * + index (OOOO_XXXCHECKLOG IDX_ID_TYPE_RE) 3 minutes /

CHECKNUM AS PINGZBSM

CHECKDATE

XXXMODE

XXXRESULT

B.RESULT

CLERKNUM AS CHECKNUM

FROM OOO_XXXECHECKLOG A

Left join (select ID,to_char (WMSYS.WM_CONCAT (xxxnum | | xxxtype | | xxxmode | | xxxresult)) RESULT

From OOOO_XXXCHECKLOG

WHERE CHECKDATE BETWEEN DATE'2018-05-04 'AND DATE'2018-05-04' and REVERSE (xxxtype) like 'RAP%'

GROUP BY ID

) b on b.id=a.id

Index_skip_scan appears in the execution plan.

Let's create the following index:

one

Create index idx_date_seal_re on OOOO_XXXCHECKLOG (CHECKDATE,REVERSE (xxxtype))

As you can see, logical reads are down to 64424, and 50 physical reads are due to the fact that the index has just been created, and the sql is also out.

The above is all the content of the article "how SQL optimizes how to use indexing". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.