Home » SQL & PL/SQL » SQL & PL/SQL » Insert into confusion (Oracle Database 10g)
Insert into confusion [message #389543] Mon, 02 March 2009 10:43 Go to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi there,

I currently have two large tables, tableA = 138M rows and tableB = 113M rows; each with a unique index on uniqueid. I would like to insert into table B, rows from tableA which are not already in tableB, i.e.

insert into tableB(col list...)
select col list...
from tableA where uniqueid not in (select uniqueid from tableB);

This does not appear to be very efficient (the session browser in TOAD estimates it taking approximately 30 hours to complete). Possible alternatives I've come across are using the 'minus' command as follows

INSERT INTO tableB (col list...)
(SELECT col list...
FROM tableA
MINUS
SELECT col list...
FROM tableA
WHERE uniqueid IN (SELECT uniqueid FROM tableB));

but this is possibly even worse? I've attached the explain plans in a text file. I've done a bit of research and have come across conflicting suggestions for the best way to approach this problem. Would using a 'not exists' command perhaps be better? I'm not sure how I would implement this. Any advice/ideas appreciated! I should probably also say that table A contains a subset of the columns of tableB.

Yours hopefully,

F

  • Attachment: plans.txt
    (Size: 1.02KB, Downloaded 103 times)
Re: Insert into confusion [message #389544 is a reply to message #389543] Mon, 02 March 2009 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Would using a 'not exists' command perhaps be better?
The only way to know for sure is to actually try it.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Re: Insert into confusion [message #389552 is a reply to message #389543] Mon, 02 March 2009 11:09 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Apologies, hope this is clearer.
I currently have two large tables, tableA = 138M rows and tableB = 113M rows; each with a unique index on uniqueid. I would like to insert into table B, rows from tableA which are not already in tableB, i.e.


INSERT INTO tableB(col1, col2,...,coln )
SELECT col1, col2,...,coln
FROM tableA 
WHERE uniqueid NOT IN (SELECT uniqueid FROM tableB);



This does not appear to be very efficient (the session browser in TOAD estimates it taking approximately 30 hours to complete). Possible alternatives I've come across are using the 'minus' command as follows


INSERT INTO tableB (col1, col2,...,coln)
(SELECT col1, col2,...,coln
FROM tableA
MINUS
SELECT col1, col2,...,coln
FROM tableA
WHERE uniqueid IN (SELECT uniqueid FROM tableB));



but this is possibly even worse? I've attached the explain plans in a text file. I've done a bit of research and have come across conflicting suggestions for the best way to approach this problem. Would using a 'not exists' command perhaps be better? I'm not sure how I would implement this. Any advice/ideas appreciated! I should probably also say that table A contains a subset of the columns of tableB.
Re: Insert into confusion [message #389554 is a reply to message #389543] Mon, 02 March 2009 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
How many rows from TableA need to be INSERTed into TableB?

Why, oh why, do you have 2 table with exact same columns?
Please research Third Normal Form!
Re: Insert into confusion [message #389555 is a reply to message #389554] Mon, 02 March 2009 11:23 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
There should be around 24M rows from tableA that need to go into tableB.

"Why, oh why, do you have 2 table with exact same columns?"

Sorry i don't really understand your question exactly. Could you clarify and I'll seek to explain?
Re: Insert into confusion [message #389678 is a reply to message #389552] Tue, 03 March 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think you've quite got the right approach to using MINUS.

I'd have a look at one of these two:
INSERT INTO tableB (col1, col2,...,coln)
(SELECT col1, col2,...,coln
FROM tableA
MINUS
SELECT col1, col2,...,coln
FROM tableB);

or
INSERT INTO tableB (col1, col2,...,coln)
(SELECT col1, col2,...,coln
FROM tableA
WHERE uniqueid IN (
SELECT uniqueid 
FROM   tableA
MINUS
SELECT uniqueid 
FROM tableB));
Re: Insert into confusion [message #389687 is a reply to message #389678] Tue, 03 March 2009 03:37 Go to previous message
frog9697
Messages: 18
Registered: December 2008
Junior Member
That makes more sense, thank you. I'll give the first of those a try.
Previous Topic: Count Number of Null Column Values of each Record in a Table
Next Topic: Creating Synonyms (Merged)
Goto Forum:
  


Current Time: Thu Dec 08 04:13:18 CST 2016

Total time taken to generate the page: 0.06796 seconds