Re: Optimizing Query

Colin,

A few pointers. You could look here. But there is no guarentee the solution lies here :-)

• 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 problem could also be in the update query. Post the update query and see the indexes it uses. Also check the PCTFREE value is not very low. Increase the PCTFREE and try. It matters while updating (Please refer the manual for this , as i am not very sure about this ). Note if the PCTFREE for your table which is being updated is 0 , set it to atleast 10.

• Place the most restrictive AND first and try. I read this in Celkos books "SQl for smarties". If the optimizer mode is choose, then it could help. Rearrange ((c.processed = 0 > and o.ordertype = 1 )) to ((o.ordertype = 1 and c.processed = 0 ))
• Another important pointer. Check if you have too many indexes in the base tables. They could be slowing your updates if the indexed columns are being updated. If you find that the update is causing the problem, see whether you could drop some unused indexes. See whether dropping the bitmapped index helps, assuming you arent using it anywhere
• Some other things-like network speed etc. could also be the problem. Read the chapter on Oracle Tuning in the docs. Since yours seems to be a simple query, the chances are bright that some other problem could be there. Also the Rollback segment may need to be tuned
• Also, you say you are updating some rows. See whether bind variables are being used in that update. Bind variable SQL statements are cached. Post your update statement.

Instead of

UPDATE x set m =12
and
UPDATE x set m =12

Try
UPDATE x set m = :1. Before the statement executes, set the value of the bind

• If nothing works, Rearrange the columns in the From Clause and give a shot.

As i said, your query seems to be simple. The chances are high that the optimizer has not goofed up :-)

Regards
Mark

colinwinning_at_yahoo.com (Colin) wrote in message news:<8181cd1e.0110220401.21740d7a_at_posting.google.com>...
> Apologies for the fairly long post,
>
> I am currently trying to optimize a query which is proving quite difficult. I
> have two tables clients and orders. The clients table has around 15M rows and
> the orders table around 25M rows.
>
> I am trying to do a join between these two tables as follows :
>
> 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
>
> I have tried many combinations of indexes on fields on each of the two tables.
> The optimizer is currently using the primary key on clients (id) and a bitmap
> index on orders (ordertype) - as seen by the explain plan.
> There is also an index on the orders (client_id) field.
>
> Forgot to mention, as I process each record, the processed field is updated to
> another value. Also, the optimizer mode is set to choose. Oracle version 8.1.5.
>
> I have had different response times coming back from the server depending on
> when the tables and indexes were last analyzed. These are not always better
> when the statistics have been computed.
>
> Can anyone suggest where I might be going wrong and if there is a better
> combination of indexes that should be used.
>
> TIA
> Colin
Received on Tue Oct 23 2001 - 03:58:29 CDT