description: On the statement

select * from XI_ITEM XI
where XDHID in (select XDHID from XH_HDR XH where XH.XDST = '6');

the execution plan is not as expected.

Expected:
- one time exec of the "in-select" on XH_HDR
- then read all rows of the outer select XI_ITEM
and check to the result set of XH_HDR

In fact,
- it reads all rows of the XI_ITEM
- executes for every row of XI_ITEM the "in-select" on XH_HDR

Since the number of rows XDH : XDI is 1 : 100, it results in a long time response.

Compared to other DBMS like MS-SQL or Gupta's SQL Base, they work as expected, means response time less than one second.

Introduction for reproducing the problem

Two tables in a classic situation, a header and an Item table.

CREATE TABLE XH_HDR
(
XDHID INTEGER NOT NULL,
XDST CHAR( 1) NOT NULL,
PRIMARY KEY (XDHID)
);

CREATE TABLE XI_ITEM
(
XDHID INTEGER NOT NULL,
XITEMNO INTEGER NOT NULL,
XDISZE INTEGER,
PRIMARY KEY (XDHID, XITEMNO)
);

RI is not necessary but for better documentation

ALTER TABLE XI_ITEM ADD
FOREIGN KEY (XDHID) REFERENCES XH_HDR (XDHID)
ON DELETE CASCADE
ON UPDATE NO ACTION;

Inserting 100 rows into XH_HDR
- Ascending numbered XDHID
- 10 Rows of them XDST = '6', Rest any value
=> Total 100 Rows

Inserting for every row of XH_HDR 500 rows into XI_ITEM
- XDHID from header table
- Ascending numbered XITEMNO
=> Total 50'000 Rows

In the Execution plan statistics, I expect now 50'100 accesses (50'000 to XI_ITEM, 100 to XH_HDR), but it shows 50'000 to every of the two tables.

=>

On the statement

select * from XI_ITEM XI
where XDHID in (select XDHID from XH_HDR XH where XH.XDST = '6');

the execution plan is not as expected.

Expected:
- one time exec of the "in-select" on XH_HDR
- then read all rows of the outer select XI_ITEM
and check to the result set of XH_HDR

In fact,
- it reads all rows of the XI_ITEM
- executes for every row of XI_ITEM the "in-select" on XH_HDR

Since the number of rows XDH : XDI is 1 : 100, it results in a long time response.

Compared to other DBMS like MS-SQL or Gupta's SQL Base, they work as expected, means response time less than one second.

Introduction for reproducing the problem

Two tables in a classic situation, a header and an Item table.

CREATE TABLE XH_HDR
(
XDHID INTEGER NOT NULL,
XDST CHAR( 1) NOT NULL,
PRIMARY KEY (XDHID)
);

CREATE TABLE XI_ITEM
(
XDHID INTEGER NOT NULL,
XITEMNO INTEGER NOT NULL,
XDISZE INTEGER,
PRIMARY KEY (XDHID, XITEMNO)
);

RI is not necessary but for better documentation

ALTER TABLE XI_ITEM ADD
FOREIGN KEY (XDHID) REFERENCES XH_HDR (XDHID)
ON DELETE CASCADE
ON UPDATE NO ACTION;

Inserting 100 rows into XH_HDR
- Ascending numbered XDHID
- 10 Rows of them XDST = '6', Rest any value
=> Total 100 Rows

Inserting for every row of XH_HDR 500 rows into XI_ITEM
- XDHID from header table
- Ascending numbered XITEMNO
=> Total 50'000 Rows

In the Execution plan statistics, I expect now 50'010 accesses (50'000 to XI_ITEM, 10 by Index to XH_HDR),
but it shows 50'000 to XI_ITEM and 50'000 by Index to XH_HDR.

Workflow: jira [ 11493 ] => Firebird [ 14851 ]

assignee: Dmitry Yemanov [ dimitr ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Link: This issue is duplicated by CORE3518 [ CORE3518 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 3.0 Beta 1 [ 10332 ] =>

Fix Version: 3.0 Beta 2 [ 10586 ] =>