>
> Hi all:
> I have a strange problem.I have a query that is populating a particular
> table based on equi-joins formed on multiple tables. This query seems to
> take about 6-7 seconds.
> insert into TABLE_A
> select LOAN_NUMBER,
> col2,
> col3....
> from LOAN_TABLE,
> TABLE_B,
> TABLE_C
> where LOAN_TABLE.LOAN_NUMBER = TABLE_B.LOAN_NUMBER
> and ......;
> But when I make the following change (include NOT EXISTS), the query does
> not seem to return any rows even after 10 mins though TABLE_A is empty.!!
> All tables have been analyzed.
>
> INSERT INTO TABLE_A
> SELECT LOAN_NUMBER,
> col2,
> col3.....
> FROM LOAN_TABLE,
> TABLE_B,
> TABLE_C
> WHERE NOT EXISTS
> ( SELECT 'x'
> FROM TABLE_A
> WHERE LOAN_TABLE.LOAN_NUMBER = TABLE_A.LOAN_NUMBER )
> AND LOAN_TABLE.LOAN_NUMBER = TABLE_B.LOAN_NUMBER
> AND ......;
>
> Any help is appreciated.
> Thanks
> Shaheer Mecci.
------------------------------------------------------------------------
Actually, using NOT IN prevents Oracle from using an index if there is
one. NOT EXISTS (or EXISTS) is much more efficient.
Try aliasing your TABLE_A in the NOT EXISTS clause as in the following
example:
INSERT INTO TABLE_A
SELECT LOAN_NUMBER,
col2,
col3.....
FROM LOAN_TABLE,
TABLE_B,
TABLE_C
WHERE NOT EXISTS
( SELECT 'x'
FROM TABLE_A here_is_the_alias
WHERE LOAN_TABLE.LOAN_NUMBER = here_is_the_alias.LOAN_NUMBER )
AND LOAN_TABLE.LOAN_NUMBER = TABLE_B.LOAN_NUMBER
AND ......;
As for not responding in 10 minutes, even though TABLE_A is empty, you may
have been locking yourself out. Try looking for locks while you are
waiting. Regardless, the aliasing above should help.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 280+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page