Hi everyone

Had a query running much slower in 2.1.2 than in 2.0.3, below is a prototype of what's happening. Has anyone come across this? Does this (mis)behaviour of optimiser affect other patterns of queries?

/* Metadata */

RECREATE TABLE ALLFILE (
PK_ALLFILE INTEGER NOT NULL PRIMARY KEY,
PATHNAME CHAR(255) NOT NULL,
FILENAME CHAR(255) NOT NULL
);

--

RECREATE TABLE EXCLUDEDFILE (
PK_EXCLUDEDFILE INTEGER NOT NULL PRIMARY KEY,
PATHNAME CHAR(255) NOT NULL,
FILENAME CHAR(255) NOT NULL
);

CREATE INDEX EXCLUDEDFILE_IDX1 ON EXCLUDEDFILE (FILENAME);

/*Data*/

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (1, 'C:\TEMP\', '1.TXT');

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (2, 'C:\TEMP\', '2.TXT');

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (3, 'C:\TEMP\', '3.TXT');

--

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (1, 'C:\ABC\', '*');

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (2, 'C:\CCC\', '*');

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (3, 'C:\TEMP\', 'ABC.TXT');

COMMIT;

Query:

select
AllFile.*
from AllFile
where
allfile.filename = '1.TXT' and
not exists (
select 1
from excludedfile
where
excludedfile.pathname = allfile.pathname and
(excludedfile.filename = allfile.filename or
excludedfile.filename = '*')
)

Both 2.1.2. and 2.0.3 choose this plan:

PLAN (EXCLUDEDFILE INDEX (EXCLUDEDFILE_IDX1, EXCLUDEDFILE_IDX1))
PLAN (ALLFILE NATURAL)

2.0.3 does 3 natural reads on AllFile, and 2 index reads on ExcludedFile, because it applies allfile.filename = '1.TXT' first.

2.1.2 does 3 natural reads on AllFile, but also 6 index reads on ExcludedFile, because it applies allfile.filename = '1.TXT' last.