Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Urgent help need for SQL statement tuning
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 :
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