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

On 25 May 2006 00:15:25 -0700, plinehan_at_yahoo.com wrote:

<snip>

>The part of my post which hasn't been addressed is
>what sort of indexes to put on the Person table - i.e.
>a bitmap index if it's only two values - what about higher
>numbers - what's the rule of thumb for the cardinality
>of bitmap indexes?
>
>Also, the construction of these "minor" tables - should
>they be indexed, and if so, what sort of index should be
>used - and if there are differences, why?
>
>
>Paul...
>

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'.

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.

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!

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.

If you use code to check if a key field value is allowed (which I would strongly advice against), 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.

Jaap. Received on Thu May 25 2006 - 04:06:15 CDT