[
https://issues.apache.org/jira/browse/PHOENIX-7659?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sanjeet Malhotra updated PHOENIX-7659:
--------------------------------------
Description:
Currently, if someone wants to lookup multiple individual rows and WHERE clause
is has conditions on all the PK columns to generate point lookups, then we use
IN list. A typical such query looks like:
{noformat}
SELECT COL1 FROM TABLE WHERE PK1 = VAL1 AND PK2 IN (VAL21, VAL22, ...,
VAL2n){noformat}
As the size of IN list increases so, does the parsing time of the query. But if
we use PK2 = ANY(?) and then assign a SQL array type to the bind variable then
we can cut down on the parsing time and prevent parsing time to grow linearly
with rows being looked up in a query.
The scope of this Jira currently is to target only the literal ARRAY
expressions specified in the query or array specified as bind parameter.
was:
Currently, if someone wants to lookup multiple individual rows and WHERE clause
is has conditions on all the PK columns to generate point lookups, then we use
IN list. A typical such query looks like:
{noformat}
SELECT COL1 FROM TABLE WHERE PK1 = VAL1 AND PK2 IN (VAL21, VAL22, ...,
VAL2n){noformat}
As the size of IN list increases so, does the parsing time of the query. But if
we use PK2 = ANY(?) and then assign a SQL array type to the bind variable then
we can cut down on the parsing time and prevent parsing time to grow linearly
with rows being looked up in a query.
> Leverage = ANY() instead of big IN list to do huge number of point lookups in
> a single query
> --------------------------------------------------------------------------------------------
>
> Key: PHOENIX-7659
> URL: https://issues.apache.org/jira/browse/PHOENIX-7659
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 5.2.2, 5.3
> Reporter: Sanjeet Malhotra
> Assignee: Sanjeet Malhotra
> Priority: Minor
>
> Currently, if someone wants to lookup multiple individual rows and WHERE
> clause is has conditions on all the PK columns to generate point lookups,
> then we use IN list. A typical such query looks like:
> {noformat}
> SELECT COL1 FROM TABLE WHERE PK1 = VAL1 AND PK2 IN (VAL21, VAL22, ...,
> VAL2n){noformat}
> As the size of IN list increases so, does the parsing time of the query. But
> if we use PK2 = ANY(?) and then assign a SQL array type to the bind variable
> then we can cut down on the parsing time and prevent parsing time to grow
> linearly with rows being looked up in a query.
>
> The scope of this Jira currently is to target only the literal ARRAY
> expressions specified in the query or array specified as bind parameter.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
