Problem with JOIN

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. Received on Wed Jul 03 1996 - 00:00:00 CEST