Hi all, this is my first foray into optimization and I'm at a
relatively beginner level.

The situation that I have is an exceedingly simple one.

I have a table with a list of phone numbers, table A.

I have another table with a list of ranges of phone numbers, table B.

If the number in A lies between the start_range and end_range in B,
then the number is marked.

For example, in A a number could be 2125551212.
A range in B could be 2125550000, 2125552000.
So this number would get marked.

So the query is

update A set A.marked = 'Y'
where exists
(select 1
from RANGES B
where a.phone >= b.START_RANGE and a.DEST_NO_011 <=
b.END_RANGE);

Table A has about 9 million rows and B has about 3000.

This query runs for hours and hours.

I created index on A for phone indexes on B for start_range and
end_range but it's still taking pretty long.

Any other pointers?

TIA,
Sashi

On 28.01.2010 16:18, Sashi wrote:
Are you sure this is not a bug? It seems you rather want "a.phone"
instead of "a.DEST_NO_011".

This is likely not used. I believe you won't get away without a full
table scan on A. Btw, are your statistics up to date?

You better create a multi column index on (B.START_RANGE, B.END_RANGE)
because otherwise lookups will be more expensive. Depending on how
often START_RANGE and END_RANGE repeat you might even be able to reduce
the index size by compressing one or two index columns.

Did you look at the execution plan? What did it look like? If you got
the proper permissions (role PLUSTRACE) you can simply do "set autotrace
on" in SQL Plus and get the plan after execution.

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/