Query optimization

From: Sashi <smalladi_at_gmail.com>
Date: Thu, 28 Jan 2010 07:18:01 -0800 (PST)
Message-ID: <c9f6b878-cbf9-441f-8487-b0c7518e4db0_at_r19g2000yqb.googlegroups.com>

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 Received on Thu Jan 28 2010 - 09:18:01 CST