Re: Problem with indexing/ slow access to Oracle

Hi,

You're mixing up full_table scans and indexed access. There's really alot to tell
about, so you better look into some manuals. Few things: - full table scan reads multiple blocks at the same time (can be quicker than an index)
- primary key implicitly gives you an index - primary keys should be on unique identified values for a row in a table (can be

   multiple fiels)
- maybe your join should contain more fiels, depends on the interpretation

• yes you can index too much (Import for inserts and updates)

Jeroen

R Fray wrote:

> 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 - 06:51:24 CDT