[Sql tuning of the implementation plan] merge semi join and merge anti join

2010-10-27  来源:本站原创  分类:Database  人气:124 

【Sql tuning of the implementation plan】 merge semi join and merge anti join

Semi Join (also called half-connections) or more in the sub-query in the use of such exists, for the outer row set, look for the internal (ie, sub-query) row set, match back after the first line, not down, look for example:

SQL> select b. *

2 from scott.dept b

3 where b.deptno in (select deptno from scott.emp a)

4;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

Execution Plan

-------------------------------------------------- --------

Plan hash value: 1090737117

-------------------------------------------------- --------------------------------------

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

-------------------------------------------------- --------------------------------------

| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17) | 00:00:01 |

| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17) | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0) | 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| * 4 | SORT UNIQUE | | 14 | 42 | 4 (25) | 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0) | 00:00:01 |

-------------------------------------------------- --------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- -

4 - access ("B". "DEPTNO" = "DEPTNO")

filter ("B". "DEPTNO" = "DEPTNO")

Statistics

-------------------------------------------------- --------

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

614 bytes sent via SQL * Net to client

400 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

1 sorts (memory)

0 sorts (disk)

3 rows processed

SQL>

Or:

SQL> select b. *

2 from scott.dept b

3 where exists (select 1 from scott.emp a where a.deptno = b.deptno)

4;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

Execution Plan

-------------------------------------------------- --------

Plan hash value: 1090737117

-------------------------------------------------- --------------------------------------

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

-------------------------------------------------- --------------------------------------

| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17) | 00:00:01 |

| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17) | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0) | 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| * 4 | SORT UNIQUE | | 14 | 42 | 4 (25) | 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0) | 00:00:01 |

-------------------------------------------------- --------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- -

4 - access ("A". "DEPTNO" = "B". "DEPTNO")

filter ("A". "DEPTNO" = "B". "DEPTNO")

Statistics

-------------------------------------------------- --------

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

614 bytes sent via SQL * Net to client

400 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

1 sorts (memory)

0 sorts (disk)

3 rows processed

SQL>

In this case you can see, in and exsits the execution plan is identical, and both use the merge join semi's oporation

But not in or not exists is different, Oracle7.3 version not exists and not in prior use also tilter, merge anti join and hash anti join access path is then increased.

Example:

SQL> select b. *

2 from scott.dept b

3 where not exists (select 1 from scott.emp a where a.deptno = b.deptno)

4;

DEPTNO DNAME LOC

---------- -------------- -------------

40 OPERATIONS BOSTON

Execution Plan

-------------------------------------------------- --------

Plan hash value: 1353548327

-------------------------------------------------- --------------------------------------

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

-------------------------------------------------- --------------------------------------

| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17) | 00:00:01 |

| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17) | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0) | 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| * 4 | SORT UNIQUE | | 14 | 42 | 4 (25) | 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0) | 00:00:01 |

-------------------------------------------------- --------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- -

4 - access ("A". "DEPTNO" = "B". "DEPTNO")

filter ("A". "DEPTNO" = "B". "DEPTNO")

Statistics

-------------------------------------------------- --------

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

535 bytes sent via SQL * Net to client

400 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

Here is the merge join anti (also called anti-connection), and semi contrary, only the external line when it does not match the internal return.

And, not in you, and not exsits different execution plan shows that the filter:

SQL> select b. *

2 from scott.dept b

3 where b.deptno not in (select deptno from scott.emp a)

4;

DEPTNO DNAME LOC

---------- -------------- -------------

40 OPERATIONS BOSTON

Execution Plan

-------------------------------------------------- --------

Plan hash value: 3547749009

-------------------------------------------------- -------------------------

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

-------------------------------------------------- -------------------------

| 0 | SELECT STATEMENT | | 3 | 60 | 7 (0) | 00:00:01 |

| * 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 00:00:01 |

| * 3 | TABLE ACCESS FULL | EMP | 2 | 6 | 2 (0) | 00:00:01 |

-------------------------------------------------- -------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- -

1 - filter (NOT EXISTS (SELECT / * + * / 0 FROM "SCOTT". "EMP" "A" WHERE

LNNVL ("DEPTNO" <>: B1)))

3 - filter (LNNVL ("DEPTNO" <>: B1))

Statistics

-------------------------------------------------- --------

1 recursive calls

0 db block gets

19 consistent gets

5 physical reads

0 redo size

535 bytes sent via SQL * Net to client

400 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

We know not in and can not be equated with not exsits, from the point of view the execution plan, not in the implementation plan of the operation is a filter, and the form and appearance are the full table, do not use the index, and view information from the predicate, operation 1:

1 - filter (NOT EXISTS (SELECT / * + * / 0 FROM "SCOTT". "EMP" "A" WHERE

LNNVL ("DEPTNO" <>: B1)))

Null value is not in effect on large, if slightly changed a bit this query, you have different:

SQL> select b. *

2 from scott.dept b

3 where b.deptno not in (select nvl (deptno, 0) from scott.emp a);

DEPTNO DNAME LOC

---------- -------------- -------------

40 OPERATIONS BOSTON

Execution Plan

-------------------------------------------------- --------

Plan hash value: 1353548327

-------------------------------------------------- --------------------------------------

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

-------------------------------------------------- --------------------------------------

| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17) | 00:00:01 |

| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17) | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0) | 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| * 4 | SORT UNIQUE | | 14 | 42 | 4 (25) | 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0) | 00:00:01 |

-------------------------------------------------- --------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------- -

4 - access ("B". "DEPTNO" = NVL ("DEPTNO", 0))

filter ("B". "DEPTNO" = NVL ("DEPTNO", 0))

Statistics

-------------------------------------------------- --------

1 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

535 bytes sent via SQL * Net to client

400 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

Curiously, the index used here, because to tell the oracle will not have null values, but also changed the predicate information:

4 - access ("B". "DEPTNO" = NVL ("DEPTNO", 0))

This is not in use need attention.

Transfer from: http://space.itpub.net/16179598/viewspace-671211

相关文章
  • [Sql tuning of the implementation plan] merge semi join and merge anti join 2010-10-27

    [Sql tuning of the implementation plan] merge semi join and merge anti join Semi Join (also called half-connections) or more in the sub-query in the use of such exists, for the outer row set, look for the internal (ie, sub-query) row set, match back

  • sql optimization of the implementation plan for 2011-03-19

    Methods for implementation of the plan: 1, execute sql statements explain plan, the query output table explain for statement required statement related to the underlying tables and views can be dbms_xplan.display access query execution schedule 2, th

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • Oracle: implementation plan: access path connection table, suggesting Statistical information bind variables 2011-09-09

    SQL execution: Analysis: Hard analysis, soft analysis Implementation Plan: access path (access path), table join (table join), statistics (statistics), bind variable (bind variable), tips (hints) ================================================== ===

  • Analysis of the implementation plan optimization SQL <2> ORACLE optimizer (change) 2010-05-27

    Optimizer is sometimes called the query optimizer, which is affecting database query performance because the most important part, do not think that only the SELECT statement is the query. In fact, with any WHERE conditions DML (INSERT, UPDATE, DELETE

  • Oracle SQL with AutoTRACE analysis of the implementation plan 2010-09-06

    Turn http://hi.baidu.com/bystander1983/blog/item/d6c26a53fe457b040cf3e3cd.html * Environment: windowsXP + Oracle10gR2 * AutoTRACE is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool * / AUTOTRACE is a function of

  • Transfer: ORACLE SQL TUNING 2011-05-10

    Reprinted ORACLE SQL TUNING One. Optimizer mode ORACLE optimizer There are 3 kinds: a. RULE (rule-based) b. COST (based on cost) c. CHOOSE (optional) In order to use cost-based optimizer (CBO, Cost-Based Optimizer), you must be regularly updated stat

  • Turn: ORACLE SQL TUNING 2011-05-10

    Reproduced ORACLE SQL TUNING One. Optimizer mode ORACLE optimizer total of three kinds: a. RULE (rule-based) b. COST (based on cost) c. CHOOSE (optional) In order to use cost-based optimizer (CBO, Cost-Based Optimizer), you must be regularly updated

  • How to see Oracle implementation plan 2011-01-14

    oracle explain an implementation plan. Related Concepts 1 · rowid, pseudo-column: is the system to add their own, each table has a pseudo-column is not physically exist. It can not be modified, deleted, and added, rowid in the life cycle of the line

  • Oracle Implementation Plan Detailed 2011-05-06

    Oracle Implementation Plan Detailed --- Of: TTT BLOG This article addresses: http://blog.chinaunix.net/u3/107265/showart_2192657.html --- Description: Full details of this oracle concepts related to the implementation plan, access to the data access

  • Why Oracle will sometimes use the index to find data? - Force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use SQL, a query to the database when you release, Oracle will be generated with an "implementation plan" that is, what kind of data that the statement will search through the implementation of the program. Search for program sele

  • My SQL tuning method 2011-07-22

    Three key points: Implementation plan Index Queries in the query set size of each step My SQL tuning steps: 1 found that slow query, analysis of their implementation plan (2) a reasonable index, re-analysis test 3. If, after 1, 2, performance is stil

  • Implementation Plan (Execution Plan) process analysis 2011-01-11

    Simply put, the implementation plan is actually constituted by a series of data processing functions of a tree chain; those who have access to the physical data of the function is called the access path (Access Path, such as the Index Range Scan, Ful

  • Oracle SQL Tuning simple 2011-01-27

    Today, just to see a simple SQL tuning, they also tried to optimize. Reads as follows: http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-291732.html Also made ​​a simple test and do an extension: Oracle database, there is a

  • Detailed implementation plan 2011-03-09

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of d

  • ORACLE implementation plan of some basic concepts 2 2011-03-22

    ORACLE implementation plan of some basic concepts (2) III. The connection between the table Join an attempt to combine the two predicate table, one can only connect two tables, table join is also called table association. In the following description

  • Why Oracle will sometimes use the index to find data? - To force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use the SQL language, a query to the database release, Oracle will have with an "implementation plan", that is what the statement by the data search program execution. Search program selection is closely related to the Oracle opti

  • SQL Tuning 34 [SQL Collection] 2011-06-26

    SQL Tuning 34 [SQL Collection] Description: Very practical SQL optimization, but also developers write SQL required attention, very grateful to the author's dedication, to do the classic article collection for learning exchanges. Not only do we have

  • Oracle Implementation Plan (Explain Plan) Description 2011-07-07

    http://blog.csdn.net/tianlesoftware/article/details/5827245 http://blog.csdn.net/tianlesoftware/article/details/5826546 If you want to analyze the SQL performance on a particular issue, usually we have to look at the SQL execution plan to see each st

  • How to read ORACLE implementation plan 2011-08-11

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of d