Foreign Keys on Views Performance Question
Date: Fri, 28 Aug 2009 13:07:19 -0400
I have a performance tuning question. We have the following scenario:
- Schema x has base table A.
- Schema y has base table B.
- Schema y has a view C which queries x.A
- Schema y has a view D which joins y.C and y.B.
I can create a primary key constraint on table y.B and on views y.C and y.D. But since I cannot create a foreign key from y.B to y.C will the optimizer know how to join these tables/views properly? The primary keys on the views are created with "NORELY DISABLE NOVALIDATE". The foreign key from y.B to y.C cannot be created due to a ORA-02270 error which I'm assuming is normal if create a foreign key that references a view.
The execution plans our optimizer is coming up with are extremely inefficient and it looks like it's because it doesn't know how to join y.C and y.B.
Is designing something like this possible or will you always suffer from poor execution plans? Duplicating the data in both schemas is not an option due to storage constraints.
22.214.171.124 Enterprise Edition.