Re: Warehousing : Constraints

Thanks for the comments.

Dusan Bolek" <pagesflames_at_usa.net> wrote
>You need to have warranted consistency between parents and childs,
>because if parent table lacks some records from child table you can
>easily get no errors, but weird results.

So the requirement is parent child integrity - a very valid reason (critical IMO). However, that can be done without necassarily having to use foreign key constraints, though fk constraints are the "best solution" from a pure database perspective.

Howard J. Rogers" <dba_at_hjrdba.com> wrote
>The point of the new feature was that data warehouses often used views
>and Materialized Views to speed things up -but that left the optimiser
>completely blind to the relationships between the underlying tables,
>and hence resulted in sub-optimal execution plans.

So the reason then for wanting fk constraints is to assist the CBO in determining the best execution plan? Can you provide some backup to that statement? I can not see how Oracle will treat a VIEW differently wrt optimisation. A VIEW is nothing but a SUB-SELECT - whether that is coded inline in a SQL statement, or called via a view in the SQL statement, I can not see offhand how the CBO can decide to do it differently.

Also, there are other problems with query optimisation. Especially when Oracle CBO is dealing with VLTs. It needs accurate stats. You can not easily create accurate stats from a VLT as this will require a substansive data sample to be taken from the VLT. The runtime and processing windows often do not allow for this. E.g. a 10% sample (which just fits into the allocated processing slot) is not indicative of the table and will cause the CBO to make the wrong decision.

The reasons behind _why_ wanting fk constraints are very valid IMO. But there are always more than one way to skin a cat.

Okay, so why do I think that fk constraints is a problem? Just an every day issue that DBAs and programmers deal with. Performance.

Let's say you fact table refers to 20 dimensions. This means 20 constraint checks per row inserted. 20 index lookups. That is a huge overhead when dealing with the normal single large transaction in the data warehouse environment.

How do you address this? Does the requirements for wanting fk constraints justify the decrease in load and update performance? Are there no alternatives to use to still meet those requirements, but without resorting to fk constraints and the associated performance problems?

--
Billy