Design question

Giganews Newsgroups
Subject: Design question
Posted by:  Barry (bbulsara…@hotmail.com)
Date: 23 Sep 2006

Oracle 10.1 on Sun V210

All, I have a table (which I will call table1) that contains rows with
half a dozen unique values (eg. 'A', 'B', 'C'). There are not null FK
to these values from a number of other tables (collectively called
tablesN).

My question is:

1. In terms of being in a position to tune performance, would
    it be better to drop table1 and
    put CHECK x IN ('A','B','C') in all of tablesN.
2. If not, would table1 be better created as
    a) "create table table1(id number primary key, val varchar2(1))"
and
        get tableN FKs to refer to table1.id
    b) "create table table1(val varchar2(1) primary key)" and
        get tableN FKs to refer to table1.val
    c) "create table table1(val varchar2(1) primary key) organization
index" and
        get tableN FKs to refer to table1.val
    d) something else.

Queries are made all the time showing for example "WHERE
tableN.x=table1.x AND table1.x='A'". In other words, the design isn't
just to make sure that table1.x contains only allowed values, but to
query on the value of x too.

tablesN is about 20 tables. Typically these have between 30,000 and
400,000 rows with FKs to table1.x in them.

The objective of this exercise is look at the existing design and see
how it could be done better, to give the CBO a better chance of
producing a more efficient plan. At the moment there is no bottleneck -
the exercise is confirming scalability of the existing design and
explore options should we need to at a later stage.
Thanks
Barry

Replies