| bit confused about Execution Plan [message #206991] |
Sun, 03 December 2006 02:43  |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Hellow Everyone,
I am a bit confused about the topic which i red from Sybex Oracle 9i Performance & Tunning Book.
plz look at the SQL Statement,Execution Plan & Interpretation below.
Explain plan for
Select dist.distributor_id,
dist.city,
dist.state,
dist.Zipcode,
district.name,
emp.lastname from
distributer dist,district,employe emp where
emp.employee_id=dist.manager_id and
disttrict.district_id=dist.district_id;
Execution Plan
8 select Statement
7 Nested Loops
4 Nested Loops
1 Table Access Full Distributor
3 Table Access By Index Rowid Employee
2 Index Unique Scan Employee_id_pk
6 Table Access by Index Rowid District
5 Index Unique Scan District_id_pk
Explain Plan Interpretation
In General, Explain Plan output is interpretted by starting at the innermost operation in the Explain Plain. This operation is always executed first, Unless it is an index access.In this case the table operation directly above the index access would begin the execution Plan. From this Starting Point, you examine the explain plan's remaining operations, working your way up & out towards the left most operation in the plan.
Plz Clear the following doubts in the above Execution Plan:
plz explain how the statements will be executed in the above Sequence i.e;
Table Access Full Distributor will be executed first & the rest .
Thanks in Advance
|
|
|
|
| Re: bit confused about Execution Plan [message #206994 is a reply to message #206991] |
Sun, 03 December 2006 05:42  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
8 select Statement
7 Nested Loops
4 Nested Loops
1 Table Access Full Distributor
3 Table Access By Index Rowid Employee
2 Index Unique Scan Employee_id_pk
6 Table Access by Index Rowid District
5 Index Unique Scan District_id_pk
#1 - For each row in Distributor table /* FULL table scan */ LOOP
# Get EMP_ID value
#2 - Access with that value Employee_id_pk index and get there a single ROWID
#3 - Use that ROWID to access a single row in Employee table
# Get DISTRICT_ID value
#5 - Access with that value District_id_pk and get there a single ROWID
#6 - Use that ROWID to access a single row in District table
# END LOOP
HTH
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
