OCP Blog
Super DBA
no_unnest hint, useful hint for subqueries!
by Vazha Mantua Wednesday, May 25, 2011 12:20 PM

Good Day,

Today we discuss about optimize SQL statements with subqueries . In SQL Plan subquery maybe nested or unnested.

Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

  • Uncorrelated IN subqueries

  • IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:

  • You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.

  • You can unnest other subqueries by specifying the UNNEST hint in the subquery.

 

 

Know talk about example how can we improve performance with no_unnest hint.

 

SELECT /*+ index(v1.table1 table1_IX1) */
v1.col1,
v1.col2,
v1.col3,
v1.col4,
v1.col5
FROM VIEW1 v1
WHERE (v1.code = :B1 And v1.ID = Nvl(null, v1.ID) And
v1.ID In
(Select
v2.sid
From VIEW2 v2
Where ('N' = 'N' And v2.Key1 = Nvl(null, Key1) And
NVL(null, Active_Flag) = Active_Flag And
NVL(null, Inform_Flag) = Inform_Flag)
Or ('Y' = 'Y' and :b2 = KEY1 and Active_Flag = 'Y')) )

sql_plan of this statement is :

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 244
| 1 | HASH JOIN SEMI | | 1 | 244
| 2 | NESTED LOOPS OUTER | | 1 | 231
| 3 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 110
| 4 | INDEX RANGE SCAN | TABLE1_IX1 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID | TABLE2 | 1 | 121
| 6 | INDEX UNIQUE SCAN | TABLE2_PK | 1 |
| 7 | VIEW | VW_NSO_1 | 2 | 26
| 8 | CONCATENATION | | |
| 9 | TABLE ACCESS BY INDEX ROWID | TABLE3 | 1 | 21
| 10 | NESTED LOOPS | | 1 | 49
| 11 | NESTED LOOPS | | 1 | 28
| 12 | TABLE ACCESS BY INDEX ROWID| TABLE4 | 1 | 18
| 13 | INDEX UNIQUE SCAN | TABLE4_PK | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID| TABLE5 | 1 | 10
| 15 | INDEX RANGE SCAN | TABLE5_PK | 1 |
| 16 | INDEX RANGE SCAN | TABLE1_IX1 | 1 |
| 17 | TABLE ACCESS BY INDEX ROWID | TABLE5 | 1 | 10
| 18 | NESTED LOOPS | | 1 | 49
| 19 | NESTED LOOPS | | 1 | 39
| 20 | TABLE ACCESS FULL | TABLE3 | 4559 | 95739
| 21 | TABLE ACCESS BY INDEX ROWID| TABLE4 | 1 | 18
| 22 | INDEX UNIQUE SCAN | TABLE4_PK | 1 |
| 23 | INDEX RANGE SCAN | TABLE5_PK | 1 |
--------------------------------------------------------------------------------

Know Write no_unnest hint in subquery:

 

SELECT /*+ index(v1.table1 table1_IX1) */
v1.col1,
v1.col2,
v1.col3,
v1.col4,
v1.col5
FROM VIEW1 v1
WHERE (v1.code = :B1 And v1.ID = Nvl(null, v1.ID) And
v1.ID In
(Select /*+ no_unnest */
v2.sid
From VIEW2 v2
Where ('N' = 'N' And v2.Key1 = Nvl(null, Key1) And
NVL(null, Active_Flag) = Active_Flag And
NVL(null, Inform_Flag) = Inform_Flag)
Or ('Y' = 'Y' and :b2 = KEY1 and Active_Flag = 'Y')) )

Cost of this plan was 9192

 

 

Let’s see new SQL PLAN

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 231 |
| 1 | FILTER | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 231 |
| 3 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 110 |
| 4 | INDEX RANGE SCAN | TABLE1_IX1 | 2 | |
| 5 | TABLE ACCESS BY INDEX ROWID | TABLE2 | 1 | 121 |
| 6 | INDEX UNIQUE SCAN | TABLE2_PK | 1 | |
| 7 | TABLE ACCESS BY INDEX ROWID | TABLE5 | 1 | 10 |
| 8 | NESTED LOOPS | | 1 | 49 |
| 9 | NESTED LOOPS | | 1 | 39 |
| 10 | TABLE ACCESS BY INDEX ROWID| TABLE3 | 3 | 63 |
| 11 | INDEX RANGE SCAN | TABLE3_IX1 | 3 | |
| 12 | TABLE ACCESS BY INDEX ROWID| TABLE4 | 1 | 18 |
| 13 | INDEX UNIQUE SCAN | TABLE4_PK | 1 | |
| 14 | INDEX RANGE SCAN | TABLE5_PK | 1 | |

 

Cost of this plan was 16, it’s 574 times less!!!

Tags:

Filter by APML

Calendar

<<  October 2018  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox