[
https://issues.apache.org/jira/browse/TRAFODION-2751?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hans Zeller closed TRAFODION-2751.
----------------------------------
> Unnecessary PROBE_CACHE in an index join
> ----------------------------------------
>
> Key: TRAFODION-2751
> URL: https://issues.apache.org/jira/browse/TRAFODION-2751
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Reporter: Hans Zeller
> Assignee: Hans Zeller
> Fix For: 2.3-incubating
>
>
> When we transform a scan into a nested join of an index and its base table,
> this join is on the clustering key columns, which are present and unique in
> both the index and the base table.
> Therefore, this is a 1:1 join with unique matches on each side. There is no
> use in a probe cache, since we will never probe for the same base table key
> twice.
> Right now we see a probe cache, though. There are a few reasons and I'm not
> sure whether this is a regression or an old bug.
> The reason: When we create the index join, in
> IndexJoinRule1::makeSubstituteFromIndexInfo() in file
> core/sql/optimizer/TransRule.cpp, we don't synthesize logical properties for
> the join. This is correct, in principle, because its logical properties are
> the same as those of the scan. However, the synthLogProp() method also sets
> some local variables in the join that are used to determine whether a probe
> cache is useful: leftHasUniqueMatches_ and rightHasUniqueMatches_.
> Here is a test case:
> create table tdi(service_id int not null,
> user_number int not null,
> start_time timestamp not null,
> b int,
> primary key(service_id, user_number, start_time))
> division by (date_trunc('day', start_time))
> salt using 4 partitions on (user_number);
> create index tdix on tdi(user_number, start_time) salt like table;
> control query shape join(cut,cut);
> prepare s from
> select * from tdi where user_number = 1234 and start_time between timestamp
> '2017-01-01 00:00:00' and timestamp '2017-01-15 00:00:00';
> explain options 'f' s;
> The explain shows a PROBE_CACHE, which is unnecessary.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
