Re: Problem with JOIN

From: Ken Johnson <ken.johnson_at_mail.tapestry.com>
Date: 1996/07/04
Message-ID: <31DBEB53.AEF_at_mail.tapestry.com>#1/1

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.

Since you want to copy EVERY record in both tables, the most efficient method is to use a full table scan, not an index. If the index is used, first oracle finds the appropriate record using the index, then it finds the full record in the table. You can either remove your index, or, if you need the index for other operations, you can use a optimizer hint to have it use a full table scan:         

create table calls as

   select /*+ FULL(customer) */

	  /*+ FULL(call_record) */
	cust_id, area_code, duration

   from customer, call_record
   where id = cust_id;

You can look in the Oracle7 Server Application Developer's Guide for more details on hints.

-- 
-------------------------------------------------
Ken Johnson -  Senior Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com

Received on Thu Jul 04 1996 - 00:00:00 CEST