Design question

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
   1. "create table table1(id number primary key, val varchar2(1))" and get tableN FKs to refer to table1.id
   2. "create table table1(val varchar2(1) primary key)" and get tableN FKs to refer to table1.val
   3. "create table table1(val varchar2(1) primary key) organization index" and get tableN FKs to refer to table1.val
   4. 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 Received on Sat Sep 23 2006 - 05:20:01 CDT