Re: Query optimization
Date: Thu, 28 Jan 2010 18:19:21 +0100
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".
> 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.
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Thu Jan 28 2010 - 11:19:21 CST