An embarrassing " feature" in Oracle RDBMS (part 2)

In my previous post "An embarrassing " feature" in Oracle RDBMS (part 1)" I showed a way of  breaking the referential integrity of the database. The fact that this is possible in Oracle made me think about the consequences of such a feature.

If the metadata says that a FK-constraint is valid and enabled, then my understanding of how the optimizer works tells me that it potentially can pick an execution plan, which actually returns the wrong result.  And by creating an example, which did exactly that,  I hoped to get  Oracle to see the seriousness of this "feature".  (It's an common knowledge that if it something Oracle Corp. is serious about, it's to get their flagship product to return the correct result. Wrong results can never be anything but a bug.)

In December  I attended  "Joze Senegacnik" presentation "Query Transformations"at UKOUG-2007. In this presentation he had an example of how the Query Transformer sometimes eliminates a join. By using that idea it's possible to create a situation where Oracle would return the wrong result.  Check these queries:

SELECT child_tab.*
  FROM parent_tab,
       child_tab
WHERE parent_tab.parent_id= child_tab.parent_id;

SELECT *
  FROM child_tab
 WHERE EXISTS (SELECT 'X'
          FROM parent_tab
         WHERE parent_tab.parent_id = child_tab.parent_id);

The both happen to have the same execution plan:

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

The query transformer is aware of the valid FK constraint and uses this knowledge to eliminate access to parent_tab. Such a  plan would of course cause wrong result if the state of the FK constraint was wrong. So this proves that that this "feature" is nothing but a bug (see below):

dwdemo@SID> select * from child_tab; CHILD_ID PARENT_ID ---------- ---------- 1 1 2 1 3 2 4 2 dwdemo@SID> select * from parent_tab; PARENT_ID ---------- 2 dwdemo@SID> SELECT * 2 FROM child_tab 3 WHERE EXISTS (SELECT 'X' 4 FROM parent_tab 5 WHERE parent_tab.parent_id = child_tab.parent_id); CHILD_ID PARENT_ID ---------- ---------- 1 1 2 1 3 2 4 2

WRONG RESULT!

So I guess Oracle has to correct this feature. But what could be the options:

1. Don't allow DML on parent tables when FK:s on child tables are set to DISABLE VALIDATE.



2. Always re-validate FK constraints when they go from DISABLE VALIDATE to ENABLE VALIDATE.



3. Don't even allow FK:s to go to the state DISABLE VALIDATE.

Anyone with a better idea?

An embarrassing " feature" in Oracle RDBMS (part 1)

A couple of months ago, while I was playing around with loading data, I found out that it's actually possible to break the referential integrity of the database. And this without leaving any traces behind. Check this example

-- create a simple PK-FK relationship
CREATE TABLE parent_tab 
(parent_id NUMBER NOT NULL); 
    
ALTER TABLE parent_tab 
ADD CONSTRAINT parent_pk PRIMARY KEY (parent_id);

CREATE TABLE child_tab 
(child_id NUMBER NOT NULL, 
 parent_id NUMBER NOT NULL);
 
ALTER TABLE child_tab 
ADD CONSTRAINT child_pk PRIMARY KEY (child_id);

ALTER TABLE CHILD_TAB
ADD CONSTRAINT child_fk FOREIGN KEY (parent_id) 
REFERENCES parent_tab (parent_id);

-- Insert some values
INSERT INTO parent_tab VALUES (1);
INSERT INTO parent_tab VALUES (2);
INSERT INTO child_tab VALUES (1,1);
INSERT INTO child_tab VALUES (2,1);
INSERT INTO child_tab VALUES (3,2);
INSERT INTO child_tab VALUES (4,2);

-- Modify the FK-Constraint
ALTER TABLE child_tab 
MODIFY CONSTRAINT child_fk DISABLE VALIDATE;

We now have a "DISABLE VALIDATE" constraint.  Such a constraint does not allow any DML on  it's table. This makes sense cause how could the database ever guarantee the constraint  (keep it valid) + allow DML and at the same time not check if DML-changes confirms to the constraint. (Note: So in fact we now have a READ-ONLY table.  That feature does not exist in pre 11g. So maybe this is way simulating that feature in earlier versions of Oracle. But that's  Blog-entry for the future).  BUT the parent table will allow DML-changes as all it's constraints are still ENABLED VALDIATED.

By deleting some records from the parent table and  re-enabling the FK-constraint, it's actually possible to create some orphans.

-- Delete records from parent_tab
DELETE FROM parent_tab 
 WHERE parent_id = 1;

COMMIT;

-- Now Enable the FK-Constraint again
ALTER TABLE child_tab 
MODIFY CONSTRAINT child_fk ENABLE VALIDATE;

-- Check the status of the constraint
SELECT constraint_name, status, validated
  FROM user_constraints
 WHERE table_name = 'CHILD_TAB'
   AND constraint_type= 'R';

-- Check data and the status of the constraint
SELECT parent_id FROM parent_tab;
 
 PARENT_ID
----------
         2
 
SELECT parent_id FROM child_tab;
 
 PARENT_ID
----------
         1
         1
         2
         2
 
SELECT constraint_name, status, validated
  FROM user_constraints
 WHERE table_name = 'CHILD_TAB'
   AND constraint_type= 'R';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
CHILD_FK                       ENABLED  VALIDATED
 

All of a sudden we've broken the referential integrity of the database. Sounds like serious stuff to me.  Once I found this out I posted the above example in some Oracle forums. The reaction I got was mixed. Some people said it was a feature needed for loading huge amount of data in a DW-environment, some called it a Bug, but none really seemed to consider it more than annoying.   I also learned that this "feature" in actually in all versions from 8i-11g. I then raised an SR with Oracle and after some convincing I managed to get them to create an enhancement  request (They wouldn't of course call it a bug). But I still couldn't help being more than annoyed over this feature.

Think of the consequences:

1. As a DBA I can never ever be 100% sure that my constraints actually are valid.



2. I believe  such a "feature" can cause Oracle to return the wrong result.



3. One of the main jobs of a database is to guarantee referential integrity. This "feature" basically mean that Oracle can't do it. And hasn't been able to do it since 8i. I don't like to see Oracle being added to the list of databases that can't handle referential integrity.

On the other hand how can such a simple "feature" be present in version after version without anyone reporting it as a bug and without being caught by Oracles internal testing. So maybe after all this there is reason for this feature. Though I find it hard to believe.