Re: Improvement of the select sentence

Jose Ignacio wrote:
>
> Hi
> I have a ORACLE database in a linux server. I have a client aplication to
> access to it. I have a select sentence that I want to optimize. The sentence
> is like that: (schematically)
>
> Select Table1.field, Table2.field, Table3 field, ....
> From Table1, Table2, Table3
> Where Table1.fieldId = Table2.fieldId AND
> Table2.fieldId2 = Table3.fieldId2 (+) AND
> Table1.Otherfield = Value
> ORDERBY field.
>
> My question is if I have to sort the condicions in Where clause, or I have
> to to put index in some field in order to gain in execution speed.
>
> Thanks in advance.

Try creating a composite index on each table, using all of the columns in the query, in the order specified in the order by clause.

Then analyze each table, run an explain plan, and see if the optimizer uses the indexes. If it doesn't, add a hint and see if it now will.

Then do some timing checks and see if this is actually an improvement.

Then do some timing checks on all the other tasks in your system which touch those tables and make sure that something has not been adversely affected.

This is a rather simple-minded approach, but you didn't give much to go on.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

My other .sig contains the cure for cancer.