Make sure both tables have indexes on agreement_id or at least indexes where
agreement_id is the first column specified in a concatenated index.
f025033_at_my-dejanews.com wrote in message <6uvk0q$1n$1_at_nnrp1.dejanews.com>...
>In article <35fe2b6d.43294628_at_news.u-net.com>,
> russ_at_u-net.net (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.
>>
>Hi Russel,
>
>first of all: yes a primary key is automatically indexed with a unique
index
>on the primary key column(s). Of course you should create primary key
>constraints and foreign key constraints on your tables because this
prevents
>you from getting invalid or orphaned data. In Oracle 8 there are two
>different optimizer schemes. The "old" rule based optimizer and the cost
baes
>optimizer. in order to use the better cost based optimizer you should
analyze
>your tables regularly using analyze table xyz compute statistics or begin
>dbms_utility.analyze_schema('schema_name','COMPUTE'); end; This will give
the
>optimizer all information regarding to the data, the indices, et al. The
>optimizer can now use the best (lowest cost) was to get your data. You can
>get execution plans from your sql statements if you user explain plan.
There
>you can see what optimizer goal is used and which ways the optimizer has
used
>to get your data.
>
>Next problem could be a sga that is too small. Have you checked the cache
hit
>ratio of your db buffers and the shared pool? If this goes below 95 % you
>should adjust your sga to use more memory. Each miss will get data from
disk
>but from memory and this will slow down massively your selects.
>
>Hope this helps
>Andy
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Oct 01 1998 - 12:26:14 CDT
