You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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.
Commits: 1112962 ffff0e8
The text was updated successfully, but these errors were encountered: