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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Urgent help need for SQL statement tuning

Re: Urgent help need for SQL statement tuning

From: Rohrbacher, Ing. Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Thu, 18 Jun 1998 21:31:05 +0200
Message-ID: <35896AF9.90C9E7D9@sbox.tu-graz.ac.at>


Hi,

johnj_at_dbbasics.com wrote:

> I have the following problem: I have a Customer table and an Order table, both with
> customer_id to join on. I write the following statement.
>
> select a.customer_id
> from customer a, order b
> where a.customer_id=b.customer_id and
> b.rec_date between '01-JUN-98' and '31-JUN-98' and
> a.Country_cd = 1 and
> a.state_cd in ('NC','SC','VA','MD','FL')
>
> when the where clause contains references to Customer and Order it takes for ever
> to run. If the where clause has only Customer or only Order references, it comes
> back immediately. Both tables have about 250,000 records. I am missing something.
> respond to johnj_at_dbbasics.com or news.
>

Its hard to say anything if one don't know anything about datatype of columns and indexes defined upon.

Anyway check the following :

  1. guess ) ID's is of different datatype in customer and order table. So Oracle has to ( implicitly ) convert values. For instance if id is number in customer table and char in order table Oracle treats this like select ... form customer a , order b where to_char( a.id ) = b.id Given 250 000 records this will take very, very long my friend !
  2. guess ) No Index on ID in either of this table. OR more tricky to find : A index of a combinations of columns where costumer_id is NOT the first column ! For instance if costumer_id is part of a composite primary key which is defined something like this constraint order_pk is primary key ( order_date, costumer_id ) this will give and index where rowids a frist sorted by date and then by costumerid thus making it poor performing when only giving a costumer_id without a date. If this is so create an Index on costumer_Id alone
  3. not a guess : let Oracle explain you the execution path of the statement. There you will quickly find out which indices are used and which not.

Therefore you need a plan table like this SQL> describe plan_table

 Name                            Null?    Typ
 ------------------------------- -------- ----
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                  VARCHAR2(30)
 OBJECT_NODE                              VARCHAR2(30)
 OBJECT_OWNER                             VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(30)
 OBJECT_INSTANCE                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 SEARCH_COLUMNS                           NUMBER(38)
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 POSITION                                 NUMBER(38)
 OTHER                                    LONG

In sqlplus :
 explain plan set statement_id = 'xx' for  select a.customer_id
 from customer a, order b
 where a.customer_id=b.customer_id and

 b.rec_date between '01-JUN-98' and '31-JUN-98' and
 a.Country_cd = 1 and
 a.state_cd in ('NC','SC','VA','MD','FL') ;

 sqlplus answers EXPLAIN PLAN executed. -- if you have the plan table defined

Then you can see what execution plan oracle is using by querying the plan table :

select * from plan_table where statement_id = 'xx' connect by prior id = parent_id start with parent_id is null ;

HTH
Regards
  Robo

See docu how to


Received on Thu Jun 18 1998 - 14:31:05 CDT

Original text of this message

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