update a set a.a = 'exist' where a.a in( select a.a from a, b where a.a >=b.from and a.a <= to)
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.
Dig Deeper on Oracle and SQL
Related Q&A from Phillip Bracken
A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML ... Continue Reading
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.