Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle/SQL Question

Re: Oracle/SQL Question

From: Kevin Bass <akil1_at_mindspring.com>
Date: Tue, 14 Apr 1998 20:44:38 -0400
Message-ID: <6h0vvv$qnj$1@camel12.mindspring.com>

Using the EXPLAIN PLAN is good but you should get into the habit of optimizing your sql statements without using the EXPLAIN PLAN tool.

When constructing a sql statement there are certain rules that can be used to optimize the sql statements before even considering user the EXPLAIN PLAN or other tools. A sql statement should follow a standard format of :

SELECT field1, field2, . . .

   FROM <big table>,

                <driving table>,
                <small table>
 WHERE <driving table.field> = <small table.field>
                 <driving table.field> = <big table.field>
                  .  . .  (other statements)

OR

SELECT field1, field2, . . .

   FROM <driving table>,

                 <small table>

 WHERE <driving table.field> = <small table.field>

Sometimes it is helpful to use a HINT in your sql statements to assist the database in determining the index. An example would be:

SELECT  /*+ INDEX ( <index name> ) */     <=== HINT
                  field1, field2, . . .
   FROM  <big table>,
                <driving table>,
                <small table>
 WHERE <driving table.field> = <small table.field>
                 <driving table.field> = <big table.field>
                  .  . .  (other statements)


There are other HINTs that can be used for optimization too. The methods above are just a sample of optimizing sql statements. There are others that can be used too.

It is good to get into the habit of optimizing sql statement yourself without using EXPLAIN PLAN or other tools. Optimizing your sql statements should be done whenever you create an sql statement. If you get into the habit of optimizing while you are creating the sql statements, you will get better and better at constructing them. Only use those tools when all else fails!!

Kevin

>In article <lorrie-ya02408000R1304981946460001_at_enews.newsguy.com>,
> lorrie_at_macconnect.com (Lorrie) wrote:
>>
>> I am an Oracle developer. When I am developing SQL statements and tuning
>> them, I need to run them over and over again, to see the performance
effect
>> of changes to the statement, and optimization mode. I also need to see
how
>> long it takes each statement to run. Also, sometimes when I drastically
>> change
>> the statement, and it uses the wrong index, the statement goes on for
ever.
>> Then I have to kill the application, and then ask my DBA to kill the
orphan
>> client session. Are there any tools out there that could help me with any
>> of these problems/requirements?
>>
>> TIA,
>> Lorrie
>>
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Apr 14 1998 - 19:44:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US