 How to read the execution plan [message #121806] |
Wed, 01 June 2005 02:54  |
shibu
Messages: 6
Registered: October 2000
|
Junior Member |
|
|
Hi all,
Can anybody help me in reading the execution plan of the following query. The purpose of the query is to get the count of records where the price of the part is greater than 4 times of the minimum price.
SELECT source,NVL (location,subentity_cd) location,COUNT (*)
FROM
(SELECT MIN (us_po_unit_pr) * 4 min_price,part_nr
FROM receipts_fact alias2
WHERE alias2.source = :source
AND NVL(alias2.location,alias2.subentity_cd) = :location
AND trans_type_cd = 'Recpt'
AND us_po_unit_pr <> 0
AND us_po_unit_pr IS NOT NULL
AND alias2.reference_dt BETWEEN
ADD_MONTHS (TRUNC (SYSDATE,'MON'),-1)
AND TRUNC (SYSDATE, 'MON') - 1
GROUP BY part_nr) alias3, combined_receipts alias1
WHERE alias1.source = :source
AND NVL (alias1.location,alias1.subentity_cd) = :location
AND trans_type_cd = 'Recpt'
AND reference_dt BETWEEN ADD_MONTHS (TRUNC (SYSDATE,'MON'),-1 )
AND TRUNC (SYSDATE, 'MON') - 1
AND alias1.part_nr = alias3.part_nr
AND alias1.us_po_unit_pr > alias3.min_price
GROUP BY source,NVL (location, subentity_cd)
And the plan is as follows
ID PID OPERATION OPTIONS OBJECT_NAME
----------------------------------------------------------------
0 | |SELECT STATEMENT| |
1 | 0 |SORT |GROUP BY |
2 | 1 |VIEW | |
3 | 2 |FILTER | |
4 | 3 |SORT |GROUP BY |
5 | 4 |FILTER | |
6 | 5 |TABLE ACCESS |BY LOCAL INDEX ROWID|COMBINED_RECEIPTS
7 | 6 |NESTED LOOPS | |
8 | 7 |TABLE ACCESS |FULL |RECEIPTS_FACT
9 | 7 |INDEX |RANGE SCAN |CR_PNR_INDX
CR_PNR_INDX is a non unique index on part_nr field in combined_receipts table.
My understanding is that the - the inline view will get executed first and the records from the combined_receipts table is checked against the values in the inline view. When I look at the execution plan I'm little bit confused.
Thanks in advance.
Shibu
-
Attachment: eplan.txt
(Size: 0.57KB, Downloaded 1859 times)
|
|
|
|
|
|
| Re: How to read the execution plan [message #122118 is a reply to message #121980] |
Fri, 03 June 2005 00:28  |
shibu
Messages: 6
Registered: October 2000
|
Junior Member |
|
|
Hi Nabeelkhan,
Thanks for your response.
I know how to interpret an execution plan but this one is quite confusing. I've explained my view on how the query will be executed but the Oracle gave me altogether a different plan.
Regards,
Shibu
|
|
|
|
|
|
|
|
|
|
| Re: How to read the execution plan [message #122433 is a reply to message #121806] |
Mon, 06 June 2005 09:06 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
It looks to me like your receipts_facts table, which is the only one in the inline view, is in fact getting read first. Steps 8 and 9, with a parent step of 7, are going first. Then step 6, the combined_receipts table, comes into the picture. What makes you think otherwise?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
