Optimizing query - Page 2 - UNIX and Linux Applications

matrixmadhan · #8 (**permalink**) 08-03-2007

Quote:

Originally Posted by kahuna

I don't know if this is more efficient, but it seems like a positive approach might be better where there are only a few duplicates.

DELETE FROM tableA A1
WHERE column1 in (SELECT column1 FROM tableA GROUP BY column1 having count(*) > 1)
and rowid != (select min(rowid) from tableA A2 where A1.column1 = A2.column1)

Correct me if am wrong !

Am not able to understand the optimization that you have made to make the query run faster.

Basically, with combination of using selected columns ( column1 ) and the specific rowids ( rowid ), what is the need to specify an extra condition with a separate subquery to extract column1.

Isnt that redundant ? Or how does it make the query optimized and more efficient.

This is really equivalent to for each record with a rowid ' x ', necessarily two subqueries should be executed for all the records.

kahuna · #9 (**permalink**) 08-03-2007

Quote:

Originally Posted by matrixmadhan

Basically, with combination of using selected columns ( column1 ) and the specific rowids ( rowid ), what is the need to specify an extra condition with a separate subquery to extract column1.

Yes you are right that it makes 2 sub-selects. If you have many duplicates, then your original post may be better. But suppose you only have a single duplicate. My subquery1 returns only a single value of column1 (the duplicate), where your subquery returns many values (the non duplicates). So now I have a single value to compare against tableA where you have many values. Yes, I still have to make subquery2, but it is against a much smaller set.

Subquery2 makes sure that, for a given value of column1, we don't delete the row with the smallest rowid.

jim mcnamara · #10 (**permalink**) 08-04-2007

Look - the query has to search the ENTIRE output of the IN (SELECT ..)
for each set of rows it deletes. I know that rowid's are what you get from an index.

Instead of me going on about this - look in Tom Kyte's book 'EXpoert One on One' and look in the analytical function chapter - there is an example of how to optimize a query very like this. Or try the asktom oracle site:
From there:

Code:

from 
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1224636375004

delete from tableA a
     where rowid <> ( select max(rowid)
                        from tableA b 
                       where b.column1 = a.column1)
    /

kahuna · #11 (**permalink**) 08-04-2007

I know that it may not be possible, but it would be interesting to see a comparison timing of the different queries.

kahuna · #12 (**permalink**) 08-06-2007

I made a test table with a single varchar field and no index. I loaded it with 30,000 records and an additional 30 duplicate records. I ran the following queries.

Code:

select count(*) FROM tableA
WHERE rowid not in
(SELECT MIN(rowid) FROM tableA GROUP BY column1);

1 hour 12 minutes 58 seconds

Code:

select count(*) FROM tableA A1
WHERE column1 in (SELECT column1 FROM tableA GROUP BY column1 having count(*)
> 1)
and rowid != (select min(rowid) from tableA A2 where A1.column1 = A2.column1);

1 second

Code:

select count(*) from tableA a
     where rowid <> ( select max(rowid)
                        from tableA b
                       where b.column1 = a.column1);

6 min 29 sec

Your mileage may vary.

kahuna · #13 (**permalink**) 08-16-2007

Matrixmadhan,

Have you decided if anything works better than your original query? If so, can you let us know what worked and some idea of the time difference? Thanks.

matrixmadhan · #14 (**permalink**) 08-16-2007

Thanks for the follow up.

I had one more option of doing that.

Just tried with few records and that seems to be better.
Actually I didnt compare with bulk number of records.

Will do that and post the results positively by end of tomorrow !

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Tips for Optimizing Rails on Oracle	iBot	Oracle Updates (RSS)	0	04-06-2008 02:10 AM
Optimizing for a Speed-up	switch	Shell Programming and Scripting	3	04-07-2006 05:52 PM
optimizing disk performance	J.P	Filesystems, Disks and Memory	4	03-01-2005 08:32 AM
Optimizing Apache Server Performance	Neo	News, Links, Events and Announcements	0	02-14-2005 06:30 PM
Optimizing the system reliability	Deepa	Filesystems, Disks and Memory	2	12-11-2002 07:09 PM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode