Re: Optimizing Query

From: Colin <colinwinning_at_yahoo.com>
Date: 24 Oct 2001 01:59:49 -0700
Message-ID: <8181cd1e.0110240059.7f504571@posting.google.com>

The SQL (not PLSQL) being run is

select /*+FIRST_ROWS*/ *
from clients c, orders o
where c.id = o.client_id

and c.processed = 0        --one of about 60 different values, 0 unprocessed 
and o.ordertype = 1        --either 1, 2, or null

and rownum <= 1000

and it is being run from a separate program. The update is also run from this program. The update is being done on the clients table - update processed flag when record has been processed.

The reason the query still takes the same amount of time now is that it is doing a fast full scan on an index on the orders table (client_id, ordertype) followed by a full table scan on clients.

I have thought about the idea of partitioning orders on ordertype (as suggested in a previous post) but would like to exhaust the indexing idea before partitioning any tables.

Cheers
Colin

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3bd57bf7$0$8514$ed9e5944_at_reading.news.pipex.net>...
> "Colin" <colinwinning_at_yahoo.com> wrote in message
> news:8181cd1e.0110230611.1f94abca_at_posting.google.com...
> > > ** First find out where the problem is. You say that you are updating
> > > some rows. Remove the update and just run the select and see how long
> > > it takes.
> > >
> >
> > The update does not directly affect the query. It still takes the same
> > length
> > of time.
>
> and
>
> > Indeed. The query does seem to be simple. The problem is (I think)
> > that the
> > updates are causing the statistics generated to become out of date
> > quickly.
>
> The first of statements implies that the second one is a blind alley. If the
> loop really does run with the same performance problems whilst not
> performing any updates then the issue of stats is surely moot since the
> table is not changing in any way.
>
> Also if the orders table is being updated then the bitmapped index on it is
> a very very bad idea indeed.
>
> I'm not sure oif I ever saw the pl/sql you are actually running.
Received on Wed Oct 24 2001 - 03:59:49 CDT