[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Daniel Wong updated PHOENIX-4845:
---------------------------------
Description:
RVCs along with the LIMIT clause are useful for efficiently paging through rows
(see [http://phoenix.apache.org/paged.html]). This works well if the pk columns
are sorted ascending, we can always use the > operator to query for the next
batch of row.
However if the PK of a table is (A DESC, B DESC) we cannot use the following
query to page through the data
{code:java}
SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
{code}
Since the rows are sorted by A desc and then by B descending we need change the
comparison order
{code:java}
SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
{code}
If the PK of a table contains columns with mixed sort order for eg (A DESC, B)
then we cannot use RVC to page through data.
If we supported using RVCs in the offset clause we could use the offset to set
the start row of the scan. Clients would not have to have logic to determine
the comparison operator. This would also support paging through data for tables
where the PK columns are sorted in mixed order.
{code:java}
SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
{code}
We would only allow using the offset if the rows are ordered by the sort order
of the PK columns of and Index or Primary Table.
Note that there is some care is needed in the use of OFFSET with indexes. If
the OFFSET is coercible to multiple indexes/base table it could mean very
different positions based on key. To Handle This the INDEX hint needs to be
used to specify an index offset for safety.
was:
RVCs along with the LIMIT clause are useful for efficiently paging through rows
(see [http://phoenix.apache.org/paged.html]). This works well if the pk columns
are sorted ascending, we can always use the > operator to query for the next
batch of row.
However if the PK of a table is (A DESC, B DESC) we cannot use the following
query to page through the data
{code:java}
SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
{code}
Since the rows are sorted by A desc and then by B descending we need change the
comparison order
{code:java}
SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
{code}
If the PK of a table contains columns with mixed sort order for eg (A DESC, B)
then we cannot use RVC to page through data.
If we supported using RVCs in the offset clause we could use the offset to set
the start row of the scan. Clients would not have to have logic to determine
the comparison operator. This would also support paging through data for tables
where the PK columns are sorted in mixed order.
{code:java}
SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
{code}
We would only allow using the offset if the rows are ordered by the sort order
of the PK columns.
FYI [~jfernando_sfdc]
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
> --------------------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
> Reporter: Thomas D'Silva
> Assignee: Daniel Wong
> Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
