Correlated Update/Delete to use Indexes?
For the following query, Oracle uses the index for both tables.
Execution is very fast and I am very happy :-).
select count(*) from table1, table2
where table1.pkey = table2.pkey and
table2.non_key_column = 'some_value';
The only way I know to produce a comparable query to do deletes
or updates is to use a correlated subquery:
delete from table1 where exists
(select null from table2
where table2.pkey = table1.pkey and
table2.non_key_column = 'some_value');
This requires a full table scan on table1 and an index scan of
table 2. If table1 is large, this query is slow and I am not
happy :-(.
It just seems like there must be a way to do this in Oracle that
will use the index on both tables.
Am I missing something really obvious or am I stuck with a bunch
Received on Wed May 13 1998 - 12:43:14 CDT
