Re: Warehousing : Constraints

From the Oracle doco.

The Oracle database utilizes constraints when optimizing SQL queries. Although constraints can be useful in many aspects of query optimization, constraints are particularly important for query rewrite of materialized views.

Data warehouse applications recognize multi-dimensional cubes in the database by identifying integrity constraints in the relational schema. Integrity constraints represent primary and foreign key relationships between fact and dimension tables. By querying the data dictionary, applications can recognize integrity constraints and hence the cubes in the database. However, this does not work in an environment where DBAs, for schema complexity or security reasons, define views on fact and dimension tables. In such environments, applications cannot identify the cubes properly. By allowing constraint definitions between views, you can propagate base table constraints to the views, thereby allowing applications to recognize cubes even in a restricted environment. View constraint definitions are declarative in nature, but operations on views are subject to the integrity constraints defined on the underlying base tables, and constraints on views can be enforced through constraints on base tables. Defining constraints on base tables is necessary, not only for data correctness and cleanliness, but also for materialized view query rewrite purposes using the original base objects.

Materialized view rewrite extensively uses constraints for query rewrite. They are used for determining lossless joins, which, in turn, determine if joins in the materialized view are compatible with joins in the query and thus if rewrite is possible.

End quote.

And materialized views will be very important to a data warehouse, of course. Though constraints can also be placed on ordinary views. But yes, at the end of the day, constraints help the optimiser work out what to do, therefore seeing constraints is likely to result in better plans than not seeing them.

As for the disadvantages of constraints, recall that 9i now also allows the pre-fetch and cacheing of parent primary keys when performing the 2nd piece of DML on the child table. Should speed things up a bit.

If indexes aren't your thing, try creating your constraints as disabled validate. Enforcement of uniqueness, but no index required. Buggers up your DML a bit (ie, you aren't allowed to do any!), but then there's always the option to drop and re-create the constraint (and because it's re-created in the disabled state, it should be trivially fast to re-create).

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
news:9t2b0l$5ja$1_at_ctb-nnrp2.saix.net...


> 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

>

>