Problem with indexing/ slow access to Oracle

I'm sure that I am doing something wrong when writing indexes and creating tables.

I am running Oracle 8.0.4.0.0 on Solaris 2.6, on a Sun with 512MB memory and a fast 27GB Raid drive.

When I perform a simple query, such as 'select * from customer where customer_number = 12345', results are returned very quickly.

Likewise, if I perform a query on a second table in a similar fashion, this is also very fast.

The problem and slow access arises when I am querying multiple tables. Ie, if I run :

select * from customer, agreement where customer.agreement_id = agreement.agreement_id and customer.customer_num = 12345;

• This takes about 10 seconds to bring back the data which is unfortunately unacceptable for what is a real time system.

I only have about 30,000 customer and 150,000 agreement records, so surely it should be quicker than this?

I am confused as to what I should set as a primary key when creating tables, and what fields/criteria I should use when creating indexes. Can you index 'too many' fields? Is it better to have something as a primary key or an index, as you can't do both - or is a primary key automatically indexed upon table creation?

Please cc replies to russ_at_u-net.net if possible.

Thanks,
Russ.

--

Russell Fray
U-NET Ltd. Received on Tue Sep 15 1998 - 03:58:57 CDT