Problem solve Get help with specific problems with your technologies, process and projects.

Improve performance of SQL query

How can I improve the performance for this SQL?

How can I improve the performance for this SQL? The value of a.a, b.from, b.to is a number in varchar type.
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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.