select count(*) from SALE$ACC_TR_LINK sl where sl.ID_TR_BON is not null

COUNT
1

Query
------------------------------------------------
select *
from SALE$ACC_TR_LINK sl
join SALE$DOC_ITEM_BPS dib on dib.ID_SDI = sl.ID_SDI
join BILL$POSITIONS bps on dib.ID_BPS = bps.ID
where sl.ID_TR_BON = :ID_TR

Plan
------------------------------------------------
PLAN JOIN (DIB NATURAL, BPS INDEX (BILL$POSITIONS), SL INDEX (SALE$ACC_TR_LINK_SDI))

Query Time
------------------------------------------------
Prepare : 31,00 ms
Execute : 12 870,00 ms
Avg fetch time: 0,00 ms

Operations
------------------------------------------------
Read : 30 315
Writes : 0
Fetches: 18 684 568
Marks : 0


Enchanced Info:
Table Name Indexed | Non-Indexed
BILL$POSITIONS 1547919 0
SALE$ACC_TR_LINK 821988 0
SALE$DOC_ITEM_BPS 0 1547919

SALE$ACC_TR_LINK_TRB 0.5

остальные связки вот такого порядка.
SALE$ACC_TR_LINK_SDI 0.00000121..

Query
------------------------------------------------
select *
from SALE$ACC_TR_LINK sl
join SALE$DOC_ITEM_BPS dib on dib.ID_SDI = sl.ID_SDI+0
join BILL$POSITIONS bps on dib.ID_BPS = bps.ID
where sl.ID_TR_BON = :ID_TR

Plan
------------------------------------------------
PLAN JOIN (SL INDEX (SALE$ACC_TR_LINK_TRB), DIB INDEX (SALE$DOC_ITEM_BPS_SDI), BPS INDEX (BILL$POSITIONS))

Query Time
------------------------------------------------
Prepare : 31,00 ms
Execute : 31,00 ms
Avg fetch time: 0,00 ms


Operations
------------------------------------------------
Read : 2
Writes : 0
Fetches: 12
Marks : 0

Enchanced Info:
Table Name Indexed | Non-Indexed
- - -

set term ^ ;
set SQL dialect 3^
create database 'c:\temp\test.fdb' user 'SYSDBA' password 'masterkey'
page_size 4096^

create domain PK bigint not null^
create domain REF bigint^
create domain TEXT varchar(100) ^

create table MASTER(
ID PK,
TEXT TEXT,
constraint MASTER
primary key(ID)
)
^
create table SLAVE(
ID PK,
LINK REF,
TEXT TEXT,
constraint SLAVE
primary key(ID),
constraint SLAVE_LINK
foreign key (LINK)
references MASTER(ID)
)
^
create table REF(
ID PK,
TEXT TEXT,
constraint REF
primary key(ID)
)
^


create table DOUBLE_SLAVE(
ID PK,
LINK REF,
REF1 REF, -- another master reference almost never null
REF2 REF, -- another master reference almost always null
TEXT TEXT,
constraint DOUBLE_SLAVE
primary key(ID),
constraint DOUBLE_SLAVE_LINK
foreign key (LINK)
references MASTER(ID),
constraint DOUBLE_SLAVE_REF1
foreign key (REF1)
references REF(ID),
constraint DOUBLE_SLAVE_REF2
foreign key (REF2)
references REF(ID)
)
^

execute block
as
declare i bigint;
begin
i = 0;
while (i < 1000000)
do begin
insert into MASTER(ID, TEXT) values (:i, :i);
insert into REF(ID, TEXT) values (:i, :i);
insert into SLAVE(ID, LINK, TEXT) values (:i, :i, :i);
insert into DOUBLE_SLAVE(ID, TEXT, LINK, REF1) values (:i, :i, :i, :i);
i = i + 1;
end
update DOUBLE_SLAVE set REF2 = 100000 where ID = 100000;
end
^

set statistics INDEX MASTER^
set statistics INDEX SLAVE_LINK^
set statistics INDEX DOUBLE_SLAVE_REF1^
set statistics INDEX DOUBLE_SLAVE_REF2^


Query
------------------------------------------------
select *
from DOUBLE_SLAVE ds
join MASTER m on ds.LINK = m.ID
join SLAVE s on s.LINK = m.ID
where ds.REF2 = :REF


Plan
------------------------------------------------
PLAN JOIN (DS INDEX (DOUBLE_SLAVE_REF2), M INDEX (MASTER), S INDEX (SLAVE_LINK))

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 13
Marks : 0

Query
------------------------------------------------
select *
from DOUBLE_SLAVE ds
join MASTER m on ds.LINK = m.ID
join SLAVE s on s.LINK = m.ID
where ds.REF1 = :REF


Plan
------------------------------------------------
PLAN JOIN (DS INDEX (DOUBLE_SLAVE_REF1), M INDEX (MASTER), S INDEX (SLAVE_LINK))


Operations
------------------------------------------------
Read : 8
Writes : 0
Fetches: 27
Marks : 0


Enchanced Info:
Table Name Indexed Non-Indexed
DOUBLE_SLAVE 1 0
MASTER 1 0
SLAVE 1 0