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:
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!!!