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
When using a the folowing construction i get a bad plan:
SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A WHERE SOLD_CTRY IN
(
SELECT CTRY_OBJID FROM CTRY_GROUP_COLLECTION B WHERE B.CTRY_GROUP_OBJID
IN (SELECT OBJID FROM CTRY_GROUP C WHERE C.GROUPCODE='AMERICAS')
)
PLAN (C INDEX (CTRY_GROUP_PK))
PLAN (B INDEX (CTRY_CODES_FK))
PLAN (A NATURAL)
The same result but with a join uses a more logical plan.
SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A
JOIN CTRY_GROUP_COLLECTION B ON A.SOLD_CTRY=B.CTRY_OBJID
JOIN CTRY_GROUP C ON C.OBJID=B.CTRY_GROUP_OBJID AND C.GROUPCODE='AMERICAS'
PLAN JOIN (C INDEX (IDX_GROUPCODE), B INDEX (CTRY_GROUP_FK), A INDEX (CTRY_CODES_FK2))
this was tested on (2.5.0.22381 ) and 2.1.1
The text was updated successfully, but these errors were encountered:
Well, these two queries are not equivalent (the join one supposedly requires a DISCTINCT term), but I seem getting your point.
This is not a bug but rather a design limitation. The IN subqueries are always being joined to the parent ones, i.e. similar to A LEFT JOIN B LEFT JOIN C which also implies a pre-defined join order from the outer tables to the inner ones.
Submitted by: Derryck welas (welas)
Duplicates CORE1769
Attachments:
TEST.zip
When using a the folowing construction i get a bad plan:
SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A WHERE SOLD_CTRY IN
(
SELECT CTRY_OBJID FROM CTRY_GROUP_COLLECTION B WHERE B.CTRY_GROUP_OBJID
IN (SELECT OBJID FROM CTRY_GROUP C WHERE C.GROUPCODE='AMERICAS')
)
PLAN (C INDEX (CTRY_GROUP_PK))
PLAN (B INDEX (CTRY_CODES_FK))
PLAN (A NATURAL)
The same result but with a join uses a more logical plan.
SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A
JOIN CTRY_GROUP_COLLECTION B ON A.SOLD_CTRY=B.CTRY_OBJID
JOIN CTRY_GROUP C ON C.OBJID=B.CTRY_GROUP_OBJID AND C.GROUPCODE='AMERICAS'
PLAN JOIN (C INDEX (IDX_GROUPCODE), B INDEX (CTRY_GROUP_FK), A INDEX (CTRY_CODES_FK2))
this was tested on (2.5.0.22381 ) and 2.1.1
The text was updated successfully, but these errors were encountered: