Re: DB design / performance

Super Grover schrieb in Nachricht
<1da5k5e.1ufp8k81vafqfoN_at_dc2-modem1546.dial.xs4all.nl>...
>Hi,
>
>I have a few questions for all Oracle- and DBA-heroes out there. :-)
>It concerns the basic design of my database and the possible performance
>gains when I change it.
>
>Basically I have two tables. The fist table contains fixed data with
>60.000 records (primary key = 'CODE'). The second one contains variable
>data, that changes each month (primary key = 'CODE' & 'MONTHCODE') with
>now 15*60.000 records.
>
>- Question 1
>In the variable data table, I have 10 fields for each record. Of those
>10, there are 4 that I use very much in queries and 6 not too often.
>Would performance increase much if I split this table in two? I guess
>the underlying question is: how are the records read. Does it take much
>time skipping the un-used fields?
>
>- Question 2a
>In the fixed data table, there are a few fields I use to select certain
>classes of records in queries on the variable table. For example a field
>'CLASS' that contains 'HIGH'(10%), 'MED'(50%) or 'LOW'(40%), which I
>basically use in 80% of my queries. So I use an equijoin on the 'CODE'
>field of the two tables. Does it make sense to add this field to the
>variable table so that I don't have to use the join, although it
>duplicates information (the same value for each month)?
>
>- Question 2b
>Or maybe I should split the variable table into three separate tables
>(one for each 'CLASS') and create a View with a UNION statement to use
>when I want to run queries on all data?
>Is it faster than using three Views of one table? Underlying question:
>how much time does it take skipping the undesired records (certainly in
>the case selecting 'HIGH'-records)?
>
>- Question 3
>Now, if I choose to split the variable table into three separate ones,
>and I use equijoins in queries to select records on other criteria than
>'CLASS', for example 'CURRENCY', does it then make sense to split the
>fixed table into three as well? In that case I have a set of fixed and
>variable data for HIGH, MED, and LOW.
>
>I have several more questions, but I will save them for later. Thanks to
>all who will give me some advice on the above questions.
>
>Gr.
>James

Hi,
First: Buy powerful Hardware. Second: always create an index when using large tables. Third: create the index also over a view and make shure the index covers all fields which join the two tables. Older database versions want the indices over the regarding fields rather than over the view. I once created indices over each column of importance in all the big tables and that worked. It was a bookkeeping software.... Regards,
Steff Received on Fri Jun 05 1998 - 00:00:00 CDT