[
https://issues.apache.org/jira/browse/IGNITE-13021?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Roman Kondakov updated IGNITE-13021:
------------------------------------
Description:
Currently a full table scan will be executed in the case of disjunctive
predicate even if predicate fields are indexed. For example:
{code:java}
SELECT * FROM emps WHERE name='A' OR surname='B'
{code}
This is caused by the nature of indexes: they can return cursor bounded by
lower and upper bounds. We can cope with it by implementing a logical rule for
rewriting {{OR}} query to a {{UNION ALL}} query:
{code:java}
SELECT * FROM emps WHERE name='A'
UNION ALL
SELECT * FROM emps WHERE surname='B' AND LNNVL(name='A')
{code}
where {{LNNVL()}} function has semantics
{code:java}
LNNVL(name='A') == name!='A' OR name=NULL.
{code}
It is used to avoid expensive deduplication. This name is taken from Oracle, we
can think of more meaningful name, or find the analog in Calcite or H2.
See, for example, this blog post:
[https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion]
for details.
Also it is needed to check this works for {{IN}} clause with small number of
literals (AFAIK Calcite converts large {{IN}} clauses to a join with {{Values}}
table where N > 20).
was:
Currently a full table scan will be executed in the case of disjunctive
predicate even if predicate fields are indexed. For example:
{code:java}
SELECT * FROM emps WHERE name='A' OR surname='B'
{code}
This is caused by the nature of indexes: they can return cursor bounded by
lower and upper bounds. We can cope with it by implementing a logical rule for
rewriting {{OR}} query to a {{UNION ALL}} query:
{code:java}
SELECT * FROM emps WHERE name='A'
UNION ALL
SELECT * FROM emps WHERE surname='B' AND LNNVL(name='A')
{code}
where {{LNNVL()}} function has semantics
{code:java}
LNNVL(name='A') == name!='A' OR name=NULL.
{code}
It is used to avoid expensive deduplication. This name is taken from Oracle, we
can think of more meaningful name, or find the analog in Calcite or H2.
See, for example, this blog post:
[https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion]
for details.
> Calcite integration. Avoid full scans for disjunctive queries.
> --------------------------------------------------------------
>
> Key: IGNITE-13021
> URL: https://issues.apache.org/jira/browse/IGNITE-13021
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Roman Kondakov
> Priority: Major
>
> Currently a full table scan will be executed in the case of disjunctive
> predicate even if predicate fields are indexed. For example:
> {code:java}
> SELECT * FROM emps WHERE name='A' OR surname='B'
> {code}
> This is caused by the nature of indexes: they can return cursor bounded by
> lower and upper bounds. We can cope with it by implementing a logical rule
> for rewriting {{OR}} query to a {{UNION ALL}} query:
> {code:java}
> SELECT * FROM emps WHERE name='A'
> UNION ALL
> SELECT * FROM emps WHERE surname='B' AND LNNVL(name='A')
> {code}
> where {{LNNVL()}} function has semantics
> {code:java}
> LNNVL(name='A') == name!='A' OR name=NULL.
> {code}
> It is used to avoid expensive deduplication. This name is taken from Oracle,
> we can think of more meaningful name, or find the analog in Calcite or H2.
> See, for example, this blog post:
> [https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion]
> for details.
> Also it is needed to check this works for {{IN}} clause with small number of
> literals (AFAIK Calcite converts large {{IN}} clauses to a join with
> {{Values}} table where N > 20).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
