Re: Ref. table performance in Oracle

In article <3365EC34.2767_at_earthlink.net>, Barry Hawes <bjh01_at_earthlink.net> writes

>> 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.
>>
>
> I don't see any space saving in scenario 1 as you'll be building
> an altenate unique index on keyword anyway for value querying. If
> anything, you'll be needlessly wasting the space of a redundant
> unique identifier in keyword_id. I'd even go as far as to question
> the need for widget_id instead of widget_name or another more
> meaningful column in the widget table.

Not so! Normally in these situations the reference table will be small, while the associated table will be larger (perhaps very large). So the space savings could be substantial, both in the indexspace and tablespace of the latter table, while the cost of the reference table secondary index will generally pale to insignificance.

> A bigger impact may be allowing keyword to be a variable length field,
> test it as fixed length also, high volatility of a variable length
> field can be an impact.

I share your reservations about VARCHAR in a key columm.

I think discarding reference tables (option 3) is a bit risky too - unless it is just a fixed look-up table, such as days of the week. If this is the case, you could go with option 3, but using KEYWORD_ID instead, and a view using the CASE statement to expand the id. to the full meaning. I think Oracle supports this? The reference data *must* be completely static for this to be viable though, unless you want to change the view every time a new value is needed!

-- 
Jeremy Rickard