Shulou(Shulou.com)06/02 Report--

Create table T1 as select * from dba_objects

Table created.

Create table T2 as select * from dba_objects

Table created.

Create table T3 as select * from dba_objects

Table created.

Create index Wench1 on T1 (object_id)

Index created.

Create index winter2 on T2 (object_id)

Index created.

Create index White3 on T3 (object_id)

Index created.

Exec dbms_stats.gather_table_stats (user,'T1')

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats (user,'t2')

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats (user,'t3')

PL/SQL procedure successfully completed.

Explain plan for

2 select t1.object_id,t1.object_name, t2.object_type

3 from T1, T2 where t1.object_id=t2.object_id and TABLE'

4 and exists (select 1 from T3 where t3.objectroomnameplate WXH 'AND t3.object_id=t1.object_id)

Explained.

Select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 3087771463

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 61 | 79 (2) | 00:00:01 |

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 1 | 61 | 79 (2) | 00:00:01 |

| | 3 | NESTED LOOPS | | 1 | 48 | 77 (2) | 00:00:01 |

| | 4 | SORT UNIQUE | | 1 | 24 | 74 (0) | 00:00:01 |

| | * 5 | TABLE ACCESS FULL | T3 | 1 | 24 | 74 (0) | 00:00:01 |

| | 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 24 | 2 (0) | 00:00:01 |

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

| | * 8 | INDEX RANGE SCAN | Wend2 | 1 | | 1 (0) | 00:00:01 |

| | * 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 13 | 2 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

5-filter ("T3". "OBJECT_NAME" = 'WXH')

7-access ("T3". "OBJECT_ID" = "T1". "OBJECT_ID")

8-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

9-filter ("T2". "OBJECT_TYPE" = 'TABLE')

See that ORACLE expands the subquery and join T3. Take a look at using no_unnest prompts.

Explain plan for

2 select t1.object_id,t1.object_name, t2.object_type

3 from T1, T2 where t1.object_id=t2.object_id and TABLE'

4 and exists (select / * + no_unnest * / 1 from T3 where T3 WXH 'AND t3.object_id=t1.object_id)

Explained.

Select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 2811354197

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 37 | 3609 (1) | 00:00:44 |

| | * 1 | FILTER | | |

| | * 2 | HASH JOIN | | 3458 | 124k | 149K (1) | 00:00:02 |

| | * 3 | TABLE ACCESS FULL | T2 | 3474 | 45162 | 74 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | T1 | 19046 | 446k | 74 (0) | 00:00:01 |

| | * 5 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 24 | 2 (0) | 00:00:01 |

| | * 6 | INDEX RANGE SCAN | Wend3 | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter (EXISTS (SELECT / * + NO_UNNEST * / 0 FROM "T3"T3" WHERE

"T3". "OBJECT_ID" =: B1 AND "T3". "OBJECT_NAME" = 'WXH'))

2-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

3-filter ("T2". "OBJECT_TYPE" = 'TABLE')

5-filter ("T3". "OBJECT_NAME" = 'WXH')

6-access ("T3". "OBJECT_ID" =: B1)

After using the no_unnest prompt, the subquery will not be expanded, and after the T1.T2 completes the join, it will be filtered through the filter operation.

Then take a look at push_subq to force the subquery to join-filter first.

Explain plan for

2 select / * + push_subq (@ tmp) * / t1.objectdesignidrect 1.objectdesignname, t2.object_type

3 from T1, T2 where t1.object_id=t2.object_id and TABLE'

4 and exists (select / * + qb_name (tmp) no_unnest * / 1 from T3 where t3.objectroomnameplates WXH 'AND t3.object_id=t1.object_id)

Explained.

Select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 2880557960

-

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

-

| | 0 | SELECT STATEMENT | | 948 | 35076 | 151 (1) | 00:00:02 |

| | * 1 | HASH JOIN | | 948 | 35076 | 149 (1) | 00:00:02 |

| | * 2 | TABLE ACCESS FULL | T1 | 952 | 22848 | 74 (0) | 00:00:01 |

| | * 3 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 24 | 2 (0) | 00:00:01 |

| | * 4 | INDEX RANGE SCAN | Wend3 | 1 | | 1 (0) | 00:00:01 |

| | * 5 | TABLE ACCESS FULL | T2 | 3474 | 45162 | 74 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

2-filter (EXISTS (SELECT / * + PUSH_SUBQ NO_UNNEST QB_NAME ("TMP") * / 0

FROM "T3"T3" WHERE "T3". "OBJECT_ID" =: B1 AND "T3". "OBJECT_NAME" = 'WXH'))

3-filter ("T3". "OBJECT_NAME" = 'WXH')

4-access ("T3". "OBJECT_ID" =: B1)

5-filter ("T2". "OBJECT_TYPE" = 'TABLE')

The execution plan here shows that it is not professional. It may be that ORACLE is afraid that the format is not good-looking and the FILTER operation is hidden.

-

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

-

| | 0 | SELECT STATEMENT | | 948 | 35076 | 151 (1) | 00:00:02 |

| | * 1 | HASH JOIN | | 948 | 35076 | 149 (1) | 00:00:02 |

| | FILTER | 00 | 00:00:01 |

| | * 2 | TABLE ACCESS FULL | T1 | 952 | 22848 | 74 (0) | 00:00:01 |

| | * 3 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 24 | 2 (0) | 00:00:01 |

| | * 4 | INDEX RANGE SCAN | Wend3 | 1 | | 1 (0) | 00:00:01 |

| | * 5 | TABLE ACCESS FULL | T2 | 3474 | 45162 | 74 (0) | 00:00:01 |

-

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.