Foreign key referential integrity in the fact tables (Oracle 11g)

View previous topic View next topic Go down

Foreign key referential integrity in the fact tables (Oracle 11g)

Post  sachij3u on Thu Jul 11, 2013 2:02 pm

Hi,

   I am working on designing a medium sized datamart using dimensional modeling. While designing fact tables i was planning to remove the FK constraints for better etl performance. But i also read that ORACLE query optimizer appears to be impaired when FK constraints are dropped in 9i or greater.  Release 10g R2 improves the optimizer, again relying on the existence of the FK constraints. Any pointers in this direction would help me take a decision, thanks...
avatar
sachij3u

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables (Oracle 11g)

Post  ngalemmo on Thu Jul 11, 2013 2:10 pm

Oracle allows you to define constraints but not enforce them. This allows you to have efficient loading as well as provide the information the query optimizer needs.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables (Oracle 11g)

Post  sachij3u on Thu Jul 11, 2013 2:30 pm

Thanks, Do you meant we keep the FK constraint with "Enable Novalidate" option?
Enable Novalidate Definition: When a constraint is in the enable novalidate state, all subsequent statements are checked for conformity to the constraint. However, any existing data in the table is not checked. This option would again check for conformity and affect the etl load performance.
avatar
sachij3u

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables (Oracle 11g)

Post  ngalemmo on Thu Jul 11, 2013 3:19 pm

Haven't worked on oracle in a few years, so I don't have a manual handy. I recall you can set it up as disabled.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables (Oracle 11g)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum