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

> 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)?

> 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? 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? Suppose you have a gender field which you don't want indexed, for example because it might be skewed?

> 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?

> 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?

Paul...

> Jaap.
Received on Thu May 25 2006 - 08:13:34 CDT