RE: Foreign Keys on Views Performance Question

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Fri, 28 Aug 2009 15:12:02 -0400
Message-ID: <005001ca2813$6f96bd80$4ec43880$_at_com>

Do you have a primary key on x.a? if not can you create one and then grant the reference privildge to y which should allow you to create the foreign key?

Ken

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stuart Blackburn
Sent: Friday, August 28, 2009 1:07 PM
Cc: oracle-l_at_freelists.org
Subject: Foreign Keys on Views Performance Question

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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 28 2009 - 14:12:02 CDT