Re: MY DATABASE IS SO SLOW

Is there a business requirement that all these rows be in the table at once? Since you are selecting based on a single year, I suspect you have records for other years in there. Are these other records queried, or do they just sit there? If you're talking about a transactional database, then you may consider keeping the data not involved in transactions in other tables, so that you're querying a much smaller set. If you're talking about a data warehouse, then I would suggest investigating simple data warehousing practices in which the summaries you show below might be represented as discreet rows. If you are forced to stick with the data as it is, and you are forced to query as you demonstrate here, then the 'group by' against that many records is going to kill performance. Can the 'group by' be left out?

Dan Karleen
Oracle Systems Analyst
Peterson's

Michael Wagner wrote in message <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 Tue Dec 08 1998 - 12:09:20 CST