Link to home
Start Free TrialLog in
Avatar of GGuzdziol
GGuzdziolFlag for Luxembourg

asked on

Rely foreign key constraint in 9i/10g

Hello,

On 9i it goes as supposed:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>  create table pt (id number, value number);

Table created.

SQL>  create table ft (id number, value number);

Table created.

SQL>  alter table pt add constraint pt_pk primary key(id);

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;

Table altered.

But on 10g I'm encountering problems:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table pt (id number, value number);

Table created.

SQL> create table ft (id number, value number);

Table created.

SQL> alter table pt add constraint pt_pk primary key(id);

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;
alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate
*
ERROR at line 1:
ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

I've noticed that when I
SQL> create table pt (id number, value number);

Table created.

SQL> create table ft (id number, value number);

Table created.

SQL> alter table pt add constraint pt_pk primary key(id) rely;

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;

Table altered.

on my 10g it's ok - but what will be impact of having rely PK? And why does it differ on 9i/10g?

Thanks,
Grzegorz
Avatar of Ivo Stoykov
Ivo Stoykov
Flag of Bulgaria image

Hello GGuzdziol,

Here is what the error means
ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY
Cause: RELY is specified for the foreign key contraint, when the associated primary key constraint is NORELY.
Action: Change the option of the primary key also to RELY.

HTH

I
Avatar of GGuzdziol

ASKER

ivostoykov,

Let's assume that I've already done some google'ing for that error :-) And I've even put an example how do I do that with rely PK. The question is in my last sentence.

Thanks,
Grzegorz
Hi GGuzdziol,

Specify RELY to enable an existing constraint without enforcement. Specify NORELY to enable and enforce an existing constraint. The default is NORELY.



RELY you can use it in a data warehouse where you have loaded "scrubbed" data.  You can have a constraint enabled without being enforced (trust me -- that column  is UNIQUE, that foreign key -- its OK, that check constraint -- its accurate).  We can use that in query rewrites against materialized views.  If you are interested in a larger example showing the query rewrite (takes a bit of setup and walking through) I have a chapter on materialized views in my book that describes the use of this setting
and why its relevant.

Constraint States:

                 RELY NORELY
ENFORCED           No No
TRUSTED           Yes No
STALE_TOLERATED   Yes No


it has to do with query rewrite (materialized views, views) and when the constraint will be "used"

In query_rewrite_integrity = TRUSTED -- if the constraint was put in with "norely" -- it would not be used for rewrite (we would not assume the fkey was
really 'there' and in place).  for rely -- it would use it (even if Oracle has not validated the "fact")


regards

I
So You say that setting PK to rely should have no impact assuming historical data is enforced to be accurate (unique)?

I mean: it will keep new data clean (unique) and will have no performance impact, is that right?

And one more question - is there any scenario that it's reasonable to put RELY option without corresponding NOVALIDATE?

Thx
Hi GGuzdziol,

well this meand that it will impact query rewriting. It stated whether the server should RELY on a constraint when re-writing queries using materialized views and
such (should it *trust* you).

it does not change anything - just norely will cause re-validating.

As for the last question - for instance NULL/NOT NULL are never 'relied' upon unless validated.

regards

I
But what would happen if I created PK just as
alter table ... add constraint .. primary key(...)
- would optimizer rely on it? I'd just like to know if I can break something (i.e. performance) with creating most of my PK's with RELY option.

Full scenario is;
I'm now on 9i and as stated in my first post problem does not exist - I can create FK's with novalidate/disable/rely options and without touching PK. We're planning to migrate to 10g and it turned out something has changed. First natural workaround is to create pk's with rely option - and I don't know if it's ok.
Or maybe there's different solution then create pk with rely option?

Thanks
Hi GGuzdziol,

> would optimizer rely on i
well this is a hard question. I cannot answer it because i do not know the structure of the data.

What I could suject by heart is to try with RELY option on. But *only* with/without RELY option won't affect performance.

Finally I would suggest: test, test, test...

regards

I
Avatar of JacekMycha
JacekMycha

1. As already noted, RELY/NORELY clause is used solely by query rewrite.
DISABLE NOVALIDATE RELY means: "I don't want an index and constaraint checking to slow down my batch data loading into datawarehouse, but the optimizer can RELY on my data loading routine and assume this constraint is enforced by other mechanism". This information can greatly help optimizer to use correct materialized view when rewriting queries. So if you don't use materialized views for query rewrite then you can put RELY for all your constraints (or NORELY for all your constraits) and forget about it.

2. RELY/NORELY only matters in  NOVALIDATE state. If a constraint is in VALIDATE state then optimizer is sure that constraint is enforced and it doesn't have to rely on your declaration.

3. By default, a constraint is created as NORELY. So your primary key is ENABLE VALIDATE NORELY (for historical reasons).

4. Oracle 9i did not have ORA-25158 error. A 'creative' developer can create a PRIMARY KEY in DISABLE NOVALIDATE NORELY state (instructing the optimzer "don't trust me"), enter duplicate primary keys and then create a foreign key in DISABLE NOVALIDATE RELY  state (at the same time telling optimizer "trust me"). This makes the optimizer confused and unhappy.

5. Conclusion: Because your primary key is by default ENABLE VALIDATE, you can create it as RELY. It doesn't matter as long as your primary key is VALIDATEd.

JacekMycha
Avatar of Acton Wang
why do you need to use "rely" constraint? Are you in data warehouse? Do you use ETL process to get all the data?

as the above, "RELY" is a kind "trust me" mode. It is ususally used on materialized views or large data in data warehouse. Before you use it,  you need to know all implication along with it.

you can refer to Oracle data warehouse document for furthur information.


acton
>>  Are you in data warehouse?
Yes

>> Do you use ETL process to get all the data?
Yes

My main concern is: will changing PK from ,,norely enable validate'' to ,,rely enable validate'' have any implication?
>>"rely enable validate"

as JackeMycha said, rely only tell optimizer "trust me" whehter or not primary key is enabled or disabled. It sounds like "TRUST ME" even if the constraint is disabled.

Of couse, "TRUST ME" as constraint is enabled  looks like a waste but no harm to me.

Just curious: why you put a rely disabled fk but a norely enabled pk before in 9i? looks not consistent to me.
As far as I know norely gives nothing when it's combined with enable and validate. PK is enabled to keep primary key valid (and since unique index is used it doesn't cost too much - does it?)

FK probably is rely novalidate disable for performance reasons (I'm not author of this system that's why I'm only guessing) - do not check but enable query rewrites.

From logical point of view RELY/NORELY option for constraint that is enabled and validated makes no sense for me - I'd suppose that oracle should ignore it. But I do not know that - that's why I'm asking. I am pretty sure that someone must have encountered such a problem before (as rely disable novalidate foreign key is standard mechanism fo olap and it fails on 10g with PK that's validated and enabled - and with default norely option)
ASKER CERTIFIED SOLUTION
Avatar of JacekMycha
JacekMycha

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for Your help!

I suppose Jacek's explanation was the best, so points go to him.

And just FYI: http://tkyte.blogspot.com/2006/01/stuck-in-rut.html

Cheers!