| nested loops [message #475316] |
Tue, 14 September 2010 07:11  |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Hi all,
10.2.0.1
I am trying to understand the concept of nested loops.
--------
| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00
:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00
:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00
:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00
:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
Nested loop by defintion means,for every row returned by the outer query,the inner query is executed that many times.
In the above example,oracle does a full table scan and returned 14 rows.Now for dept table,it does a index unique scan and applies the predicate a.deptno=b.deptno and returns 1 row.
My question is why it is returning only 1 row?
That measn f
or every 14 rows,this one row is fetched 14 times.
I know my understand here is not correct,but anyone could make me understand?
Thanks
|
|
|
|
| Re: nested loops [message #475319 is a reply to message #475316] |
Tue, 14 September 2010 07:17  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
For every row in emp there is one matching row in dept.
The access on dept is performed 14 times. Each time it finds 1 row.
|
|
|
|
| Re: nested loops [message #475352 is a reply to message #475316] |
Tue, 14 September 2010 10:04 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
If the access is performed 14 times ,why its showing 1,why not 14?
The predicate should have been applied at number 1 [NESTED LOOPS] that shows 14 rows.
|
|
|
|
|
|
| Re: nested loops [message #475360 is a reply to message #475356] |
Tue, 14 September 2010 10:36 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Thanks for the link Michel but it only tells how to obtain explain plan which i believe i know.
So whenever there is a nested loop,the inner query and predicate would always show 1 row?Don't you think this is misleading as the cost shown would represent the cost of 1 operation.
In case of hash join:
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
In this case the access is at 1 which seems reasonable.
|
|
|
|
|
|
| Re: nested loops [message #475364 is a reply to message #475362] |
Tue, 14 September 2010 10:57 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
My dear Michel,
Observe this part:
1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00
:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00
:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00
:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
:00:01 |
here the access at 4 shows 1 row.As there is about nothing in the docs or anywhere that tells how to interpret explain plan interms of cost,bytes and rows processed to identify PP,so as per my understanding first the optimizer full scans table emp and returns 14 rows .Next for each row of 14,the optimizer scans the index and returns 1 rowid to operation 3.This happens 14 times.What i want to know is in the operation 4,the access 4 actually returns 14 rows but in explain plan it show 1.
That means it is showing as if 1 row is returned but actually its 14.The cost associated with operation 4 is the cost of single operation.
|
|
|
|
|
|
|
|
| Re: nested loops [message #475519 is a reply to message #475411] |
Wed, 15 September 2010 07:47 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
This is the explain plan of a slow query.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 959 | 1353 (1
)| 00:00:17 |
|* 1 | HASH JOIN RIGHT SEMI | | 7 | 959 | 1353 (1
)| 00:00:17 |
| 2 | VIEW | VW_NSO_1 | 275 | 3025 | 20 (0
)| 00:00:01 |
|* 3 | FILTER | | | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | HASH GROUP BY | | 275 | 13750 | 20 (0
)| 00:00:01 |
| 5 | NESTED LOOPS | | 5499 | 268K| 20 (0
)| 00:00:01 |
|* 6 | INDEX FULL SCAN | INDX_GPFEM | 253K| 6673K| 19 (0
)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_MONACT | 1 | 23 | 1 (0
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP_MAST | 1 | 76 | 1 (0
)| 00:00:01 |
| 9 | NESTED LOOPS | | 6115 | 752K| 1333 (1
)| 00:00:16 |
| 10 | TABLE ACCESS BY INDEX ROWID| MON_ACT | 127K| 6222K| 57 (0
)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 | INDEX RANGE SCAN | FUNC_MA | 127K| | 3 (0
)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | EMPCODE_EM | 1 | | 1 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("$nso_col_1"=NVL("GPF_NO",'a'))
3 - filter(COUNT(*)>1)
6 - filter("GPF_NO" IS NOT NULL)
7 - access("A"."TREA_CODE"="B"."TREA_CODE" AND "A"."EMP_CODE"="B"."EMP_CODE")
filter(TO_CHAR(INTERNAL_FUNCTION("UPTO_DATE"),'YYYYMM')='201003')
11 - access(TO_CHAR(INTERNAL_FUNCTION("UPTO_DATE"),'YYYYMM')='201003')
12 - access("A"."TREA_CODE"="B"."TREA_CODE" AND "A"."EMP_CODE"="B"."EMP_CODE")
As per my understanding this is what is happening:
1)Oracle accesses the index indx_gpfem at 6 and applies the predicate at 6 returning 253K rows to its parent 5.
2)For each row in 253K,oracle does a nested loop join retuning 1 row at a time of which only 5499 rows are matching.That means the inner query got executed 253K times but only 5499 rows were matching.
3)It then does a group by and returns 275 rows.
4)It then applies the filter and returns 275 rows to the view.
This is one part of explain plan.IN the second part of explain plan
1)Oracle does a index full scan of index func_ma and applies the predicate 11 returing 127K rows.
2)It then passes 127K rowids to access table mon_act returning 127K rows.
3)For each 127K rows,oracle does a nested loop join of index empcode_em returning only 6115 matching rows.
Now I don't understand how nested loop is a child of table access by index rowid operation.
What is happening at 8,9?Also verify whta i have written.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
