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
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 #284506 is a reply to message #284414] Thu, 29 November 2007 22:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try somethink like ..

Select from (
select ref_item.ref_item_id ,
row_number() over
       (partition by  ref_item.REF_TYPE_ID, ref_item.inv_id
       order by rowid) rn
from ref_item,inv
where ref_item.REF_TYPE_ID = 9100  and  ref_item.inv_id=inv.inv_id )
where rn>1


But I am not sure about perfomance improvement

Thumbs Up
Rajuvan.
Re: Removing Duplicates Query [message #284513 is a reply to message #284414] Thu, 29 November 2007 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But I am not sure about perfomance improvement
Why are not sure about performance?
What did your benchmarks show about performance?
Re: Removing Duplicates Query [message #284514 is a reply to message #284506] Thu, 29 November 2007 23:07 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Problem with SQL
Next Topic: Poor Insert Performance after table truncation was interupted
Goto Forum:
  


Current Time: Sun Dec 11 04:24:35 CST 2016

Total time taken to generate the page: 0.13934 seconds