Home » RDBMS Server » Performance Tuning » Tuning This Query
Tuning This Query [message #169809] Fri, 28 April 2006 15:38 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hello
This is the statement in a procedure, it takes 10 min. to run in Production , I have attached the Query and Explain please give some ideas on it to reduce the time it takes ,
STATS are Updated Daily, tables and indexes are analysed
In the plan the tables which are undergoing FTS are huge tables even though indexes are there, these statement will return around 25-30k rows.
  SELECT distinct j.img_id  , v.cd_vol_nb
  FROM table(cast(pkg_cd_extr.str2tbl('384246') as id_typ)) v,
  (
    SELECT distinct i.IMG_ID, b.bat_id
  FROM IMG i, txn T, DOC D, BAT B, table(cast(pkg_cd_extr.str2tbl('384246') as id_typ)) isobat
  WHERE B.BAT_ID = T.BAT_ID
  AND D.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (D.IMG_FRNT_ID = I.IMG_ID OR D.IMG_REAR_ID = I.IMG_ID)
  AND b.BAT_ID = isobat.column_value
  UNION
 
  SELECT distinct I.IMG_ID, b.bat_id
  FROM IMG I, txn T, PYMT P, BAT B
  WHERE B.BAT_ID = T.BAT_ID
  AND P.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (P.IMG_FRNT_ID = I.IMG_ID OR (P.IMG_REAR_ID = I.IMG_ID AND :P_CHECK_REAR_IMAGE = 1))
  AND b.BAT_ID in (select column_value from table(cast(pkg_cd_extr.str2tbl('384246') as id_typ)))
  ) j
  WHERE j.bat_id = v.column_value
  ORDER BY v.cd_vol_nb ;

Explain Plan-------------
SELECT STATEMENT, GOAL = CHOOSE			Cost=29462	Cardinality=1151116	Bytes=32231248
 SORT UNIQUE			Cost=29462	Cardinality=1151116	Bytes=32231248
  HASH JOIN			Cost=565	Cardinality=1151116	Bytes=32231248
   COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
   VIEW	Object owner=R1APP50		Cost=2065	Cardinality=14093	Bytes=366418
    SORT UNIQUE			Cost=2065	Cardinality=14093	Bytes=661075
     UNION-ALL					
      CONCATENATION					
       HASH JOIN			Cost=87	Cardinality=719	Bytes=29479
        NESTED LOOPS			Cost=75	Cardinality=335	Bytes=13065
         HASH JOIN			Cost=75	Cardinality=344	Bytes=11696
          HASH JOIN			Cost=58	Cardinality=344	Bytes=7912
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=DOC	Cost=12	Cardinality=12509	Bytes=162617
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
          INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
         INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
        COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
       HASH JOIN			Cost=87	Cardinality=719	Bytes=29479
        NESTED LOOPS			Cost=75	Cardinality=335	Bytes=13065
         HASH JOIN			Cost=75	Cardinality=344	Bytes=11696
          HASH JOIN			Cost=58	Cardinality=344	Bytes=7912
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=DOC	Cost=12	Cardinality=12509	Bytes=162617
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
          INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
         INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
        COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
      CONCATENATION					
       FILTER					
        HASH JOIN			Cost=189	Cardinality=44037	Bytes=2069739
         COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
         NESTED LOOPS			Cost=169	Cardinality=20530	Bytes=923850
          HASH JOIN			Cost=169	Cardinality=21067	Bytes=842680
           INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
           HASH JOIN			Cost=117	Cardinality=21067	Bytes=610943
            PARTITION RANGE ALL					
             TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
            PARTITION RANGE ALL					
             TABLE ACCESS FULL	Object owner=R1APP50	Object name=PYMT	Cost=52	Cardinality=24484	Bytes=465196
          INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
       HASH JOIN			Cost=189	Cardinality=44037	Bytes=2069739
        COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
        NESTED LOOPS			Cost=169	Cardinality=20530	Bytes=923850
         HASH JOIN			Cost=169	Cardinality=21067	Bytes=842680
          INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
          HASH JOIN			Cost=117	Cardinality=21067	Bytes=610943
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=PYMT	Cost=52	Cardinality=24484	Bytes=465196
         INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
.


I tried to work on it first thing i removed distinct from 2 places, and used Union all after that the plan is

IMPROVED PLAN ---------------
SELECT STATEMENT, GOAL = CHOOSE			Cost=109519	Cardinality=4857020	Bytes=97140400
 SORT UNIQUE			Cost=109519	Cardinality=4857020	Bytes=97140400
  HASH JOIN			Cost=100	Cardinality=4857020	Bytes=97140400
   COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
   VIEW	Object owner=R1APP50		Cost=77	Cardinality=59464	Bytes=1070352
    UNION-ALL					
     CONCATENATION					
      HASH JOIN			Cost=87	Cardinality=719	Bytes=29479
       NESTED LOOPS			Cost=75	Cardinality=335	Bytes=13065
        HASH JOIN			Cost=75	Cardinality=344	Bytes=11696
         HASH JOIN			Cost=58	Cardinality=344	Bytes=7912
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=DOC	Cost=12	Cardinality=12509	Bytes=162617
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
         INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
        INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
       COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
      HASH JOIN			Cost=87	Cardinality=719	Bytes=29479
       NESTED LOOPS			Cost=75	Cardinality=335	Bytes=13065
        HASH JOIN			Cost=75	Cardinality=344	Bytes=11696
         HASH JOIN			Cost=58	Cardinality=344	Bytes=7912
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=DOC	Cost=12	Cardinality=12509	Bytes=162617
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
         INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
        INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
       COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
     CONCATENATION					
      FILTER					
       HASH JOIN			Cost=189	Cardinality=44037	Bytes=2069739
        COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
        NESTED LOOPS			Cost=169	Cardinality=20530	Bytes=923850
         HASH JOIN			Cost=169	Cardinality=21067	Bytes=842680
          INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
          HASH JOIN			Cost=117	Cardinality=21067	Bytes=610943
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
           PARTITION RANGE ALL					
            TABLE ACCESS FULL	Object owner=R1APP50	Object name=PYMT	Cost=52	Cardinality=24484	Bytes=465196
         INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
      HASH JOIN			Cost=189	Cardinality=44037	Bytes=2069739
       COLLECTION ITERATOR PICKLER FETCH		Object name=STR2TBL			
       NESTED LOOPS			Cost=169	Cardinality=20530	Bytes=923850
        HASH JOIN			Cost=169	Cardinality=21067	Bytes=842680
         INDEX FAST FULL SCAN	Object owner=R1APP50	Object name=XAK_TXN_BATID_TXNID	Cost=16	Cardinality=24437	Bytes=268807
         HASH JOIN			Cost=117	Cardinality=21067	Bytes=610943
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=IMG	Cost=33	Cardinality=23938	Bytes=239380
          PARTITION RANGE ALL					
           TABLE ACCESS FULL	Object owner=R1APP50	Object name=PYMT	Cost=52	Cardinality=24484	Bytes=465196
        INDEX UNIQUE SCAN	Object owner=R1APP50	Object name=XPK_BAT		Cardinality=1	Bytes=5
.

Timings it took was only few seconds less it took around 9 min,
Can i do still some thing on this one.


Thanks

[Updated on: Fri, 28 April 2006 15:38]

Report message to a moderator

Re: Tuning This Query [message #169813 is a reply to message #169809] Fri, 28 April 2006 16:47 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

"FROM table(cast(pkg_cd_extr.str2tbl('384246') as id_typ)) v"

What does the above above table function do?

Give its source code and all type definitions.

Srini
Re: Tuning This Query [message #169883 is a reply to message #169813] Sat, 29 April 2006 20:25 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You say the FTS tables are huge, but yours stats show <100K rows. How big are they really?

You say the query returns 25-30K rows. But how many rows are there before the DISTINCT / UNION gets rid of the duplicates?

What it comes down to is this:
Does the SQL need to read all of the data in the big tables or not? If not, this means that pkg_cd_extr.str2tbl('384246') is returning values for bat_id that will result in a join to a small proportion of the BAT table (hopefully <1%). Otherwise, you're stuffed.

If you will be processing only a small part of the big tables (ie. pkg_cd_extr.str2tbl('384246') is restrictive), then try this:
WITH x AS (
  SELECT /*+ CARDINALITY(isobat 100)*/ 
         t.txn_id, b.bat_id, isobat.v.cd_vol_nb
  FROM   
        table(cast(pkg_cd_extr.str2tbl('384246') as id_typ)) isobat
  ,      BAT B 
  ,      TXN T
  WHERE b.BAT_ID = isobat.column_value
  AND   B.BAT_ID = T.BAT_ID
)
SELECT i.img_id, x.cd_vol_nb
FROM   x
,      IMG i
,      DOC D
AND    D.TXN_ID = X.TXN_ID
AND    (D.IMG_FRNT_ID = I.IMG_ID OR D.IMG_REAR_ID = I.IMG_ID)
AND    I.IMG_SHRT_TERM_IN = 1
UNION
SELECT i.img_id, x.cd_vol_nb
FROM   x
,      IMG i
,      PYMT P
WHERE  P.TXN_ID = X.TXN_ID
AND    I.IMG_SHRT_TERM_IN = 1
AND    (  P.IMG_FRNT_ID = I.IMG_ID 
          OR 
          (  P.IMG_REAR_ID = I.IMG_ID 
             AND    
             :P_CHECK_REAR_IMAGE = 1))


The ideas here are:
- Isolate the repeated portions of the UNION using WITH. Oracle may be able to save some IO.
- UNION performs an implicit DISTINCT. Eliminating the DISTINCTS will save on a SORT-UNIQUE.
- It doesn't seem like you need to join the UNIONed query back to the table-function. You should be able to return everything you need from the inner calls of the same table-function, but I could be wrong.
- Use the CARDINALITY hint to tell Oracle how many rows you expect to be returned from the table function. I have put 100, but you should use a more accurate estimate.
- If you truly only need to process a small proportion of the big tables, then you will need indexes on b.bat_id, t.bat_id, d.txn_id, i.img_id, and p.txn_id. You may also need histograms on these columns. If it continues to use HASH joins, try USE_NL or FIRST_ROWS hints to get it to use Nested Loops and indexes.

Ross Leishman
Re: Tuning This Query [message #169886 is a reply to message #169883] Sat, 29 April 2006 23:42 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Ross and Srini for replying,

Ross with reference to
You say the FTS tables are huge, but yours stats show <100K rows. How big are they really?

The STATS are from Development and in DEV 100K rows are there,
In reality in Production these Big tables have 25-40 million rows.

You say the query returns 25-30K rows. But how many rows are there before the DISTINCT / UNION gets rid of the duplicates?

Yes we will be removing distinct only Union will be used,
moreover we don't need Distinct the other Developers used it, since the records are already Unique and constraints are there.

 then you will need indexes on b.bat_id, t.bat_id, d.txn_id, i.img_id, and p.txn_id

Indexes are already there on these columns,

I will try to impelent the other suggestions given by you, will let you know how it behaves,


Thanks


[Updated on: Sat, 29 April 2006 23:42]

Report message to a moderator

Re: Tuning This Query [message #169890 is a reply to message #169886] Sun, 30 April 2006 01:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Quote:

The STATS are from Development and in DEV 100K rows are there,
In reality in Production these Big tables have 25-40 million rows.

Don't tune your query against your development database if the data volumes and distribution differ from production. Either copy production to development or tune against production.
Re: Tuning This Query [message #170022 is a reply to message #169890] Mon, 01 May 2006 14:31 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks every body for responding

I modified the SQL bit to give values and get the Plan from Production

Here is the query and Plan

 SELECT i.IMG_ID, isobat.cd_vol_nb
  FROM IMG i, txn T, DOC D, BAT B, TABLE (cast(cd_iso_bat_t() as cd_iso_bat_t)) isobat
  WHERE B.BAT_ID = T.BAT_ID
  AND D.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (D.IMG_FRNT_ID = I.IMG_ID OR D.IMG_REAR_ID = I.IMG_ID)
  AND b.BAT_ID = isobat.bat_id  
  UNION
    SELECT I.IMG_ID, isobat.cd_vol_nb
  FROM IMG I, txn T, PYMT P, BAT B, TABLE (cast(cd_iso_bat_t() as cd_iso_bat_t)) isobat
  WHERE B.BAT_ID = T.BAT_ID
  AND P.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (P.IMG_FRNT_ID = I.IMG_ID OR (P.IMG_REAR_ID = I.IMG_ID AND :P_CHECK_REAR_IMAGE = 1))
  AND b.BAT_ID = isobat.bat_id
  ORDER BY cd_vol_nb ;


Plan
SELECT STATEMENT Optimizer Mode=CHOOSE		204 K	 	283874  	 	      	             	 
  SORT UNIQUE		204 K	8 M	283839  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      CONCATENATION		  	 	 	 	      	             	 
        NESTED LOOPS		67 K	2 M	69551  	 	      	             	 
          HASH JOIN		67 K	2 M	34093  	 	      	             	 
            HASH JOIN		70 K	1 M	16416  	 	      	             	 
              COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 	 	 	      	             	 
              NESTED LOOPS		17 M	293 M	15525  	 	      	             	 
                INDEX FAST FULL SCAN	R1APP.XAK_TXN_BATID_TXNID	17 M	195 M	8772  	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
            TABLE ACCESS FULL	R1APP.DOC	16 M	218 M	11175  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	R1APP.IMG	24 M	211 M	27540  	 	      	             	 
            INDEX UNIQUE SCAN	R1APP.XPK_IMG	1  	 	 	 	      	             	 
        HASH JOIN		67 K	2 M	69551  	 	      	             	 
          HASH JOIN		67 K	2 M	34093  	 	      	             	 
            HASH JOIN		70 K	1 M	16416  	 	      	             	 
              COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 	 	 	      	             	 
              NESTED LOOPS		17 M	293 M	15525  	 	      	             	 
                INDEX FAST FULL SCAN	R1APP.XAK_TXN_BATID_TXNID	17 M	195 M	8772  	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
            TABLE ACCESS FULL	R1APP.DOC	16 M	218 M	11175  	 	      	             	 
          TABLE ACCESS FULL	R1APP.IMG	24 M	211 M	27540  	 	      	             	 
      CONCATENATION		  	 	 	 	      	             	 
        FILTER		  	 	 	 	      	             	 
          HASH JOIN		70 K	3 M	88051  	 	      	             	 
            HASH JOIN		70 K	2 M	52587  	 	      	             	 
              HASH JOIN		70 K	1 M	16416  	 	      	             	 
                COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 	 	 	      	             	 
                NESTED LOOPS		17 M	293 M	15525  	 	      	             	 
                  INDEX FAST FULL SCAN	R1APP.XAK_TXN_BATID_TXNID	17 M	195 M	8772  	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
              TABLE ACCESS FULL	R1APP.PYMT	17 M	292 M	28364  	 	      	             	 
            TABLE ACCESS FULL	R1APP.IMG	24 M	211 M	27540  	 	      	             	 
        HASH JOIN		70 K	3 M	88051  	 	      	             	 
          HASH JOIN		70 K	2 M	52587  	 	      	             	 
            HASH JOIN		70 K	1 M	16416  	 	      	             	 
              COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 	 	 	      	             	 
              NESTED LOOPS		17 M	293 M	15525  	 	      	             	 
                INDEX FAST FULL SCAN	R1APP.XAK_TXN_BATID_TXNID	17 M	195 M	8772  	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
            TABLE ACCESS FULL	R1APP.PYMT	17 M	292 M	28364  	 	      	             	 
          TABLE ACCESS FULL	R1APP.IMG	24 M	211 M	27540  	 	      	             	 


Please let me know if some thing can be done,
Moreover give me an idea if i create a Bitmap index on
IMG.IMG_SHRT_TERM_IN will it avoid FTS on IMG table,
this column has only 2 values 1 or 0 in 40 million rows.


Thanks

[Updated on: Mon, 01 May 2006 14:44]

Report message to a moderator

Re: Tuning This Query [message #170064 is a reply to message #169883] Mon, 01 May 2006 22:49 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That Bitmap indexes improve performance on low-cardinality columns is a MYTH. They must be used in conjunction with other bitmap indexes on the same table, in a combination of ANDs, ORs, and NOTs to narrow the results down to at most 10% of the table before they are of any use at all.

On their own they are of almost no use unless the distribution is skewed (<1% of rows have a specific value).

rleishman wrote on Sun, 30 April 2006 11:25

Does the SQL need to read all of the data in the big tables or not? If not, this means that pkg_cd_extr.str2tbl('384246') is returning values for bat_id that will result in a join to a small proportion of the BAT table (hopefully <1%). Otherwise, you're stuffed.

rleishman also wrote on Sun, 30 April 2006 11:25

- Isolate the repeated portions of the UNION using WITH. Oracle may be able to save some IO.

rleishman went on to say on Sun, 30 April 2006 11:25

- Use the CARDINALITY hint to tell Oracle how many rows you expect to be returned from the table function. I have put 100, but you should use a more accurate estimate.

rleishman finished off with on Sun, 30 April 2006 11:25

If it continues to use HASH joins, try USE_NL or FIRST_ROWS hints to get it to use Nested Loops and indexes.


Honestly, did you try anything I suggested.

Ross Leishman
Re: Tuning This Query [message #170207 is a reply to message #170064] Tue, 02 May 2006 10:33 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hello Ross

Sorry for being late in implementing your ideas, Actually i was trying all steps and other things in line,
Any how honestly speaking ): I implemented 3 steps out of 4 you suggested and it is flying like any thing plan is excellent,
FYI i read from asktom also and implemented rownum from it
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446#15740265481549.


Here is the modified Query and Plan, working good.
 SELECT I.IMG_ID
  FROM IMG i, txn T, DOC D, BAT B, (select /*+ cardinality(10) */ * from table(cast(pkg_expiry.str2tbl('1,2188416') as id_typ)) where rownum >=0) isobat
  WHERE B.BAT_ID = T.BAT_ID
  AND D.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (D.IMG_FRNT_ID = I.IMG_ID OR D.IMG_REAR_ID = I.IMG_ID)
  AND b.BAT_ID = isobat.column_value
  UNION

  SELECT I.IMG_ID
  FROM IMG I, txn T, PYMT P, BAT B, (select /*+ cardinality(10) */ * from table(cast(pkg_expiry.str2tbl('1,2188416') as id_typ)) where rownum >=0) isobat
  WHERE B.BAT_ID = T.BAT_ID
  AND P.TXN_ID = T.TXN_ID
  AND I.IMG_SHRT_TERM_IN = 1
  AND (P.IMG_FRNT_ID = I.IMG_ID OR (P.IMG_REAR_ID = I.IMG_ID AND :P_CHECK_REAR_IMAGE = 1))
  AND b.BAT_ID = isobat.column_value
  

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		229  	 	1615  	 	      	             	 
  SORT UNIQUE		229  	12 K	1615  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      CONCATENATION		  	 	 	 	      	             	 
        NESTED LOOPS		71  	3 K	429  	 	      	             	 
          NESTED LOOPS		71  	3 K	287  	 	      	             	 
            NESTED LOOPS		82  	2 K	41  	 	      	             	 
              NESTED LOOPS		10  	190  	21  	 	      	             	 
                VIEW		10  	130  	11  	 	      	             	 
                  COUNT		  	 	 	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      COLLECTION ITERATOR PICKLER FETCH	.STR2TBL	  	 	 	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XAK_TXN_BATID_TXNID	8  	96  	2  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.DOC	1  	14  	3  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XIF_DOC_TXNID	2  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	R1APP.IMG	1  	9  	2  	 	      	             	 
            INDEX UNIQUE SCAN	R1APP.XPK_IMG	1  	 	1  	 	      	             	 
        NESTED LOOPS		71  	3 K	429  	 	      	             	 
          NESTED LOOPS		71  	3 K	287  	 	      	             	 
            NESTED LOOPS		82  	2 K	41  	 	      	             	 
              NESTED LOOPS		10  	190  	21  	 	      	             	 
                VIEW		10  	130  	11  	 	      	             	 
                  COUNT		  	 	 	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      COLLECTION ITERATOR PICKLER FETCH	.STR2TBL	  	 	 	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XAK_TXN_BATID_TXNID	8  	96  	2  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.DOC	1  	14  	3  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XIF_DOC_TXNID	2  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	R1APP.IMG	1  	9  	2  	 	      	             	 
            INDEX UNIQUE SCAN	R1APP.XPK_IMG	1  	 	1  	 	      	             	 
      CONCATENATION		  	 	 	 	      	             	 
        FILTER		  	 	 	 	      	             	 
          NESTED LOOPS		81  	4 K	451  	 	      	             	 
            NESTED LOOPS		82  	3 K	287  	 	      	             	 
              NESTED LOOPS		82  	2 K	41  	 	      	             	 
                NESTED LOOPS		10  	190  	21  	 	      	             	 
                  VIEW		10  	130  	11  	 	      	             	 
                    COUNT		  	 	 	 	      	             	 
                      FILTER		  	 	 	 	      	             	 
                        COLLECTION ITERATOR PICKLER FETCH	.STR2TBL	  	 	 	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
                INDEX RANGE SCAN	R1APP.XAK_TXN_BATID_TXNID	8  	96  	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	R1APP.PYMT	1  	18  	3  	 	      	             	 
                INDEX RANGE SCAN	R1APP.XAK_PYM_TXNID_PYMID	1  	 	2  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.IMG	1  	9  	2  	 	      	             	 
              INDEX UNIQUE SCAN	R1APP.XPK_IMG	1  	 	1  	 	      	             	 
        NESTED LOOPS		81  	4 K	451  	 	      	             	 
          NESTED LOOPS		82  	3 K	287  	 	      	             	 
            NESTED LOOPS		82  	2 K	41  	 	      	             	 
              NESTED LOOPS		10  	190  	21  	 	      	             	 
                VIEW		10  	130  	11  	 	      	             	 
                  COUNT		  	 	 	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      COLLECTION ITERATOR PICKLER FETCH	.STR2TBL	  	 	 	 	      	             	 
                INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	6  	1  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XAK_TXN_BATID_TXNID	8  	96  	2  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.PYMT	1  	18  	3  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XAK_PYM_TXNID_PYMID	1  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	R1APP.IMG	1  	9  	2  	 	      	             	 
            INDEX UNIQUE SCAN	R1APP.XPK_IMG	1  	 	1  	 	      	             	 


Thanks a Lot! Learned a new thing.

Take Care

[Updated on: Tue, 02 May 2006 10:53]

Report message to a moderator

Previous Topic: job not visible in dba_jobs
Next Topic: Joins in Queries
Goto Forum:
  


Current Time: Sun May 26 22:42:35 CDT 2024