Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Design question

Re: Design question

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 23 Sep 2006 09:16:42 -0700
Message-ID: <1159028201.253956@bubbleator.drizzle.com>


Barry wrote:
> 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

Read your question twice. Still unclear as to what it is you have and wish to achieve. And always include version information.

For one thing, assuming the intent is relational, how can you have 20 tables requiring the same constraint? DDL might be helpful too.

-- 
Daniel Morgan
Puget Sound Oracle Users Group
Received on Sat Sep 23 2006 - 11:16:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US