Foreign Keys on Views Performance Question
- From: Stuart Blackburn <sblackbu@xxxxxxxxx>
- Date: Fri, 28 Aug 2009 13:07:19 -0400
Hi all,
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.
11.1.0.7 Enterprise Edition.
Thanks,
Stuart
--
//www.freelists.org/webpage/oracle-l
Other related posts:
