Mittwoch, September 05, 2012

RELY Unschärfe

Vor ein paar Tagen hatte ich erwähnt, dass man einen als RELY definierten FOREIGN KEY nur mit Bezug auf einen mit RELY definierten PK erzeugen kann, da man sonst den ziemlich deutlichen Fehler "ORA-25158: RELY kann für Fremdschlüssel nicht angegeben werden, wenn der verknüpfte Primärschlüssel NORELY ist." erhält:

-- 11.1.0.7
drop table c;
drop table p;

create table p
as
select 1 id from dual;

alter table p add constraint p_pk primary key (id);

create table c
as
select 1 id from dual;

alter table c add constraint c_fk foreign key (id) references p(id) rely;

alter table c add constraint c_fk foreign key (id) references p(id) rely
*
FEHLER in Zeile 1:
ORA-25158: RELY kann für Fremdschlüssel nicht angegeben werden, wenn der verknüpfte Primärschlüssel NORELY ist.

So also nicht. Aber was bedeutet folgendes Verhalten?

alter table c add constraint c_fk foreign key (id) references p(id);

alter table c modify constraint c_fk rely novalidate;
alter table c disable constraint c_fk;

select constraint_name
     , rely
     , validated
     , status
  from user_constraints
 where constraint_name in ('P_PK', 'C_FK');

CONSTRAINT_NAME                RELY VALIDATED     STATUS
------------------------------ ---- ------------- --------
C_FK                           RELY NOT VALIDATED DISABLED
P_PK                                VALIDATED     ENABLED

explain plan for
select c.id
  from c
     , p
 where c.id = p.id;

select *
  from table(dbms_xplan.display('', ''));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2174124444

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| C    |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C"."ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

Wenn man die Anlage und die Umstellung auf RELY voneinander trennt, funktioniert die Umstellung demnach ohne Fehler und laut user_constraints ist der FK tatsächlich als RELY definiert, obwohl der PK es nicht ist. Und für die Test-Query funktioniert auch die table elimination. Seltsam.

Kommentare:

  1. Hallo Martin,

    zwei Kommentare hierzu:

    In früheren Versionen (ganz sicher bin ich mir nicht, welche Versionen dies waren, entweder 9.2 oder 10), bin ich mir recht sicher, dass diese Unschärfe grundsätzlich auch beim Anlegen schon möglich war.

    Erst in späteren Versionen wurde dies verschärft.

    Bezüglich Deines Kommentars der erfolgreichen "Table Elimination" wäre der Test aussagekräftiger, wenn Du die Constraints als DISABLE NOVALIDATE angelegt hättest, denn das ist ja die Hauptaufgabe der RELY-Variante, trotzdem den Optimizer zu solchen Optimierungen zu bewegen, auch wenn die Constraints nicht durch die Datenbank erzwungen werden.

    In Deinem Testfall sind die Constraints ja defaultmäßig aktiv und validiert, so dass es keinen Grund gibt, warum der Optimizer dies nicht glauben sollte.

    Randolf

    AntwortenLöschen
  2. Hallo Randolf,

    Danke für die Hinweise. Punkt 2 werde ich gleich noch im Beispiel berücksichtigen.

    Martin

    AntwortenLöschen