hi all:
DDL-1 : две таблицы, дочерняя связана с родительской по FK on delete cascade:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
recreate table tdetl(id int primary key using index tdetl_pk, pid int);
recreate table tmain(id int primary key using index tmain_pk);
alter table tdetl add constraint tdetl_fk foreign key (pid)
references tmain(id) on delete cascade
using index tdetl_pid;
commit;
insert into tmain values(1);
set term ^;
execute block as
declare variable n int = 500000;
begin
while (n>0) do insert into tdetl(id, pid) values(:n-1, 1) returning :n-1 into n;
end^
set term ;^
commit;
: те же две таблицы, но связи PK-FK нету, а каскадное удаление обеспечивается after_detele-триггером на родительской таблице:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
set term ^;
execute block as
begin
begin
execute statement 'drop trigger tmain_ad';
when any do begin end
end
end
^
set term ;^
commit;
recreate table tdetl(id int primary key using index tdetl_pk, pid int);
recreate table tmain(id int primary key using index tmain_pk);
commit;
create index tdetl_pid on tdetl(pid);
commit;
insert into tmain values(1);
set term ^;
execute block as
declare variable n int = 300000;
begin
while (n>0) do insert into tdetl(id, pid) values(:n-1, 1) returning :n-1 into n;
end
^
set term ;^
commit;
set term ^;
create trigger tmain_ad for tmain
active after delete position 0
as
begin
delete from tdetl d where d.pid = old.id; -- попрёт по индексу tdetl_pid, как и FK + каскад
end
^
set term ;^
commit;
Выполняю в обоих вариантах одно и тоже:
SQL> delete from tmain where id=1;
Для
варианта-1 (с FK + on delete cascade) вижу:
15558 ms, ..., 3501230 fetch(es)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
2014-07-21T00:00:21.1870 (424:0233BFD0) EXECUTE_STATEMENT_FINISH
C:\1INSTALL\FIREBIRD\FB25SNAP\T0.FDB (ATT_5, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\FB25SNAP\bin\isql.exe:1396
(TRA_30, CONCURRENCY | WAIT | READ_WRITE)
Statement 39:
-------------------------------------------------------------------------------
delete from tmain where id=1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMAIN INDEX (TMAIN_PK))
0 records fetched
15558 ms, 14529 read(s), 6668 write(s), 3501230 fetch(es), 500001 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 7
RDB$RELATION_CONSTRAINTS 10
TDETL 500000 500000
TMAIN 1 1
Для
варианта-2 (с простым AD-триггером на родительской таблице) вижу:
13894 ms, ..., 2500627 fetch(es)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
2014-07-21T00:05:17.5310 (424:0233BFD0) EXECUTE_TRIGGER_FINISH
C:\1INSTALL\FIREBIRD\FB25SNAP\T0.FDB (ATT_5, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\FB25SNAP\bin\isql.exe:1396
(TRA_35, CONCURRENCY | WAIT | READ_WRITE)
TMAIN_AD FOR TMAIN (AFTER DELETE)
13876 ms, 7261 read(s), 6418 write(s), 2500603 fetch(es), 500000 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 2
RDB$RELATION_CONSTRAINTS 4
TDETL 500000 500000
2014-07-21T00:05:17.5460 (424:0233BFD0) EXECUTE_STATEMENT_FINISH
C:\1INSTALL\FIREBIRD\FB25SNAP\T0.FDB (ATT_5, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\FB25SNAP\bin\isql.exe:1396
(TRA_35, CONCURRENCY | WAIT | READ_WRITE)
Statement 35:
-------------------------------------------------------------------------------
delete from tmain where id=1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TMAIN INDEX (TMAIN_PK))
0 records fetched
13894 ms, 7270 read(s), 6426 write(s), 2500627 fetch(es), 500001 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 3
RDB$RELATION_CONSTRAINTS 8
TDETL 500000 500000
TMAIN 1 1
Чем объяснить дифферент в 1 млн фетч, и к тому же - не впользу "встроенного" триггера, обеспечивающего FK ?
