Prev: Aggregation, sort of
Next: Immediate Openning
From: Sashi on 28 Jan 2010 10:18
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

From: Robert Klemme on 28 Jan 2010 12:19
On 28.01.2010 16:18, Sashi wrote:
> 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<=

Are you sure this is not a bug? It seems you rather want "a.phone"
instead of "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

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?

> indexes on B for start_range and
> end_range but it's still taking pretty long.

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.

> Any other pointers?

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/
 | 
Pages: 1
Prev: Aggregation, sort of
Next: Immediate Openning