[Firebird-devel] [FB-Tracker] Created: (CORE-1086) Select from ... where in (select ...) got unoptimized Execution plan

[ohenri (JIRA) <tr...@fi...>]

Select from ... where in (select ...) got unoptimized Execution plan
--------------------------------------------------------------------

                 Key: CORE-1086
                 URL: http://tracker.firebirdsql.org/browse/CORE-1086
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 2.0.0
         Environment: Firebird 2.0 and also Firebird 1.5.3
Windows Server 2003 SP1
            Reporter: ohenri


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.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira