Re: When to use check constraints and when to use small tables?

Comments embedded

On 25 May 2006 06:13:34 -0700, plinehan_at_yahoo.com wrote:

>
>
>> You use bitmap indexes on foreign key fields of a table when you want
>> to access the table by this fields combinedly (if that's an english
>> word), for instance with the question:
>
>> how many persons are 'Female' with the title 'His Holyness'.
>
>
>Yes, I understand this, but my question is really about when
>do you start/stop doing this? As I understand it, bitmap
>indices are to be used when the cardinality of the column
>is low - but what is low? Is it 10 values? 20? 50? Or is it
>the cardinality with respect to the total number of fields?
>50% (M/F)? 33% (M/F/U)? 10% (Title)? 2% (US States)?
>

The cardinality is the number of distinct values in that particular key. For me: anything less below 5 but definitely 10.
>
>> Your query would look up these descriptions in gender table and title
>> table and with the related codes access the persons table. These
>> queries are typical for a datawarehouse. In this case access on the
>> foreign key fields could be solved by the optimizer by first combining
>> the bitmap indexes (for which these indexes are extremely suited) and
>> with the resulting rowid's accessing the table itself.
>
>
>I understand this - my question about *_when_* it should
>be done remains.
>
>
>> In an OLTP environment the access is usually the other way around: you
>> insert/update a record in the persons table and the codes are checked
>> against the title table and gender table. For this you don't need any
>> indexes on the foreign key fields. And if you want your foreign key
>> fields indexed anyway (there are issues when updating the primary key
>> in a minor table), you probably never will do a combined search so a
>> normal index will do. Apart from this, the persons table will be
>> subject to many changes, for which bitmap indexes are not suited. Even
>> locking would no longer be row based!
>
>
>Why would you not do the index?

Bitmapped indices have their penalty as the entire key (in the index) needs to be updated.
Apart from that: in this particular case, assuming a non-skewed distribution, the index isn't going to be used in queries anyway.

When creating a new person
>for example, while they might change other attributes like their
>address, their gender is hardly likely to change! Nor is title,
>(apart from Ms. to Mrs., which is less and less nowadays anyway).
>
>
>As I understand it, Oracle doesn't automatically create an
>index against foreign key columns - there are RDBMS's
>that do.
>
>> If you use the database schema to enforce the key field values in the
>> persons table, you have to define the key fields as foreign keys and
>> the related fields in the minor tables as primary keys, which will
>> create an index for each primary key, explicitly or implicitly.
>
>Does it do this automatically?

If there is no index on that column it does.

Suppose you have a gender field
>which you don't want indexed, for example because it might
>be skewed?

If the column is skewed it is a *perfect* candidate for an index. But then of course you could index only individual values by using a function based index instead of a bitmap. Works like a charm.
>
>> If you use code to check if a key field value is allowed (which I
>> would strongly advice against),
>
>
>It would be OK in a trigger, surely?
>

That would be very inefficient as compared to a check constraint or a foreign key.
>
>> you can choose if you want to create
>> an index on the minor tables. If the table is big, this is the smart
>> thing to do (so the optimizer can use the index for a lookup), if it
>> is small, who cares? So do it in any case, that way you don't have to
>> wreck your brains on this issue for every table.
>
>
>My question is what sort of index do you have on the minor
>tables - IOT or ordinary? My understanding is that it should
>be IOT - am I wrong?
>
>

It probably should be IOT.

>
>Paul...
>
>
>> Jaap.

--
Sybrand Bakker, Senior Oracle DBA