Home » SQL & PL/SQL » SQL & PL/SQL » deleting with a join (Oracle Database 10g)
deleting with a join [message #385083] Fri, 06 February 2009 06:35 Go to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi there,

I was about to execute the following command:

delete from tableA where UniqueID not in (select UniqueID from tableB);

so basically removing all rows from tableA which don't match with an entry in table B. It strikes me that this is not a very good way to execute this command and that using some sort of join would be better. However, I'm getting a bit confused as to the syntax I need to do that. Can anyone help?

Thanks,

F
Re: deleting with a join [message #385088 is a reply to message #385083] Fri, 06 February 2009 06:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

@frog9697,

I may have not understood your requirement properly. Are you looking for EXISTS Condition.

Regards,
Jo
Re: deleting with a join [message #385089 is a reply to message #385083] Fri, 06 February 2009 06:42 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Yes as suggested by joicejohn

DELETE FROM tablea a
      WHERE NOT EXISTS (SELECT NULL
                          FROM tableb b
                         WHERE a.uniqueid = b.uniqueid)


Thanks
Trivendra
Re: deleting with a join [message #385091 is a reply to message #385083] Fri, 06 February 2009 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delete tableA 
where uniqueid in 
  (select uniqueid from tableB 
   minus 
   select uniqueid from tableA)
/

Regards
Michel
Re: deleting with a join [message #385093 is a reply to message #385083] Fri, 06 February 2009 06:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not a bad way of doing it if the UniqueId column is indexed.

There are two other ways of doing it:

The first (assuming you're on 10g) is to use the DELETE option in the Update clause of a MERGE statement. This could well be faster than the standard delete for large tables.

The second way would be to delete from a view.
Unless you've got your referential integrity constraints set up correctly, you'll have to use the BYPASS_UJVC hint here. If you were using this in an update, you could get into trouble if your view returned the same rows more than once, but with a delete, it's not nearly so important.
Even so, you shouldn't use method 2 until you've read up on and understood what the hint does.

create table test_90 (col_1  number, col_2 number);

create table test_91 (col_1  number);

insert into test_90 values (1,10);
insert into test_90 values (2,10);
insert into test_90 values (3,10);

insert into test_91 values (1);

commit;
        
merge into test_90  t90
using (select col_1 from test_91) t91
on    (t90.col_1 = t91.col_1)
when matched then update set col_2 = null delete where col_1 is not null;

select * from test_90;

rollback;

delete /*+ bypass_ujvc */
       (select t90.*
        from   test_90 t90
              ,test_91 t91
        where  t90.col_1 =t91.col_1);

select * from test_90;
Re: deleting with a join [message #385094 is a reply to message #385093] Fri, 06 February 2009 07:05 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Thanks very much everyone, that gives me a good few ideas to work with.

Cheers!
Re: deleting with a join [message #385100 is a reply to message #385093] Fri, 06 February 2009 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER use this hint.
Read http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197999096334#17899534514537

And Tom says:
Quote:
see, that is the problem with undocumented hints. we cannot really say *what* its behaviour is.
it is actually non-deterministic:...

Regards
Michel
Re: deleting with a join [message #385114 is a reply to message #385083] Fri, 06 February 2009 08:02 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi,

I had a quick go at executing both Trivendra and Michel' ssuggestions. It appears that both will take a very long time, i.e. several days! (despite a unique index on Uniqueid present on both tables). Both tables are very large (tableA is 180m rows and tableB 44m rows. Is there any way I can use a more efficient query.
Re: deleting with a join [message #385117 is a reply to message #385114] Fri, 06 February 2009 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without information about your environment and requested in OraFAQ Forum Guide, Performance Tuning section and Performances Tuning sticky, including execution plan, noone is able to accurately help you.

Regards
Michel
Re: deleting with a join [message #385119 is a reply to message #385100] Fri, 06 February 2009 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
NEVER use this hint


I have to disagree. This hint is used in Oracle published material: Example

Tom Kyte talks about how to use it here

Now, he also says it's on his list of 'Evil, do not use' hints, but he also says
Quote:
be careful, as long as the join columns in the source table are unique (they are, the group by does
that), this is "sort of safe", you might find the hint disappears in the future (i'd say unlikely)


It exists in Metalink (See Bug 5407528)

I wouldn't use it in production code, but in a non-production environment, I would and have used it several times.
Re: deleting with a join [message #385122 is a reply to message #385119] Fri, 06 February 2009 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to disagree. This hint is used in Oracle published material: Example

A white paper can give information but is not an argument for support. Oracle does not "support" what is in white papers (I mean if you break your database because you use something in a white paper, Oracle is not responsible for this).

Quote:
Tom Kyte talks about how to use it here

This is the same topic I mentionned and quoted.

Quote:
as long as the join columns in the source table are unique

Yes this means that if you can create a unique constraint and don't use the hint then you can use the hint. This is why it is useful in DSS where you know your data and don't update/insert them (apart during loading period when no end user queries the database). So only real expert in application data can use it.

Sorry can't access your bug number, it says no hit for it.

Regards
Michel
Re: deleting with a join [message #385127 is a reply to message #385122] Fri, 06 February 2009 09:01 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member

To be honest, JRowbottom's suggestion is a little too complicated for me, as I'm a complete beginner! I have attached the explain plan according to TOAD.
Also, some parameters from my db that may be useful:
Hash_area_size 131072
Sort_area_size 65536
Pga_aggregate_target 808M

I'm not sure if increasing any of these may help performance or whether it is better to tune the query itself?

Thanks again for all your advice, sorry if I didn't provide enough information in my last post.

F
  • Attachment: plan.txt
    (Size: 0.53KB, Downloaded 109 times)
Re: deleting with a join [message #385132 is a reply to message #385127] Fri, 06 February 2009 09:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Operation	Object Name				Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
	
DELETE STATEMENT Optimizer Mode=ALL_ROWS		150 M	 	608284  	 	      	             	 
  DELETE	schema.tableA	  	 	 	 	      	             	 
    MERGE JOIN ANTI					150 M	2G	608284  	 	      	             	 
      INDEX FULL SCAN	schema.uniqueid_indexA		194 M	2G	431349  	 	      	             	 
      SORT UNIQUE					44 M	293 M	176935  	 	      	             	 
        INDEX FAST FULL SCAN	schema.uniqueid_indexB	44 M	293 M	23839  	 	      	             	 

Well according to the optimiser it estimates it is going to delete 150M rows from the 194M row table which is nearly 75% of the data is getting deleted. Is that estimate correct. If not could you please execute the following query along with the results and post it over here.
select count(*) from table_a a, table_b b
where a.unique_id = b.unique_id (+)
and
b.unique_id is null;

Regards

Raj

[Updated on: Fri, 06 February 2009 09:24]

Report message to a moderator

Re: deleting with a join [message #385135 is a reply to message #385132] Fri, 06 February 2009 09:31 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi Raj,

Yes, that is correct. I expect that tableA will have 44M rows - thus making it the same size as table B - when the statement has completed.

Regards,

F
Re: deleting with a join [message #385138 is a reply to message #385135] Fri, 06 February 2009 09:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would almost certainly be quicker to do this:

1) Create a new table holding the data that you want to keep:
CREATE TABLE tablea_temp as select * from tablea where uniqueid in (select uniqueid from tableb);


2) Truncate tableA, (possibly after disabling any FK constraints that point to it

3) Replace the data in tablea:
INSERT /*+ append */ into tablea select * from tablea_temp;


4) Drop tablea_temp and re-enable any dropped constraints.
Re: deleting with a join [message #385142 is a reply to message #385083] Fri, 06 February 2009 10:09 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Thanks, that seems so much more sensible than my original idea Razz I'll give it a go!
Re: deleting with a join [message #385144 is a reply to message #385135] Fri, 06 February 2009 10:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you have license for partitioning I will be tempted to use the exchange partition approach.

Steps will be similar what @JRowbottom as described

Step a) Create a temp partitioned table with single partition and insert the rows you are interested.
Step b) Truncate the source table
Step c) Exchange partition between the temp table and the main table.

Advantage you have is oracle don't need to re-insert 44Million two times. For more information about partition exchange refer the oracle reference manual.

Again as I mentioned earlier check whether you have the license for partitioning.

Hope this helps.

Regards

Raj
Re: deleting with a join [message #385145 is a reply to message #385083] Fri, 06 February 2009 10:13 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
What happens if/when another user does DML against this table after the "reorg" process has been started?
Previous Topic: LEFT Outer Join problem (merged 4)
Next Topic: How to get these strings each in a line
Goto Forum:
  


Current Time: Tue Dec 06 10:11:35 CST 2016

Total time taken to generate the page: 0.14794 seconds