Regression: Cardinality is chosen in opposite direction than in FB2.5
---------------------------------------------------------------------
Key: CORE-5025
URL: http://tracker.firebirdsql.org/browse/CORE-5025
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC 1
Reporter: Karol Bieniaszewski
CREATE TABLE TEST1
(
ID INTEGER NOT NULL CONSTRAINT PK_TEST1 PRIMARY KEY
);
CREATE TABLE TEST2
(
ID INTEGER NOT NULL CONSTRAINT PK_TEST2 PRIMARY KEY,
ID_1 INTEGER NOT NULL
);
commit;
SET TERM ^ ;
CREATE PROCEDURE FILL_TEST1(FROM_I INTEGER, TO_I INTEGER)
AS
DECLARE VARIABLE VAR_I INTEGER;
BEGIN
VAR_I = :FROM_I;
WHILE (VAR_I<=TO_I) DO
BEGIN
INSERT INTO TEST1(ID) VALUES(:VAR_I);
VAR_I = VAR_I + 1;
END
END^
SET TERM ; ^
SET TERM ^ ;
CREATE PROCEDURE FILL_TEST2(FROM_I INTEGER, TO_I INTEGER, FROM_I1 INTEGER,
TO_I1 INTEGER)
AS
DECLARE VARIABLE VAR_I INTEGER;
DECLARE VARIABLE VAR_J INTEGER;
DECLARE VARIABLE VAR_ILE INTEGER;
DECLARE VARIABLE VAR_LOS INTEGER;
DECLARE VARIABLE VAR_ID INTEGER;
BEGIN
VAR_I = :FROM_I;
VAR_ID = 1;
WHILE (VAR_I<=TO_I) DO
BEGIN
VAR_ILE = TRUNC(RAND()*30);
VAR_J = 1;
WHILE (VAR_J<=VAR_ILE) DO
BEGIN
VAR_LOS = TRUNC((RAND()*(TO_I1-FROM_I1))+FROM_I1);
INSERT INTO TEST2(ID, ID_1) VALUES(:VAR_ID, :VAR_LOS);
VAR_J = VAR_J + 1;
VAR_ID = VAR_ID + 1;
END
VAR_I = VAR_I + 1;
END
END^
SET TERM ; ^
commit;
ALTER TABLE TEST2 ADD CONSTRAINT FK_TEST2__TEST1 FOREIGN KEY(ID_1) REFERENCES
TEST1(ID) ON DELETE CASCADE ON UPDATE CASCADE;
commit;
execute procedure FILL_TEST1(1, 1000);
commit;
execute procedure FILL_TEST2(1, 10000, 1, 1000);
commit;
/* SET STATISTICS keys PK_TEST1, PK_TEST2, FK_TEST2__TEST1... */
SET STATISTICS INDEX PK_TEST1;
SET STATISTICS INDEX FK_TEST2__TEST1;
SET STATISTICS INDEX PK_TEST2;
commit;
SELECT
*
FROM
TEST1 T1
INNER JOIN TEST2 T2 ON T2.ID_1=T1.ID
PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TEST2__TEST1))
faster is travelsal throught bigger set and access lower set by index then in
opposite direction (natural scan lower set and access bigger set by index)
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
