SQL Gone Wild!
Ever see something so inefficient it evokes images of grape stomping to produce wine? I have, in Oracle 10g no less. A colleague of mine brought me a situation the other day that made me do a double-take, no triple-take, on a 10046 trace. The scenario involved a single row delete from a table using the index associated with the primary key on said table to delete the row, simple right? Well, the delete hung. The 10046 showed "db file sequential reads" spewing at a very rapid clip. The process was reading a child table that contained a column that referenced the primary key of the table being deleted. Okay, this is to be expected. We don't want to break our self-imposed business rules by orphaning child records. So what is my beef with this situation?
The child table had millions of rows that would have been orphaned had the delete succeeded. Keep in mind the constraint was NOT defined with ON DELETE CASCADE. Also, a single column index on the child table was associated with the child key. The stage was set for a swift and proper decline by Oracle to perform our delete. But this did not happen. Oracle was visiting ALL of the child rows then returning ORA-00292 "... - child record found." Yes, each and every child index entry was being visited. My colleague opened as SR with a very elegant little test case that reproduces the problem. Here it is. Try it for yourself and watch the trace with wonder and amazement. We have performed the test in 8i, 9i and 10g with the same results.
DROP TABLE CHILD;
DROP TABLE PARENT;
CREATE TABLE PARENT (COL1 NUMBER);
ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);
CREATE TABLE CHILD (COL1 NUMBER);
CREATE INDEX CHILD_IX_01 ON CHILD (COL1);
ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;
INSERT INTO PARENT VALUES (999999999999);
INSERT INTO CHILD VALUES (999999999999);
COMMIT;
-- Insert approximately 1 million records into CHILD
begin
for i in 1..20 loop
insert into child select * from child;
commit;
end loop;
end;
/
alter session set events '10046 trace name context forever, level 12';
DELETE FROM PARENT WHERE COL1 = 999999999999;
The child table had millions of rows that would have been orphaned had the delete succeeded. Keep in mind the constraint was NOT defined with ON DELETE CASCADE. Also, a single column index on the child table was associated with the child key. The stage was set for a swift and proper decline by Oracle to perform our delete. But this did not happen. Oracle was visiting ALL of the child rows then returning ORA-00292 "... - child record found." Yes, each and every child index entry was being visited. My colleague opened as SR with a very elegant little test case that reproduces the problem. Here it is. Try it for yourself and watch the trace with wonder and amazement. We have performed the test in 8i, 9i and 10g with the same results.
DROP TABLE CHILD;
DROP TABLE PARENT;
CREATE TABLE PARENT (COL1 NUMBER);
ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);
CREATE TABLE CHILD (COL1 NUMBER);
CREATE INDEX CHILD_IX_01 ON CHILD (COL1);
ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;
INSERT INTO PARENT VALUES (999999999999);
INSERT INTO CHILD VALUES (999999999999);
COMMIT;
-- Insert approximately 1 million records into CHILD
begin
for i in 1..20 loop
insert into child select * from child;
commit;
end loop;
end;
/
alter session set events '10046 trace name context forever, level 12';
DELETE FROM PARENT WHERE COL1 = 999999999999;
Why doesn't Oracle stop once it encounters the first index entry indicating a foreign key violation has just occurred? Isn't a single found entry sufficient to fail my statement? It seems a bit indulgent to check each and every child row irrespective of my barbaric attempt to break my own business rules. Is it a classic case of stupid is as stupid does? Nope. It is a good old fashioned Oracle bug.
By the way, the Oracle support analyst recommended putting the index associated with a child key in a read only tablespace as a workaround. Think about that for a second...
posted by Eric S. Emrick at 8/31/2006
5 Comments:
"By the way, the Oracle support analyst recommended putting the index associated with a child key in a read only tablespace as a workaround. Think about that for a second..."
Whatever, they did not.....did they?
True story unfortunately.
Hi Mark,
Yep. Very familiar with write consistency. Actually, the topic is one of my favorites in Tom's Expert Oracle Database Architecture book. While write consistency is most assuredly at work in this example, the paradigm alone does not seemingly justify the behavior.
In this example a single row delete is being attempted. No triggers on either table. No ON DELETE CASCADE. No DEFERRED constraints. We have just the most simplistic of referential integrity relationships at play.
You said..
So, It's doing all those reads cause it's reading the blocks "where col1 = 99999999". ALL of those reads happen *before* Oracle attempts to delete rows or execute any recursive SQL to validate constraint checks.
Certainly Oracle will need to perform a current read against the affected table block, preserving write consistency along the way. Of course, there is the undo block write as well. Even a current read on the child index is required to ensure no orphan(s) are left behind. The "problem" is Oracle is performing a current read on ALL applicable child index blocks. The parent row has already been "deleted" (change made just not committed) and the parent index modified accordingly. This is evident in a dump of the redo. With regard to integrity constraints Oracle is almost always table-data optimistic and most times index-data optimistic. That is, it will perform the intended DML on the row/index entry and then impose integrity validation. Now, the WHEN, with regard to the validation is highly dependent on the nature of the DML, single row or multi-row, and the type of integrity constraint. CHECK constraints are the only exception (Triggers sold separately :). If we don't satisfy a CHECK Oracle will not be table or index data optimistic. So, back to the original question. Why doesn't Oracle stop after the first current read of the child index that indicates a referential integrity violation? On non-deferred constraints Oracle does not wait until the statement has finished to validate the constraint. Try updating a primary key on a table with pk=pk+1. It doesn't fail with an integrity issue. Oracle is accommodating temporary violations along the way. However, if you change it to pk=mod(pk,2) the violation is detected well before the multi-row update completes (or reads all candidate rows as dictated by the WHERE clause). While Oracle is not always correct in their analysis they have deemed this behavior a bug, #5504961.
Thanks for the feedback and the link to Tom's discussion thread!
I've run the same test case and verified that the bug is fixed in 10.2.0.4
see http://hemantoracledba.blogspot.com/2008/06/delete-parent-checks-every-child-row.html
I have heard about another way of acrobat file repair. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.
Post a Comment
<< Home