Ref. table performance in Oracle

Hello --

I have some general questions on schema design for performance in Oracle. Specifically, I'm looking for some guidelines on when to use reference tables with integer PK's (sequence-generated) with RI to child tables vs. storing the actual character values in the child tables.

Oracle documentation that I have read indicates that the use of 'codes' is an outdated practice. The justification is that: storage is inexpensive, and requiring data-entry personnel to remember codes leads to data-entry errors. The latter justification seems outdated to me, considering that the vast majority of front-end tools allow the 'code' or 'ID' to be invisible to the user through the use of combo or list boxes for selection. This, of course, requires the use of another table however. The documentation indicated that storage of the full english values is more appropriate. I have been unable to find information on performance issues of this decision.

Consider an example where a widget can have 30 character Keywords associated to it in a m-m relationship. The way I see it there are several ways to handle this scenario.

1. KEYWORD reference table: KEYWORD_ID (INT)(PK) KEYWORD (VARCHAR2(30))(Alt unique index)

    WIDGET_KEYWORD associative table:

	WIDGET_ID	(INT)(PK)
	KEYWORD_ID	(INT)(PK)

2.  KEYWORD reference table:
	KEYWORD		(VARCHAR2(30))(PK)

     WIDGET_KEYWORD associative table:
	WIDGET_ID	(INT)(PK)
	KEYWORD		(VARCHAR2(30)(PK)

3. Same as 2. above, but dispose of the reference table.

From a front-end development standpoint, scenario 3 is lacking in that a list of previously used keywords is not easily available for display/selection if desired.

Scenario 1 will save space due to the use of Integer values to represent the keywords, but requires that the KEYWORD table be queried to view the keywords for a widget, which is not required for scenario 2.

 What are the performance implications of indexing on numeric columns (as in 1) vs. indexing on character columns (as in 2)?

Any discussion on this matter is greatly appreciated--

Phil Zubaly
Z-Ware Engineering Received on Thu Feb 20 1997 - 00:00:00 CET