?
Solved

"WHERE ROWID > ANY (" is too slow

Posted on 2015-01-27
10
Medium Priority
?
167 Views
Last Modified: 2015-01-28
Greetings, esteemed experts!

EE question:

In Oracle 11g, I have a table with numerous columns, but the user has requested to only keep rows that contain the “first” instance of a particular value within a specific column.

So, with this data originally:

myPK  ...  myColor
1          ...  red
2          ...  red
3          ...  blue
4          ...  blue
5          ...  red
6          ...  green

Open in new window


He’d like to keep only the rows with the FIRST instance of a particular color:

myPK  ...  myColor
1          ...  red
3          ...  blue
6          ...  green

Open in new window


Initially, the developer wrote something like this (which appears to work):

delete from MyTable a
 where a.rowid > any 
  (select b.rowid
     from MyTable b
    where a.myColor = b.myColor)

Open in new window


But, since the table now has over 66 million rows, it’s now taking much too long. Outside of ensuring that there’s an index on myColor, what would you suggest to speed this up? Is there a better way to write this?

Thanks in advance!
DaveSlash
0
Comment
Question by:Dave Ford
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40574131
Try this one:
delete from 
  (select *, ROW_NUMBER() OVER(PARTITION BY myColor ORDER BY myPK) as rn
     from MyTable) a
 where a.rn > 1 

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40574210
I'm not sure the above post is Oracle syntax but I'll try it later.

>>He’d like to keep only the rows with the FIRST instance of a particular color:

First instance based on the primary key?

If so, is that primary key like the example and is a simple number column or is it more complex?
0
 
LVL 18

Author Comment

by:Dave Ford
ID: 40574220
chaau2015: Thanks for your response. I'll try that. I wasn't aware you can delete from a sub-query. (I've been a DB2-guy for a couple decades, and I'm just now forced to get more familiar with Oracle.)

slightwv:

> First instance based on the primary key?

Yes

> is that primary key like the example and is a simple number column or is it more complex?

It's a simple number ... a surrogate key.

Thanks for your help! I appreciate it.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40574257
The first post is incorrect syntax.  I confirmed it.

Sorry for my mistake.  The PK doesn't appear to matter.  I read it too quick.

>>Outside of ensuring that there’s an index on myColor,

Is there an index on mycolor?

Here are two alternatives that should delete the same rows but without an index, they are full table scans.  Not sure if either is 'better'.

delete from mytable where mypk not in (
	select min(mypk) from mytable group by mycolor
)
/

delete from mytable a
 where a.rowid > 
  (select min(b.rowid)
     from mytable b
    where a.myColor = b.myColor)
/

Open in new window


Out of the 66 million rows, how many will you be deleting?

It might be faster to create a new table with the remaining rows then dropping  the original with a quick rename.  This gets complex if there are foreign keys and/or constraints (more if the constraints are cascading) involved.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40574297
Another possibility based on the first post.

From my test it appears it might be slightly better but I don't have 66 million rows in my test...

delete from mytable where mypk in (
select mypk from (
		select mypk, row_number() over(partition by mycolor order by mypk) rn from mytable
)
where rn>1
)
/

Open in new window

0
 
LVL 18

Author Comment

by:Dave Ford
ID: 40574303
Thanks slightwv! When you say, "The first post is incorrect syntax", are you referring to mine or chaau's?

> Is there an index on mycolor?

Yes.

> Here are two alternatives ...

Thanks! I'll try those out.

> Out of the 66 million rows, how many will you be deleting?

I'm not sure. I'll check and get back to you.

> It might be faster to ...

Good point. I'll try that, too. Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40574313
>>re you referring to mine or chaau's?

Chaau's.  You cannot delete directly from a subquery in Oracle (that I know about).

>> Is there an index on mycolor?... yes...

That changes things.  Let me retest and I'll update my posts after creating the index.

Is if a simple index on the single column or a compound index on more than one column?

The closer the provided information to the exact environment, the closer our models.

>>Good point. I'll try that, too. Thanks!

Try my queries I've posted.

You don't need to actually execute them to get a good 'guess' on performance.

You can generate an execution plan w/o actually executing anything. It is a great starting point.

explain plan for
delete ...
;
SELECT * FROM TABLE(dbms_xplan.display);

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40574326
Even with an index on mycolor (and tried compound index variations) based on my simple test, the last one I posted in http:#a40574297 seems to work the best.

Your mileage may vary.
0
 
LVL 18

Author Closing Comment

by:Dave Ford
ID: 40576328
You nailed it! Thanks, slightwv!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40576332
Glad to help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question