New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Select from ... where in (select ...) got unoptimized Execution plan [CORE1086] #1507
Comments
Modified by: ohenri (ohenri)description: On the statement select * from XI_ITEM XI the execution plan is not as expected. Expected: In fact, 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 CREATE TABLE XI_ITEM RI is not necessary but for better documentation ALTER TABLE XI_ITEM ADD Inserting 100 rows into XH_HDR Inserting for every row of XH_HDR 500 rows into XI_ITEM 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 the execution plan is not as expected. Expected: In fact, 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 CREATE TABLE XI_ITEM RI is not necessary but for better documentation ALTER TABLE XI_ITEM ADD Inserting 100 rows into XH_HDR Inserting for every row of XH_HDR 500 rows into XI_ITEM In the Execution plan statistics, I expect now 50'010 accesses (50'000 to XI_ITEM, 10 by Index to XH_HDR), |
Modified by: @pcisarWorkflow: jira [ 11493 ] => Firebird [ 14851 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovFix Version: 3.0 Alpha 1 [ 10331 ] |
Commented by: Smirnoff Serg (wildsery) ohenri, How did you expect 10 reads on table XH_HDR, there's no index on field XDST specified? |
Modified by: @dyemanovFix Version: 3.0 Beta 2 [ 10586 ] => |
Submitted by: ohenri (ohenri)
Is duplicated by CORE3518
Votes: 3
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.
The text was updated successfully, but these errors were encountered: