Simple Query takes forever to process.

[OasisChurch]

Hi all:

Been awhile since I posted here.
I am trying to execute a simple query, however, after 4 hours it still was not complete. Is there better way to write this or it it just the nature of what i'm trying to do?
Query:
INSERT INTO table1(field)
SELECT field FROM table2 WHERE field NOT IN(SELECT field FROM table1);

Specifics:
Tab 1 has 192359 rows
Tab 2 has 85404 rows.
source and target fields are indexed.

I am executing this locally on my Oracle 10g box. My box is a dual core and has 4G of RAM.

let me know if you need anymore specifics.
NOTE: hopefully I did not double-post. If so, sorry.

thanks!
--scotta

 

[Dagon]

Are all your table statistics up to date ? When you say both fields are indexed, do you mean that the joining fields of table1 and table2 are indexed (specifically that there is an index on table1.field) ?

Try this alternative form:

INSERT INTO table1(field)
SELECT field FROM table2  t2
WHERE NOT EXISTS
(SELECT 1 FROM table1 t1
where t1.field = t2.field)

Run explain plan/SQL*Trace to check if it is using the indexes you expect.

 

[taupirho]

Not quite sure if this answers you but consider

create table t1(field number)
/
Table created.

insert into t1
select l from
(
select level l from dual connect by level < 192359
)
/
192358 rows created.

create table t2(field number)
/
Table created.

insert into t2
select l from
(
select level l from dual connect by level < 300000
)
where l between 190000 and 190000+85404
/

85405 rows created.

So, no indexes or anything on these tables and approx number of records in each corrrsponding to your own data. You want to insert into t1 values from t2 that dont already exist on t1 - right?

So next I ran your query and interrupted it after about 20 seconds (clock time) when no response forthcoming.

Next ran this and it came back in approx 2 secs

1 insert into t1(field)
2 select field from t2
3 minus
4* select field from t1
SQL> /

83046 rows created.






In order to understand recursion, you must first understand recursion.

 

[Dagon]

I suspect OasisChurch's query may be a simplification of what he actually wants to achieve. I would be surprised if he really only wants to insert one column into table1. If he wants more than one, then the minus method probably won't work.

 

[OasisChurch]

Hi Guys:

Thanks for you answers. The query is what it is. Just trying to insert values from table1.field1 into table2.field2 where the values do not exist in table2.field2

The exact query is as follows:
INSERT INTO APPEAL(APIN)
SELECT RPIN FROM RMAST WHERE RPIN NOT IN(SELECT APIN FROM APPEAL);

The join fields have new indexes on them.

NOTE: I will probably execute it tonight and just let it run. I will also record the time it takes.

Thanks for you help!

--scotta

 

[rcurva]

Hi Oasis,

You have to re-write your query to make use of your index (RPIN) to something like what Dagon suggested.

Otherwise, your SQL will just do full table scans.

Robbie

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

Actually full table scan is not an evil, especially in such "comparable sizes" case. Then, "dual core and has 4G of RAM" means almost nothing as Oracle uses only that memory being allowed to use. Then indexes without statistics are also almost useless.

So look at the execution plan at least or even better trace your session: maybe it just waits for a lock and this is not a matter of query performance at all.




Regards, Dima

http://seminihin.narod.ru