Re: Problem with JOIN

Bharat Kumar wrote:
>
> I'm experiencing performance problems with a join statement, and was
> wondering if I'm doing something wrong. I have the following tables:
>
> create table customer (
> id number,
> area_code number
> );
>
> create table call_record (
> cust_id number,
> duration number
> );
>
> Each table contains 100000 distinct rows. There is a one-to-one correspondence
> between the cust_id in the call_record table and id in the customer table.
> I'm trying to do the following:
>
> create table calls as (
> select cust_id, area_code, duration
> from customer, call_record
> where id = cust_id
> );
>
> When there are NO indexes on the customer and call_record table, the time
> required to create the "calls" table is approx. 20 sec.
>
> If I create indexes on the customer and call_record tables as:
>
> create index index_customer on customer(id) tablespace index_space;
> create index index_call_record on call_record(cust_id) tablespace index_space;
>
> Now, the time reqd. to create the "calls" table INCREASES to approx. 50 sec.
>
> When I ran tkprof on the trace files, I found that the "query" field for
> the "create table calls..." statement increased by more than an order of
> magnitude when the indexes were present, as compared to the first case,
> and I can't figure out why.
>
> Any suggestions?
>
> Thanks,
> -Bharat
>
> Disclaimer: My opinions are mine alone, and no one else can have them.

Looks like you didn't set unique indexes for the tables....if all ids are in fact unique, 'create unique index...' should definitely make a difference. Good luck! Received on Thu Jul 04 1996 - 00:00:00 CEST