We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Submitted by: @livius2
Relate to CORE5965 Duplicates CORE5795
CREATE TABLE TEST ( ID1 INTEGER, ID2 INTEGER, ID3 INTEGER, X NUMERIC(18,2), CONSTRAINT PK_TEST PRIMARY KEY(ID1, ID2, ID3) );
CREATE INDEX IXA_TEST__X ON TEST(X); CREATE INDEX IXA_TEST__ID1_X ON TEST(ID1, X);
--------------------------------------------------------------------------------
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0
PLAN (T INDEX (IXA_TEST__ID1_X))
index IXA_TEST__ID1_X is used --------------------------------------------------------------------------------
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1, T.ID2, T.ID3
PLAN (T ORDER PK_TEST INDEX (IXA_TEST__X))
index IXA_TEST__X - suboptimal --------------------------------------------------------------------------------
as you can see adding ORDER BY which consume some index (PK_TEST) cause suboptimal choice of index (IXA_TEST__X)
if query is changed to order by not by index SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1+0, T.ID2, T.ID3
PLAN SORT (T INDEX (IXA_TEST__ID1_X))
It can be releated to CORE5795 but this can be different case
Commits: 077a2a3 3103f72
The text was updated successfully, but these errors were encountered:
assignee: Dmitry Yemanov [ dimitr ]
Sorry, something went wrong.
Link: This issue relate to CORE5965 [ CORE5965 ]
status: Open [ 1 ] => Resolved [ 5 ]
resolution: Fixed [ 1 ]
Fix Version: 4.0 Beta 1 [ 10750 ]
Fix Version: 3.0.5 [ 10885 ]
status: Resolved [ 5 ] => Resolved [ 5 ]
QA Status: No test => Done successfully
status: Resolved [ 5 ] => Closed [ 6 ]
Link: This issue duplicates CORE5795 [ CORE5795 ]
dyemanov
No branches or pull requests
Submitted by: @livius2
Relate to CORE5965
Duplicates CORE5795
CREATE TABLE TEST
(
ID1 INTEGER,
ID2 INTEGER,
ID3 INTEGER,
X NUMERIC(18,2),
CONSTRAINT PK_TEST PRIMARY KEY(ID1, ID2, ID3)
);
CREATE INDEX IXA_TEST__X ON TEST(X);
CREATE INDEX IXA_TEST__ID1_X ON TEST(ID1, X);
--------------------------------------------------------------------------------
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0
PLAN (T INDEX (IXA_TEST__ID1_X))
index IXA_TEST__ID1_X is used
--------------------------------------------------------------------------------
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1, T.ID2, T.ID3
PLAN (T ORDER PK_TEST INDEX (IXA_TEST__X))
index IXA_TEST__X - suboptimal
--------------------------------------------------------------------------------
as you can see adding ORDER BY which consume some index (PK_TEST)
cause suboptimal choice of index (IXA_TEST__X)
--------------------------------------------------------------------------------
if query is changed to order by not by index
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1+0, T.ID2, T.ID3
PLAN SORT (T INDEX (IXA_TEST__ID1_X))
It can be releated to CORE5795 but this can be different case
Commits: 077a2a3 3103f72
The text was updated successfully, but these errors were encountered: