Я опять здесь.
Урожайный у меня день сегодня
И так FB 2.0
есть запрос
select d.id, i.name
from docdet d
left join item i on i.id = d.id_item
where d.ID_DOC = :mas_id and d.i2 is null
Я пользуюсь FIB, довольно старой
версией.
Естественно, установленно Option.poNoForceIsNull
= false
т.е. запрос при MAS_ID = null
преобразовывается к виду
select d.id, i.name
from docdet d
left join item i on i.id = d.id_item
where d.ID_DOC is null and d.i2 is null
PLAN JOIN (D NATURAL, I INDEX (PK_ITEM))
Зачем мне этот парамет нужен, не знаю,
точнее но мне не нужен, но он есть.
если я меняю местами выражения в "where"
select d.id, i.name
from docdet d
left join item i on i.id = d.id_item
where d.i2 is null and d.ID_DOC is null
то получаю совсем другой план
PLAN JOIN (D INDEX (FK_DOCDET), I INDEX (PK_ITEM))
Т.е. от перемены мест слагаемых сумма
меняется?
Свои ошибки уже исправил, но осадок...
Про таблицы
ITEM (150)
Primary pointer page: 195, Index root page: 196
Average record length: 85.77, total records: 8511
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 133, data page slots: 133, average fill: 81%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 4
80 - 99% = 128
Index ITEM_IDX1 (1)
Depth: 2, leaf buckets: 5, nodes: 8511
Average data length: 0.19, total dup: 6992, max dup: 92
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 4
Index ITEM_ITEM (0)
Depth: 2, leaf buckets: 5, nodes: 8511
Average data length: 0.29, total dup: 6926, max dup: 253
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 4
Index PK_ITEM (2)
Depth: 2, leaf buckets: 7, nodes: 8511
Average data length: 1.64, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6
DOCDET (160)
Primary pointer page: 217, Index root page: 218
Average record length: 63.26, total records: 1928535
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 24766, data page slots: 24767, average fill: 77%
Fill distribution:
0 - 19% = 81
20 - 39% = 0
40 - 59% = 2
60 - 79% = 24683
80 - 99% = 0
Index DOCDET_IDX1 (1)
Depth: 3, leaf buckets: 1491, nodes: 1928535
Average data length: 0.44, total dup: 1139222, max dup: 113325
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 421
60 - 79% = 0
80 - 99% = 1070
Index FK_DOCDET (2)
Depth: 3, leaf buckets: 1198, nodes: 1928535
Average data length: 0.03, total dup: 1898510, max dup: 23012
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 3
60 - 79% = 2
80 - 99% = 1192
Index PK_DOCDET (0)
Depth: 3, leaf buckets: 1439, nodes: 1928535
Average data length: 1.03, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 2
60 - 79% = 1
80 - 99% = 1435
CREATE TABLE DOCDET (
ID DID /* DID = INTEGER NOT NULL */,
ID_DOC DID /* DID = INTEGER NOT NULL */,
ID_ITEM DID NOT NULL /* DID = INTEGER NOT NULL */,
ITEM_COUNT DINT /* DINT = INTEGER */,
ITEMS DCOUNT /* DCOUNT = NUMERIC(9,2) */,
PRICE DCURRENCY /* DCURRENCY = NUMERIC(9,2) */,
ITEM_COUNT_ALT DINT /* DINT = INTEGER */,
ITEMS_ALT DCOUNT /* DCOUNT = NUMERIC(9,2) */,
COMMENT DSTRINGN COLLATE WIN1251_UA /* DSTRINGN =
VARCHAR(50) DEFAULT '' */,
ID_BARCODE DINT /* DINT = INTEGER */,
SYSDAY DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT
CURRENT_TIMESTAMP */,
SYSUSER DSYSUSER /* DSYSUSER = VARCHAR(12) DEFAULT
CURRENT_USER */,
SYSREGION DSYSREGION /* DSYSREGION = INTEGER DEFAULT 1 */,
I1 DINT /* DINT = INTEGER */,
I2 DINT /* DINT = INTEGER */,
I3 DINT /* DINT = INTEGER */,
I4 DINT /* DINT = INTEGER */,
D1 DDOUBLE /* DDOUBLE = DOUBLE PRECISION */,
N1 DCURRENCY4 /* DCURRENCY4 = NUMERIC(12,4) */
);
ALTER TABLE DOCDET ADD CONSTRAINT PK_DOCDET PRIMARY KEY (ID);
ALTER TABLE DOCDET ADD CONSTRAINT FK_DOCDET FOREIGN KEY (ID_DOC)
REFERENCES DOC (ID) ON DELETE CASCADE;
CREATE INDEX DOCDET_IDX1 ON DOCDET (ID_BARCODE);
CREATE TABLE ITEM (
ID DID NOT NULL /* DID = INTEGER NOT NULL */,
ID_ITEM DINT default 0 /* DINT = INTEGER */,
ITEM_TYPE DINT /* DINT = INTEGER */,
CODE DINT /* DINT = INTEGER */,
NAME DSTRING /* DSTRING = VARCHAR(84) DEFAULT '' NOT NULL
*/,
PRINTNAME DSTRING84N COLLATE WIN1251_UA /* DSTRING84N =
VARCHAR(84) DEFAULT '' */,
ID_IZM DSHORT /* DSHORT = SMALLINT DEFAULT 1 */,
IZM_VALUE DCOUNT /* DCOUNT = NUMERIC(9,2) */,
INPACK DINT /* DINT = INTEGER */,
VISIBLE DVISIBLE /* DVISIBLE = SMALLINT DEFAULT 1 NOT NULL */,
ORDR DINT /* DINT = INTEGER */,
PRICE DCURRENCY0 /* DCURRENCY0 = NUMERIC(9,2) DEFAULT 0 */,
CODE_ALT DINT /* DINT = INTEGER */,
CODE_MIN DINT /* DINT = INTEGER */,
CODE_MAX DINT /* DINT = INTEGER */,
SYSDAY DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT
CURRENT_TIMESTAMP */,
SYSUSER DSYSUSER /* DSYSUSER = VARCHAR(12) DEFAULT CURRENT_USER
*/,
SYSREGION DSYSREGION /* DSYSREGION = INTEGER DEFAULT 1 */,
PP_COMMENT DSTRING30N COLLATE WIN1251_UA /* DSTRING30N =
VARCHAR(30) DEFAULT '' */,
MAIN_CODE DINT /* DINT = INTEGER */,
VSTAVKA DINT /* DINT = INTEGER */,
ID_COLOR DINT /* DINT = INTEGER */
);
ALTER TABLE ITEM ADD CONSTRAINT PK_ITEM PRIMARY KEY (ID);
CREATE INDEX ITEM_IDX1 ON ITEM (CODE);
CREATE INDEX ITEM_ITEM ON ITEM (ID_ITEM);
