Available indices are not used in some cases if ORDER BY expression is a filtered one [CORE5481] #5751
Description
Submitted by: yakovhrebtov (yakovhrebtov)
Is related to CORE5070
Relate to CORE5965
CREATE TABLE ORG_ACCOUNTS
(
ORGACCOUNTID BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE BALANCES
(
BALANCEID BIGINT NOT NULL,
ORGACCOUNTID BIGINT NOT NULL,
BALANCEDATE DATE NOT NULL
);
ALTER TABLE BALANCES ADD CONSTRAINT PK_BALANCES PRIMARY KEY (BALANCEID);
ALTER TABLE BALANCES ADD CONSTRAINT FK_BALANCES_ORGACCOUNTS FOREIGN KEY (ORGACCOUNTID) REFERENCES ORG_ACCOUNTS (ORGACCOUNTID);
ALTER TABLE BALANCES ADD CONSTRAINT BALANCES_BALANCEDATE_ORGACCOUNT UNIQUE (ORGACCOUNTID, BALANCEDATE);
CREATE DESCENDING INDEX BALANCES_BALANCEDATE_DESC ON BALANCES (BALANCEDATE);
select first 1 *
from Balances B
where OrgAccountID=18 and
BalanceDate<='01.01.2017'
order by BalanceDate desc;
v2.5:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (BALANCES_BALANCEDATE_ORGACCOUNT))
v3.0:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC)
Correct (best) plan should be:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (FK_BALANCES_ORGACCOUNTS))
Now the funny thing:
ALTER TABLE BALANCES DROP CONSTRAINT BALANCES_BALANCEDATE_ORGACCOUNT;
v3.0:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (FK_BALANCES_ORGACCOUNTS))
I.e. existing compound index BALANCES_BALANCEDATE_ORGACCOUNT is not only ignored itself (see also CORE5070), but it also hides possibilities to use another index FK_BALANCES_ORGACCOUNTS.