| --
-- Nature of problem: Optimization problem OracleRDB V6.0-15 and V7.0
--
-- Run this file with RDMS$DEBUG_FLAGS SO and observe output.
-- In the first DELETE, a sequential access is performed.
--
-- This message is commented in SQL format.
--
drop database filename 'pms70965.RDB';
--
set language ENGLISH;
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
create database
filename 'pms70965.RDB'
dictionary is NOT REQUIRED
protection is ACL
number of users 100
number of cluster nodes 1
buffer size is 12 blocks
number of buffers 256
number of recovery buffers 512
adjustable lock granularity ENABLED
global buffers are DISABLED (number is 500, user limit 5)
carry over locks are ENABLED
lock timeout interval is 0 seconds
statistics collection is ENABLED
system index compression is DISABLED
No restricted access
reserve 10 storage areas
reserve 1 journals
snapshot is ENABLED IMMEDIATE
segmented string storage area is RDB$SYSTEM
create storage area RDB$SYSTEM
filename 'pms70965sys.RDA'
-- read write storage area
locking is row level
page format is UNIFORM
page size is 2 blocks
allocation is 400 pages
snapshot filename 'pms70965sys.SNP'
create storage area AREA_01
filename 'pms70965_01.RDA'
-- read write storage area
locking is row level
page format is MIXED
page size is 12 blocks
allocation is 100 pages
snapshot filename 'pms70965_01.SNP'
interval is 256
thresholds are (94,95,99)
create storage area AREA_02
filename 'pms70965_02.RDA'
-- read write storage area
locking is row level
page format is MIXED
page size is 12 blocks
allocation is 100 pages
snapshot filename 'pms70965_02.SNP'
interval is 256
thresholds are (94,95,99)
create storage area AREA_03
filename 'pms70965_03.RDA'
-- read write storage area
locking is row level
page format is MIXED
page size is 12 blocks
allocation is 100 pages
snapshot filename 'pms70965_03.SNP'
interval is 256
thresholds are (94,95,99)
create storage area AREA_04
filename 'pms70965_04.RDA'
-- read write storage area
locking is row level
page format is UNIFORM
page size is 12 blocks
allocation is 100 pages
snapshot filename 'pms70965_04.SNP'
; -- end create database
create domain STD_TXT_30 CHAR (30);
create domain INT_ID_NUM CHAR (26);
create domain CODE_NR SMALLINT
check(( value >= 0))
not deferrable;
commit work;
create table PARENT_TABLE (
LINIE_NUM INT_ID_NUM
primary key deferrable,
LINIE_TEXT STD_TXT_30);
create unique index PARENT_HIDX_PS
on PARENT_TABLE (LINIE_NUM)
type is HASHED
store using (LINIE_NUM)
in AREA_01
with limit of ('AZZZZZZZZZZZZZZZZZZZZZZZZZ')
in AREA_02
with limit of ('LZZZZZZZZZZZZZZZZZZZZZZZZZ')
otherwise in AREA_03;
commit work;
create storage map PARENT_MAP
for PARENT_TABLE
store using (LINIE_NUM)
in AREA_01
with limit of ('AZZZZZZZZZZZZZZZZZZZZZZZZZ')
in AREA_02
with limit of ('LZZZZZZZZZZZZZZZZZZZZZZZZZ')
otherwise in AREA_03
placement via index PARENT_HIDX_PS;
create table CHILD_TABLE (
LINIE_NUM INT_ID_NUM,
foreign key (LINIE_NUM) references PARENT_TABLE (LINIE_NUM)
deferrable,
LEV_POS_NR CODE_NR,
primary key (LINIE_NUM,LEV_POS_NR)
deferrable,
LINIE_TEXT STD_TXT_30);
create index CHILD_HIDX_PS
on CHILD_TABLE (LINIE_NUM)
type is HASHED
store using (LINIE_NUM)
in AREA_01
with limit of ('AZZZZZZZZZZZZZZZZZZZZZZZZZ')
in AREA_02
with limit of ('LZZZZZZZZZZZZZZZZZZZZZZZZZ')
otherwise in AREA_03;
commit work;
create storage map CHILD_MAP
for CHILD_TABLE
store using (LINIE_NUM)
in AREA_01
with limit of ('AZZZZZZZZZZZZZZZZZZZZZZZZZ')
in AREA_02
with limit of ('LZZZZZZZZZZZZZZZZZZZZZZZZZ')
otherwise in AREA_03
placement via index CHILD_HIDX_PS;
create trigger PARENT_TABLE_BD_TG
before delete on PARENT_TABLE
(delete from CHILD_TABLE C1
where (C1.LINIE_NUM = PARENT_TABLE.LINIE_NUM)
) for each row;
commit work;
--
-- Now some data
--
insert into parent_table (LINIE_NUM,LINIE_TEXT)
values ('AZZZZZZZZZZZZZZZZZZZZZZZZZ','The A Record');
insert into parent_table (LINIE_NUM,LINIE_TEXT)
values ('LZZZZZZZZZZZZZZZZZZZZZZZZZ','The L Record');
insert into parent_table (LINIE_NUM,LINIE_TEXT)
values ('ZZZZZZZZZZZZZZZZZZZZZZZZZZ','The Z Record');
insert into child_table (LINIE_NUM,LEV_POS_NR,LINIE_TEXT)
values ('AZZZZZZZZZZZZZZZZZZZZZZZZZ',1,'The A1 Child Record');
insert into child_table (LINIE_NUM,LEV_POS_NR,LINIE_TEXT)
values ('LZZZZZZZZZZZZZZZZZZZZZZZZZ',1,'The L1 Child Record');
insert into child_table (LINIE_NUM,LEV_POS_NR,LINIE_TEXT)
values ('ZZZZZZZZZZZZZZZZZZZZZZZZZZ',1,'The Z1 Child Record');
commit;
--
-- Now watch the debug log and see the strategy
--
select a.linie_num,a.linie_text,b.lev_pos_nr,b.linie_text
from parent_table a, child_table b
where a.linie_num=b.linie_num
and a.linie_num='AZZZZZZZZZZZZZZZZZZZZZZZZZ'
order by b.linie_num,b.lev_pos_nr;
roll;
--
-- This worked OK;
--
delete from parent_table where linie_num='AZZZZZZZZZZZZZZZZZZZZZZZZZ';
commit;
--
-- This does not use the most efficient strategy.
--
create unique index CHILD_SIDX_P on CHILD_TABLE
(LINIE_NUM,LEV_POS_NR)
type is sorted
usage update
enable compression store in AREA_04;
commit work;
--
-- Now we've added an index.
--
finish;
attach 'file pms70965.RDB';
--
select a.linie_num,a.linie_text,b.lev_pos_nr,b.linie_text
from parent_table a, child_table b
where a.linie_num=b.linie_num
and a.linie_num='LZZZZZZZZZZZZZZZZZZZZZZZZZ'
order by b.linie_num,b.lev_pos_nr;
--
-- This worked OK;
--
delete from parent_table where linie_num='LZZZZZZZZZZZZZZZZZZZZZZZZZ';
commit;
-- Now it works a little better, but not much.
--
-- This appears to be an optimization problem
--
-- It would be nice if it worked without the Primary Key unique index,
-- and it could chose a faster strategy when there is one.
--
-- In Parent child relationships where the number of children IS ALWAYS
-- very small (in our case it is <= 1), the storage model as described
-- above is the most efficient, and it should be unnecessary to have a
-- unique index on the Primary Key.
--
|
| Ian,
Here is the reply from the customer. I suspect you might know him, he
knows you!
I know it's the foreign key that's causing the problem. The question
is why?
1) Why does it do it this way?
2) Might engineering agree to improve this?
It would seem to me, that despite the absence of a syntax in SQL to
declare both the cascade delete and the foreign/primary relationship
within a single definition, it should be possible, indeed reasonable,
for the optimizer to recognize the situation described in the example
and get the answer right.
I think the right answer for the constraint evaluation is to recognize
that the associated primary key is gone within the context of the same
transaction and perform no optimization at all.
Please feel to correct me if I'm wrong, as "it would be a mistake to
overestimate my intelligence"
Send this quote and my regards to Ian.
cheers
pjl
Colin
|
| ~ I know it's the foreign key that's causing the problem. The question
~ is why?
Well Philip might have known, but the original posting didn't say so.
~ 1) Why does it do it this way?
I have not had a chance to investigate, but it is on my long list of things to
do.
~ 2) Might engineering agree to improve this?
Yes. We are always looking to improve stuff :-)
~ It would seem to me, that despite the absence of a syntax in SQL to
~ declare both the cascade delete and the foreign/primary relationship
~ within a single definition, it should be possible, indeed reasonable,
~ for the optimizer to recognize the situation described in the example
~ and get the answer right.
We do plan to support the UPDATE options for constraints eventually.
Currently, it is not possible for the optimizer to detect this. This is
because the TRIGGER and the CONSTRAINT are seperate requests (aka queries).
~I think the right answer for the constraint evaluation is to recognize that
~the associated primary key is gone within the context of the same transaction
~and perform no optimization at all.
This is easy to see from a global point of view.
Ian
|