| Query on Explain Plan Steps [message #535562] |
Wed, 14 December 2011 22:01  |
 |
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
Please refer the following execution plan
I was assuming
1) The table T2 will be Full scanned
2) In a Nested loop for every value retuned in step 1) above index on T1 will be accessed
3) For every value accessed from index on T1, value will be retrived from table T1
However from the plan below it seems
1) The table T2 will be Full scanned
2) In a Nested loop for every value retuned in step 1) above index on T1 will be accessed
Now "after all iteration of the loop are completed" and values are retrieved from table T1 (out of the loop)
Please help me understand it
Also please let me know how we can print execution step nos. in the explain plan
Regards
OraPratap
<font size="1"><font size="4"><font size="4">
select /*+ gather_plan_statistics ordered use_nl(t1) index(t1) */ count(t1.n2), count(t2.n2) from t2, t1 where t2.n2 = 45 and t1.n1 = t2.n1 ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 146 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 225 |00:00:00.01 | 146 |
| 3 | NESTED LOOPS | | 1 | 225 | 241 |00:00:00.02 | 116 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 15 | 15 |00:00:00.01 | 99 |
|* 5 | INDEX RANGE SCAN | T_I1 | 15 | 15 | 225 |00:00:00.01 | 17 |
----------------------------------------------------------------------------------------------- [/size][/size][/size]
[Updated on: Wed, 14 December 2011 22:03] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query on Explain Plan Steps [message #535620 is a reply to message #535571] |
Thu, 15 December 2011 05:50  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
In this particular case, the operation at step 2 is repeated for every row returned by step 3 as they are returned. Step 1 is different though; it collects all rows from child step 2 before it passes any back.
Here's an article that might help http://www.orafaq.com/node/1420
There are only a few actions that "collect" data before passing to the parent step - most pass rows on as soon as they get them. SORT is the main one (aggregation, distinct, sort-merge join, UNION). If you see a VIEW step in a plan then it is almost certainly collecting all child rows in TEMP before passing them on.
Ross Leishman
|
|
|
|
|
|
| Re: Query on Explain Plan Steps [message #535701 is a reply to message #535623] |
Thu, 15 December 2011 14:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If Oracle can find all of the join columns in the index, then it will probably choose Plan 2 - depending on version. I'm pretty sure the "deferred table access" we see in Plan 2 was not around in earlier versions. If it is only scanning the index for SOME of the join columns, it will need to retrieve the others from the table before joining.
I don't know how to get the execution sequence. Once you know how to read a plan, they are kind of redundant.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
