Home » RDBMS Server » Performance Tuning » Removing Duplicates Query (9.2.0.5 Sun O.S 5.7)
Removing Duplicates Query [message #284414] |
Thu, 29 November 2007 10:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Hi,
I have this select to select duplicates , this takes forever for a huge table (400 mill rows ) and does a FTS,
Is there a better way / Approach to do that.
select ref_item_id from ref_item
where ref_item.REF_TYPE_ID = 9100 and ref_item.rowid in (
select rid
from ( select rowid rid, row_number() over
(partition by ref_item.REF_TYPE_ID, ref_item.inv_id
order by rowid) rn from ref_item where ref_item.REF_TYPE_ID = 9100) where rn <> 1
I have modifed that little bit to include 1 more table but this also does same FTS and forever it runs
select ref_item.ref_item_id from ref_item,inv
where ref_item.REF_TYPE_ID = 9100 and ref_item.inv_id=inv.inv_id and ref_item.rowid in (
select rid
from ( select ref_item.rowid rid, row_number() over
(partition by ref_item.REF_TYPE_ID, ref_item.inv_id
order by ref_item.rowid) rn from ref_item,inv where ref_item.REF_TYPE_ID = 9100 and inv.inv_id=ref_item.inv_id)
where rn <> 1 )
Here is the Plan
Plan
SELECT STATEMENT CHOOSECost: 595,774 Bytes: 2,584 Cardinality: 76
12 NESTED LOOPS Cost: 595,774 Bytes: 2,584 Cardinality: 76 10 NESTED LOOPS Cost: 593,097 Bytes: 74,956 Cardinality: 2,677
8 VIEW SYS.VW_NSO_1 Cost: 405,305 Bytes: 1,311,485 Cardinality: 187,355
7 SORT UNIQUE Bytes: 3,747,100 Cardinality: 187,355
6 VIEW R1APP. Cost: 405,305 Bytes: 3,747,100 Cardinality: 187,355
5 WINDOW SORT Cost: 405,305 Bytes: 3,747,100 Cardinality: 187,355
4 HASH JOIN Cost: 404,530 Bytes: 3,747,100 Cardinality: 187,355
1 INDEX FAST FULL SCAN UNIQUE R1APP.XPK_INV Cost: 929 Bytes: 26,160,696 Cardinality: 4,360,116
3 PARTITION RANGE ALL Partition #: 9 Partitions accessed #1 - #109
2 TABLE ACCESS FULL R1APP.REF_ITEM Cost: 393,476 Bytes: 92,481,200 Cardinality: 6,605,800 Partition #: 9 Partitions accessed #1 - #109
9 TABLE ACCESS BY USER ROWID R1APP.REF_ITEM Cost: 1 Bytes: 21 Cardinality: 1 Partition #: 11
11 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_INV Cost: 1 Bytes: 6 Cardinality: 1
INV table in this second query has 4.2 mill rows.
Any other way to write this Query.
Thanks
|
|
|
|
|
Re: Removing Duplicates Query [message #284514 is a reply to message #284506] |
Thu, 29 November 2007 23:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
rajavu1 wrote on Fri, 30 November 2007 05:54 | Try somethink like ..
But I am not sure about perfomance improvement
|
The idea of answering a performance-related question is that you have at least a basic idea about why your change would improve performance. If you have such an idea, post it; explain why you think your version will perform better than the original.
Simply posting an alternative query, hoping it will perform better doesn't really serve much purpose.
|
|
|
Goto Forum:
Current Time: Mon Aug 26 12:50:44 CDT 2024
|