Proper Use of Indexes

[fotistso]

Hello,
I have two tables that are quite big. The first one has 1 million rows and the second around 2 million.
My problem is that when i update oracle ignores the index that i have created.
My query is like:

update table1 a
set
(a.col1 , a.col2)
=
(select b.col1, b.col2
from table2 b
where b.col3 = a.col3)
where exists (select * from table2 b where b.col3 = a.col3);

if i remove the where exists clause the index works fine but with the where exists clause oracle does a full scan on both tables.

Any idea why this happens and any solution??

Thanks,
Fotis

 

[SantaMufasa]

Fotis,

If you have indexes on table1.col3 and table2.col3, then I'm a bit puzzled. I don't see a reason whey your EXISTS clause would invoke a full-table scan if you have the WHERE clause you have and those two columns indexed.

One efficiency suggestion I have is replace the "*" with NULL in your EXISTS subquery.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fotistso]

Actually, i join on two columns (sorry i didnt mention that) and i have one index for both cols (the index is on col3,col4 on both tables). I havent created one index per column. Do you think i need one index per column instead of a combined one?
The query is:
update table1 a
set
(a.col1 , a.col2)
=
(select b.col1, b.col2
from table2 b
where b.col3 = a.col3
and b.col4 = a.col4)
where exists (select * from table2 b where b.col3 = a.col3 and b.col4 = a.col4);

Also what does the NULL does on the exists statement?

 

[SantaMufasa]

Also what does the NULL does on the exists statement?

In the EXISTS statement, it checks whether a ROW returned from the query; it does not care what COLUMN information returns from the query. This means that returning a ROW of NULL information produces the same logical result as a ROW of several columns of data. Therefore, returning NULL is much more efficient (on EXISTS) than returning all columns (i.e., "*").


Yes, please create a separate single-column index for the second column in your other index, just to see if that improves the performance from your full-table scan.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”