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
Received on Fri Jun 05 1998 - 00:00:00 CDT
