Home » RDBMS Server » Performance Tuning » Tuning This Query
| Tuning This Query [message #169809] |
Fri, 28 April 2006 15:38  |
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  |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
|
|
|
Goto Forum:
Current Time: Fri Apr 26 10:57:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
