|Constraint state in DWH environment (rely disable novalidate) [message #123342]
|Sun, 12 June 2005 04:30
Registered: June 2005
i have some questions regarding the state of primary and foreign key constraints in an data warehouse environment.
some information about the basic structure of the data warehouse
- oracle 9i database
- star schema with partitioned fact table, partitioned by a year column. a large customer table is partitioned and acts as a dimension for the fact table
- artifical primary keys, referenced by fk-columns in the fact table
- materialized views will be used, query rewrite is enabled
- the ETL process guarantees that the data inserted into the tables satisfy fk and pk constraints, therefore RELY state for the constraints should be sufficient.
- the fact table contains the last 10 years of data, i.e it's a rolling time window
here come the questions:
- to improve administration and the ETL process, i want to avoid global indexes on the fact table and the customers table. A PK is necessary on the customer's table to be referenced by the fact table. since the ETL process guarantees data quality, setting the PK to RELY DISABLE NOVALIDATE should be ok to avoid creation of the corresponding global PK index. if user queries access the fact and customer tables directly, not via a materialized view, does the optimizer takes PK and FK constraints in RELY DISABLE state into account to optimize the queries? indexes do exist for all FK columns.
- do you have any experiences with problems / bugs regarding constraints in RELY DISABLE NOVALIDATE state? (optimizer, query rewrite, ...) data is loaded into the fact table using exchange partition, therefore there will be no problems with insert and updates because of DISABLE NOVALIDATE.
- what constraint states do you usually use in your data warehouses and why? would be great if you could share some information to learn more about the pros and cons of different constraint states.
Thanks a lot in advance for your efforts.