Re: MY DATABASE IS SO SLOW

For anyone to give you a 'good' answer will require a little more data.

1. What is your optimizer mode? And if Choose, ALL_ROWS, or FIRST_ROWS, i.e. cost based how and when were the statistics last updated.
2. What are your indexes? I find "I have create a index on every query part. " to be pretty vague.
3. Please provide the explain plan

My guess is you are doing a full table scan of data1, then joining to data2, and then joining to names. You have two filter values on data1, so how distinctive is the 'year' and 'company' columns combination. If these columns identify a relatively small sample set then a composite index on them may be what you need for choosing your driving records, that is if the group by clause does not prevent our using it. It seems to be that 'group by' often results in full table scans unless all selected columns are in the index. Since you have only one column from data1 in the select list I think it can use an index on (year,company) but I am not sure. I seem to remember by using an index hint to get Oracle to use an index to solve a group by similar to this.

Anyhow you need to post items #2 and #3 to allow someone better than me at tuning SQL to help.

Michael Wagner <wagner_at_eurolab.at> wrote in article <01be21f8$d6c519c0$1401a8c0_at_test>...
> I have a database with approx 3.500.000 entrys with 110 byte/set and the
> database grow i this year up to 80.000.000 entrys. If i make a query on
the
> 3.500.000 entrys it needs 1 hour before i get the result.
>
> select data1.year,data2.fare,data2.value,data2.class,names.n_desc,
> count(data2.fare), sum(data2.numbers) from
> data1,data2,names
> where data1.key=data2.key
> and names.n_key=data2.class and class >'0' and data1.year='98' and
> data1.company='33'
> group by data1.year,data2.fare,data2.value,data2.class,names.n_desc;
>
> The database is on SCO 5.0.4 and ORACLE 7.3.4 I have create a index on
> every query part. The database is now 390 MB the database grows up to 8-9
> GB
> What can i do to make the query faster.
>
> Thanks Mike !
>
Received on Mon Dec 07 1998 - 11:06:44 CST