Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  sachij3u 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...
sachij3u
sachij3u

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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  sachij3u 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.
sachij3u
sachij3u

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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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