Re: DB design / performance

Super Grover wrote:
>
> 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.

 [...]
> 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.

In both of these cases, you might benefit from a covering index, ie an index that contains all of the columns in your query. Using such an index means that selection can take place entirely within the index, which is typically scanned faster than the entire table. For example, if your queries against FixedTable all use CLASS but no other columns, then an index on (CLASS, CODE) will allow those queries to run faster.

Note that there are drawbacks: With the way you've described the distribution on CLASS, any query that is not entirely covered by the index (eg, you also select using column COUNTRY) will execute much more slowly because Oracle will first select rows from the index, then access the actual data blocks. Appropriate use of hints will keep this from happening, as will putting the extra column(s) in the index.

When creating a covering index, you typically want to have the most selective columns first. Depending on the environment, you may need multiple indexes. And always remember that indexes slow down inserts and updates.

-kdg Received on Fri Jun 05 1998 - 00:00:00 CDT