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

This article mainly introduces how to rewrite exists to reduce logic reading in oracle. The article is very detailed and has a certain reference value. Interested friends must finish reading it!

Optimize sql by rewriting exists to in or this inner join.

Sql_id:056bs9dzz8mwy

Brief description of the problem: logical reading high.

Sql text:

SELECT A.V., a.rowid

FROM WBANK.WD_BANK_BASEINFOMATION A

WHERE EXISTS (SELECT 1

FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

FROM WBANK.WD_BANK_BASEINFOMATION

WHERE SUBSTR (TYPECODE, 1,3) = '001'

GROUP BY KEYWORD, TYPECODE, INNERCODE

HAVING COUNT (*) 1) B

WHERE A.KEYWORD = B.KEYWORD

AND A.TYPECODE = B.TYPECODE

AND A.INNERCODE = B.INNERCODE)

Execute the plan:

Execution Plan

Plan hash value: 1318914978

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 130 | 7930K (1) | 39:39:10 |

| | * 1 | FILTER | | |

| | 2 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 2640K | 327m | 6249 (2) | 00:01:53 |

| | * 3 | FILTER | | |

| | 4 | SORT GROUP BY NOSORT | | 1 | 47 | 3 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | IDX_WD_B_BI | 1 | 47 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter (EXISTS (SELECT 0 FROM "WBANK". "WD_BANK_BASEINFOMATION")

"WD_BANK_BASEINFOMATION" WHERE "TYPECODE" =: B1 AND "KEYWORD" =: B2 AND "INNERCODE" =: B3 AND

SUBSTR ("TYPECODE", 1Magol 3) = '001' GROUP BY "KEYWORD", "TYPECODE", "INNERCODE" HAVING

COUNT (*) 1))

3-filter (COUNT (*) 1)

5-access ("KEYWORD" =: B1 AND "TYPECODE" =: B2 AND "INNERCODE" =: B3)

Filter ("INNERCODE" =: B1 AND SUBSTR ("TYPECODE", 1p3) = '001')

Statistics

1 recursive calls

0 db block gets

2329554 consistent gets

13 physical reads

0 redo size

2507 bytes sent via SQL*Net to client

513 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

It can be found that the logic reading is as high as 2 million.

When I first saw this sql, I wondered if there would be something wrong with the logic, resulting in an empty result set. After running over, I found that the result set was indeed empty. The innercode column of the subquery is all null. According to the condition A.INNERCODE = B.INNERCODE external table (although it is the same table) there will be no matching results. On second thought, if the subquery innercode column had a non-empty column, there would be no problem. Of course, you still have to ask whether the development result set is necessarily related to the empty column, and if so, you can use this logical relationship to rewrite sql. Of course, this is later.

Take a look at the data distribution:

SQL > select count (*) from WBANK.WD_BANK_BASEINFOMATION

COUNT (*)

-

2645546

SQL > select count (*) from (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

2 FROM WBANK.WD_BANK_BASEINFOMATION

3 WHERE SUBSTR (TYPECODE, 1,3) = '001'

4 GROUP BY KEYWORD, TYPECODE, INNERCODE

5 HAVING COUNT (*) 1)

COUNT (*)

-

one hundred and twenty eight

The outer result set is more than 2.6 million full table data. There are only 128 subquery result sets. According to oracle's handling of exists, it is obviously unreasonable to be driven by external result sets, that is, to execute more than 2.6 million times. If the external result set is large and the internal result set is small, in is usually used in this case, driven by the internal result set, which is executed 128 times.

Verify the problem with the number of executions:

SQL > alter session set statistics_level=all

SQL > SELECT A.D.C., a.rowid

2 FROM WBANK.WD_BANK_BASEINFOMATION A

3 WHERE EXISTS (SELECT 1

4 FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

5 FROM WBANK.WD_BANK_BASEINFOMATION

6 WHERE SUBSTR (TYPECODE, 1,3) = '001'

7 GROUP BY KEYWORD, TYPECODE, INNERCODE

8 HAVING COUNT (*) 1) B

9 WHERE A.KEYWORD = B.KEYWORD

10 AND A.TYPECODE = B.TYPECODE

11 AND A.INNERCODE = B.INNERCODE)

No rows selected

SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST'))

Plan hash value: 1318914978

PLAN_TABLE_OUTPUT

- -

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | | 0 | 000.00 SELECT STATEMENT 09.75 | 2329K |

| | * 1 | FILTER | | 1 | | 0 | 00ju 09.75 | 2329k |

| | 2 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 2640K | 2645K | 000.61 | 12226 |

| | * 3 | FILTER | | 2632K | | 0 | 07.38 | 2317K |

| | 4 | SORT GROUP BY NOSORT | | 2632K | 1 | 1273K | 06.64 | 2317K |

| | * 5 | INDEX RANGE SCAN | IDX_WD_B_BI | 2632K | 1 | 1273K | 00VERV 03.42 | 2317K |

-

PLAN_TABLE_OUTPUT

- -

Predicate Information (identified by operation id):

1-filter (IS NOT NULL)

3-filter (COUNT (*) 1)

5-access ("KEYWORD" =: B1 AND "TYPECODE" =: B2 AND "INNERCODE" =: B3)

Filter (("INNERCODE" =: B1 AND SUBSTR ("TYPECODE", 1p3) = '001'))

31 rows selected.

You can see the starts column section, internal subqueries 2632k times, consistent with the amount of external data.

Rewrite sql with in

SELECT A.V., a.rowid

FROM WBANK.WD_BANK_BASEINFOMATION A

WHERE (A.KEYWORDZA. TYPECODEREA. INERCODE) in (SELECT B.KEYWORDLEX B.TYPECODE B.INERCODE

FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

FROM WBANK.WD_BANK_BASEINFOMATION

WHERE SUBSTR (TYPECODE, 1,3) = '001'

GROUP BY KEYWORD, TYPECODE, INNERCODE

HAVING COUNT (*) 1) B

);

Execute the plan:

Set autotrace on

Execute sql.

Get the execution plan:

Execution Plan

Plan hash value: 1385212545

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 3 | 7008 | 6236 (2) | 00:01:53 |

| | 1 | NESTED LOOPS | | 3 | 7008 | 6236 (2) | 00:01:53 |

| | 2 | NESTED LOOPS | | 3 | 7008 | 6236 (2) | 00:01:53 |

| | 3 | VIEW | VW_NSO_1 | 55 | 118K | 6228 (2) | 00:01:53 |

| | * 4 | FILTER | | |

| | 5 | HASH GROUP BY | | 1 | 2585 | 6228 (2) | 00:01:53 |

| | * 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 26410 | 1212K | 6226 (2) | 00:01:53 |

| | * 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 1 | | 2 (0) | 00:00:01 |

| | 8 | TABLE ACCESS BY INDEX ROWID | WD_BANK_BASEINFOMATION | 1 | 130 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-filter (COUNT (*) 1)

6-filter (SUBSTR ("TYPECODE", 1Jing 3) = '001')

7-access ("A". "KEYWORD" = "KEYWORD" AND "A". "TYPECODE" = "TYPECODE" AND

"A". "INNERCODE" = "INNERCODE")

Filter ("A". "INNERCODE" IS NOT NULL AND "A". "INNERCODE" = "INNERCODE")

Statistics

1 recursive calls

0 db block gets

12226 consistent gets

0 physical reads

0 redo size

2507 bytes sent via SQL*Net to client

513 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

The execution plan has become a table driven by internal subqueries. And logical reading decreased by 10, 000 from 2 million.

Let's verify the number of execution times:

SQL > alter session set statistics_level=all

Execute sql

SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST'))

Get the implementation plan (part):

-

--

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem |

| |

-

--

| | 0 | SELECT STATEMENT | | 1 | | 0 | 0000 SELECT STATEMENT 02.28 | 12226 | |

| |

| | * 1 | HASH JOIN RIGHT SEMI | | 1 | 1311K | 0 | 02.28 | 12226 | 391k |

) |

| | 2 | VIEW | VW_NSO_1 | 1 | 80389 | 128C | 02.28 | 12226 | |

| |

| | * 3 | FILTER | | 1 | 1 | 128 | 0000lv 02.28 | 12226 | |

| |

| | 4 | HASH GROUP BY | | 1 | 4020 | 1607K | 00VERV 00RU 02.17 | 12226 | 710m |

) |

| | * 5 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 1607K | 1607K | 000.78 | 12226 | |

| |

| | * 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 0 | 1311K | 0 | 0 | 000.00 | 00.01 | 0 | |

| |

Found that the implementation plan is not consistent, this is the real implementation plan.

Predicate Information (identified by operation id):

1-access ("A". "KEYWORD" = "KEYWORD" AND "A". "TYPECODE" = "TYPECODE" AND "A". "INNERCODE" = "INNERCODE")

3-filter (COUNT (*) 1)

5-filter (SUBSTR ("TYPECODE", 1Jing 3) = '001')

6-filter ("A". "INNERCODE" IS NOT NULL)

Note

PLAN_TABLE_OUTPUT

- -

-

-cardinality feedback used for this statement

Something about cardinality feedback was found later. There is still a big difference in whether the estimated value is actual. It means that there is something wrong with the statistics.

Viewing statistics was collected in April.

Collect statistics

SQL > exec dbms_stats.gather_table_stats (ownname = > 'WBANK',tabname = >' WD_BANK_BASEINFOMATION',estimate_percent = > 10meme methodological opt = > 'for all columns size repeat',no_invalidate= > false)

PL/SQL procedure successfully completed.

Implementation plan after collecting statistics

Plan hash value: 1385212545

- -

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

- -

| | 0 | SELECT STATEMENT | | 1 | | 2 | 0000 SELECT STATEMENT 02.55 | 12232 |

| | 1 | NESTED LOOPS | | 1 | 3 | 2 | 02.55 | 02.55 | 12232 |

| | 2 | NESTED LOOPS | | 1 | 3 | 2 | 02.55 | 02.55 | 12230 |

| | 3 | VIEW | VW_NSO_1 | 1 | 53 | 129 | 02.55 | 12226 |

| | * 4 | FILTER | | 1 | | 129 | 00VERV 02.55 | 12226 |

| | 5 | HASH GROUP BY | | 1 | 1 | 1607K | 00VLARO 02.40 | 12226 | 710m | 17m | 170m (0) |

| | * 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 26458 | 1607K | 000.80 | 12226 |

| | * 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 129 | 1 | 2 | 00Rank 00.01 | 4 |

| | 8 | TABLE ACCESS BY INDEX ROWID | WD_BANK_BASEINFOMATION | 2 | 1 | 2 | 00lv 00.01 | 2 |

- -

Predicate Information (identified by operation id):

4-filter (COUNT (*) 1)

6-filter (SUBSTR ("TYPECODE", 1Jing 3) = '001')

7-access ("A". "KEYWORD" = "KEYWORD" AND "A". "TYPECODE" = "TYPECODE" AND "A". "INNERCODE" = "INNERCODE")

Filter (("A". "INNERCODE" IS NOT NULL AND "A". "INNERCODE" = "INNERCODE"))

34 rows selected.

You can see that it is indeed 129 times. And there is no cardinality feedback that causes the execution plan to change. Logical reading is still more than 10,000.

It occurred to me that we could also use the method of inner join to rewrite sql.

SELECT A.V., a.rowid

FROM WBANK.WD_BANK_BASEINFOMATION A

Inner join (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

FROM WBANK.WD_BANK_BASEINFOMATION

WHERE SUBSTR (TYPECODE, 1,3) = '001'

GROUP BY KEYWORD, TYPECODE, INNERCODE

HAVING COUNT (*) 1) B

On A.KEYWORD = B.KEYWORD

AND A.TYPECODE = B.TYPECODE

AND A.INNERCODE = B.INNERCODE

Execute the plan:

Plan hash value: 4254729379

PLAN_TABLE_OUTPUT

- -

- -

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

- -

| | 0 | SELECT STATEMENT | | 1 | | 2 | 0000 SELECT STATEMENT 02.48 | 12232 |

| | 1 | NESTED LOOPS | | 1 | 59 | 2 | 02.48 | 02.48 | 12232 |

| | 2 | NESTED LOOPS | | 1 | 59 | 2 | 02.48 | 02.48 | 12230 |

| | 3 | VIEW | | 1 | 59 | 129 | 02.48 | 12226 |

| | * 4 | FILTER | | 1 | | 129 | 00ju 02.48 | 12226 |

| | 5 | HASH GROUP BY | | 1 | 59 | 1607K | 00VLARO 02.31 | 12226 | 710m | 17m | 168m (0) |

| | * 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 26466 | 1607K | 000.76 | 12226 |

PLAN_TABLE_OUTPUT

- -

| | * 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 129 | 1 | 2 | 00Rank 00.01 | 4 |

| | 8 | TABLE ACCESS BY INDEX ROWID | WD_BANK_BASEINFOMATION | 2 | 1 | 2 | 00lv 00.01 | 2 |

- -

Predicate Information (identified by operation id):

4-filter (COUNT (*) 1)

6-filter (SUBSTR ("TYPECODE", 1Jing 3) = '001')

7-access ("A". "KEYWORD" = "B". "KEYWORD" AND "A". "TYPECODE" = "B". "TYPECODE" AND "A". "INNERCODE" = "B". "INNERCODE")

Filter (("A". "INNERCODE" IS NOT NULL AND "A". "INNERCODE" = "B". "INNERCODE"))

PLAN_TABLE_OUTPUT

- -

34 rows selected.

Logical reading

Statistics

1 recursive calls

0 db block gets

12232 consistent gets

0 physical reads

0 redo size

3083 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

Logical reading is also more than 10,000.

Looking at the execution plan, it is found that the bottleneck is scanning the full table of the table and filtering the condition filter (SUBSTR ("TYPECODE", 1mem3) = '001').

You can consider building a functional index on this column

SQL > select count (*) from wbank.WD_BANK_BASEINFOMATION WHERE SUBSTR (TYPECODE, 1,3) = '001'

COUNT (*)

-

1607674

The total data of the table is only 2645546 and returns 1607674, so it is useless to build an index, so there is no need to build an index.

To sum up. The optimization recommendation is to change sql and change exists to in or inner join:

SELECT A.V., a.rowid

FROM WBANK.WD_BANK_BASEINFOMATION A

WHERE (A.KEYWORDZA. TYPECODEREA. INERCODE) in (SELECT B.KEYWORDLEX B.TYPECODE B.INERCODE

FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

FROM WBANK.WD_BANK_BASEINFOMATION

WHERE SUBSTR (TYPECODE, 1,3) = '001'

GROUP BY KEYWORD, TYPECODE, INNERCODE

HAVING COUNT (*) 1) B

);

Or

SELECT A.V., a.rowid

FROM WBANK.WD_BANK_BASEINFOMATION A

Inner join (select KEYWORD, TYPECODE, INNERCODE, COUNT (*)

FROM WBANK.WD_BANK_BASEINFOMATION

WHERE SUBSTR (TYPECODE, 1,3) = '001'

GROUP BY KEYWORD, TYPECODE, INNERCODE

HAVING COUNT (*) 1) B

On A.KEYWORD = B.KEYWORD

AND A.TYPECODE = B.TYPECODE

AND A.INNERCODE = B.INNERCODE

Logical reading will increase from more than 2 million to more than 10,000.

The above is all the contents of the article "how to rewrite exists to reduce logical Reading in oracle". 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.