Oracle 캐스케이드 삭제

Oracle 캐스케이드 삭제

테이블의 캐스케이드 삭제가 개별 삭제 문(단일 plsql 블록에서 실행)보다 효율적입니까?

무엇을cascade delete개별 삭제 문을 발행합니다.

다음 테스트 사례를 검토합니다.

create table parent 
 (parent_id number, 
  parent_name varchar2(30), 
  constraint parent_pk primary key (parent_id) using index);

create table child 
 (child_id   number,
  parent_id  number,
  child_name varchar2(30),
  constraint child_pk primary key (parent_id, child_id) using index,
  constraint child_fk01 foreign key (parent_id) 
    references parent (parent_id) 
      on delete cascade;
 );


insert into parent
 (parent_id, parent_name)
select object_id, object_name from dba_objects where rownum <= 10000;

begin
  for i in 1..10
  loop
    insert into child
      (child_id, parent_id, child_name)
    select i, parent_id, parent_name
      from parent;
  end loop;
end;
/

exec dbms_stats.gather_table_stats (tabname => 'PARENT', cascade => true);
exec dbms_stats.gather_table_stats (tabname => 'CHILD', cascade => true);

exec dbms_monitor.session_trace_enable;
alter table child drop constraint child_fk01;
alter table child add constraint child_fk01 foreign key (parent_id)
  references parent (parent_id) on delete cascade enable novalidate ;
delete from parent;
rollback;

추적 파일에서 다음과 같은 행을 찾을 수 있습니다.

  delete from "<MY_SCHEMA_NAME>"."CHILD" where "PARENT_ID" = :1
END OF STMT
PARSE #6:c=0,e=182,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1293353992514766
EXEC #6:c=0,e=545,p=0,cr=2,cu=32,mis=1,r=10,dep=1,og=4,tim=1293353992515354
EXEC #6:c=0,e=233,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515644
EXEC #6:c=0,e=238,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515931
EXEC #6:c=0,e=252,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992516229
EXEC #6:c=0,e=231,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516507
EXEC #6:c=0,e=227,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516782
EXEC #6:c=0,e=244,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992517072
EXEC #6:c=0,e=219,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517337
EXEC #6:c=0,e=236,p=0,cr=3,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517622
EXEC #6:c=0,e=235,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517921
EXEC #6:c=0,e=229,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518196
EXEC #6:c=0,e=246,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992518487
EXEC #6:c=0,e=234,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518767
EXEC #6:c=6999,e=570,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992519383

즉, Oracle은 다음에 대해 삭제 명령문을 발행합니다.CHILD삭제하는 각 레코드에 대해PARENT.

또 다른 질문은 두 가지 중 어느 것이 더 효율적인지에 대한 것입니다.

DELETE FROM CHILD WHERE PARENT_ID = 1;
DELETE FROM PARENT WHERE PARENT_ID = 1;

대

DELETE FROM PARENT WHERE PARENT_ID = 1;

와 둘 다on delete cascade가능한.놀랍게도 위의 첫 번째 사례에서 Oracle은 하위 테이블의 외부 키 인덱스를 조사하여 캐스케이드가 필요한 행이 있는지 확인합니다.행이 없는 경우 Oracle은 계단식 삭제를 실행하지 않습니다.

이와 같이 두 옵션을 비교할 수 없습니다.그것은 성능 문제가 아니라 더 많은 디자인과 구조입니다.

기본/외부 키를 사용하여 데이터베이스를 설계하는 경우, 수동으로 어떤 열과 테이블에 외부 키가 있고 일치하는 SQL 문을 생성하는지 검색하는 것보다 계단식 삭제를 사용하여 삭제하는 것이 더 쉽습니다.

계단식 삭제 기능의 주요 이점은 삭제 작업을 수행하는 데 필요한 SQL 문의 양을 줄일 수 있다는 것입니다.

계단식 삭제는 하위 항목을 삭제하는 동안 교착 상태 문제를 방지하는 것이 좋습니다.응용 프로그램에서 하위 항목의 개별 삭제와 관련된 성능 문제를 하나 해결했습니다.우리는 캐스케이드로 교체했고 이제 모든 것이 잘 작동하고 있습니다.

단계적으로 삭제하고 외부 키를 정의하지 않은 경우 다음과 같은 방법을 사용할 수 있습니다.

DELETE FROM my_table
 WHERE ROWID IN
     ( SELECT ROWID
         FROM my_table
        START WITH (condition_on_the_row_that_you_want_to_delete)
      CONNECT BY PRIOR (primary_key) = (self_foreign_key)
     )

언급URL : https://stackoverflow.com/questions/8571692/oracle-cascade-delete