| Different Plan for Inner and Outer Query [message #164293] |
Wed, 22 March 2006 16:21  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi All
Hi
This statement takes forever
UPDATE img set img_arc_in = 1, img_shrt_term_in = 0, img_cache_dt = NULL
WHERE EXISTS
(
SELECT img.img_id
FROM lockbox_cust, bat, image_view, img
WHERE lockbox_cust.cust_id = 1908
AND bat.lockbox_id = lockbox_cust.lockbox_id
AND image_view.bat_id = bat.bat_id
AND image_view.img_id = img.img_id
AND bat.cr_dt < '01-JAN-2006'
)
If i hardcode the values of inner select statement it updates in 5 seconds.
here is the Explain Plan for Above Update with Inner Select Statement
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
UPDATE STATEMENT Optimizer Mode=CHOOSE 37 M 17960
UPDATE R1APP.IMG
FILTER
TABLE ACCESS FULL R1APP.IMG 37 M 287 M 17960
NESTED LOOPS 18 K 799 K 21077
NESTED LOOPS 18 K 690 K 2463
NESTED LOOPS 812 20 K 839
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST 3 24 2
INDEX RANGE SCAN R1APP.XIF_LOCCUS_CUSID 3 1
TABLE ACCESS BY INDEX ROWID R1APP.BAT 295 5 K 279
INDEX RANGE SCAN R1APP.XIF_BAT_LOCID 321 2
VIEW R1APP.IMAGE_VIEW 23 276 2
UNION-ALL PARTITION
CONCATENATION
NESTED LOOPS 1 58 10
NESTED LOOPS 1 52 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.PYMT 1 18 3
INDEX RANGE SCAN R1APP.XAK_PYM_TXNID_PYMID 1 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
NESTED LOOPS 1 58 10
NESTED LOOPS 1 52 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.PYMT 1 18 3
INDEX RANGE SCAN R1APP.XAK_PYM_TXNID_PYMID 1 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
CONCATENATION
NESTED LOOPS 2 108 11
NESTED LOOPS 2 96 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.DOC 2 28 3
INDEX RANGE SCAN R1APP.XIF_DOC_TXNID 2 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
NESTED LOOPS 2 108 11
NESTED LOOPS 2 96 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.DOC 2 28 3
INDEX RANGE SCAN R1APP.XIF_DOC_TXNID 2 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
Here is the Inner Select Statement and Plan
SELECT img.img_id
FROM lockbox_cust,
bat,
image_view,
img
WHERE lockbox_cust.cust_id = 1908
AND bat.lockbox_id = lockbox_cust.lockbox_id
AND image_view.bat_id = bat.bat_id
AND image_view.img_id = img.img_id
AND bat.cr_dt < '01-JAN-2006'
And the Explain plan for it
SELECT
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 18 K 21077
NESTED LOOPS 18 K 799 K 21077
NESTED LOOPS 18 K 690 K 2463
NESTED LOOPS 812 20 K 839
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST 3 24 2
INDEX RANGE SCAN R1APP.XIF_LOCCUS_CUSID 3 1
TABLE ACCESS BY INDEX ROWID R1APP.BAT 295 5 K 279
INDEX RANGE SCAN R1APP.XIF_BAT_LOCID 321 2
VIEW R1APP.IMAGE_VIEW 23 276 2
UNION-ALL PARTITION
CONCATENATION
NESTED LOOPS 1 58 10
NESTED LOOPS 1 52 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.PYMT 1 18 3
INDEX RANGE SCAN R1APP.XAK_PYM_TXNID_PYMID 1 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
NESTED LOOPS 1 58 10
NESTED LOOPS 1 52 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.PYMT 1 18 3
INDEX RANGE SCAN R1APP.XAK_PYM_TXNID_PYMID 1 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
CONCATENATION
NESTED LOOPS 2 108 11
NESTED LOOPS 2 96 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.DOC 2 28 3
INDEX RANGE SCAN R1APP.XIF_DOC_TXNID 2 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
NESTED LOOPS 2 108 11
NESTED LOOPS 2 96 9
NESTED LOOPS 1 34 6
NESTED LOOPS 1 22 4
NESTED LOOPS 1 18 4
TABLE ACCESS BY INDEX ROWID R1APP.BAT 1 10 3
INDEX UNIQUE SCAN R1APP.XPK_BAT 1 2
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX 1 8 1
INDEX UNIQUE SCAN R1APP.XPK_LOC 1
INDEX UNIQUE SCAN R1APP.XPK_SIT 1 4
INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID 1 12 2
TABLE ACCESS BY INDEX ROWID R1APP.DOC 2 28 3
INDEX RANGE SCAN R1APP.XIF_DOC_TXNID 2 2
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
INDEX UNIQUE SCAN R1APP.XPK_IMG 1 6 1
In the First plan it is doing FTS of img table, may be because i am not issuing the where clause, since it is somebody's
else code i have to tune now, If you people have some ideas for this,i will be thankful.
Thanks
[Updated on: Wed, 22 March 2006 16:22] Report message to a moderator |
|
|
|
|
|
| Re: Different Plan for Inner and Outer Query [message #164483 is a reply to message #164307] |
Thu, 23 March 2006 11:07  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Thanks Ross for replying,
Looking at my select statement and update do you think Updatable View is possible i was going throgh your Document, it says join critreria should be = and other conditions,
Ross what else can be done with this update,
Thanks
|
|
|
|
| Re: Different Plan for Inner and Outer Query [message #164559 is a reply to message #164483] |
Fri, 24 March 2006 00:10 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try this UPDATE img
set img_arc_in = 1
, img_shrt_term_in = 0
, img_cache_dt = NULL
WHERE rowid IN (
SELECT img.rowid
FROM lockbox_cust, bat, image_view, img
WHERE lockbox_cust.cust_id = 1908
AND bat.lockbox_id = lockbox_cust.lockbox_id
AND image_view.bat_id = bat.bat_id
AND image_view.img_id = img.img_id
AND bat.cr_dt < '01-JAN-2006'
)
_____________
Ross Leishman
|
|
|
|
|
|
| Re: Different Plan for Inner and Outer Query [message #164836 is a reply to message #164657] |
Mon, 27 March 2006 00:16 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| feroze wrote on Sat, 25 March 2006 02:47 |
what could be the reason you changed it to rowid, any significance..
|
Yes, the significance is that it goes faster 
You had an error in your SQL - the inner query was not correllated with the outer query.
You would probably get the same result by removing IMG from the FROM clause of the inner query. The clause AND image_view.img_id = img.img_id would then correlate to the outer query, but this would rely on the optimizer unnesting the query (see Unnesting in the Performance Tuning Manual). Unnesting is not always the default behaviour, depending on your database version and possibly some other factors.
You could unnest it yourself as follows:UPDATE img set img_arc_in = 1, img_shrt_term_in = 0, img_cache_dt = NULL
WHERE img_id IN (
(
SELECT image_view.img_id
FROM lockbox_cust, bat, image_view
WHERE lockbox_cust.cust_id = 1908
AND bat.lockbox_id = lockbox_cust.lockbox_id
AND image_view.bat_id = bat.bat_id
AND bat.cr_dt < '01-JAN-2006'
)
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
