Home » RDBMS Server » Performance Tuning » Different Plan for Inner and Outer Query
Different Plan for Inner and Outer Query [message #164293] Wed, 22 March 2006 16:21 Go to next message
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 #164307 is a reply to message #164293] Wed, 22 March 2006 20:37 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The inner query may be fast-ish, but it is not just run once, it is run for EVERY SINGLE ROW in the img table.

You should look into Updateable Join Views. There is info in the Oracle Concepts manual and the SQL Reference manual, and I have written this doc their use.
_____________
Ross Leishman
Re: Different Plan for Inner and Outer Query [message #164483 is a reply to message #164307] Thu, 23 March 2006 11:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #164657 is a reply to message #164559] Fri, 24 March 2006 09:47 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi Ross

Excellent Sir! it run very fast, what could be the reason you changed it to rowid, any significance..


Thanks a Lot !
Re: Different Plan for Inner and Outer Query [message #164836 is a reply to message #164657] Mon, 27 March 2006 00:16 Go to previous message
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 Smile

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'
)
This will probably be faster still, but I didn't want to mess with the structure of your sub-query. Try it if you want.

_____________
Ross Leishman


Previous Topic: information about indexes
Next Topic: Materialized View Based on a Union Query
Goto Forum:
  


Current Time: Thu Mar 28 12:16:28 CDT 2024