To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
You don't mention what indexes you have on these tables nor what access
path Oracle is currently taking. First of all, do an explain plan on
this SQL, if you haven't already, to see what access plan is using to
access the table. This information will tell what if Oracle is using any
indexes or doing full table scans.
The first thing I would try is instead of using an IN, I would use an
EXISTS, as such:
Update a
set a.a = 'exist'
where EXISTS ( select 1 from a, b where a.a >=b.from and a.a <= to)
Using an IN will probably cause Oracle to bypass using an index, if one
exists, and cause the Optimizer to use a table scan to access table a.
Another option, if these tables are large, I would consider indexing
column 'from' on table b if it is not indexed already. You might also
want to consider indexing column 'a' on table a, if that table is not
updated very frequently. The index on column a in this example, will
help on the subquery, but it will hurt you when doing the update since
the index on column a will be updated causing extra I/O. As a result,
the index may actually hurt performance but it will depend on the number
of rows that are updated, and the current size of the table.
|
