phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas D'Silva (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause to support paging in tables where the sort order of PK columns varies
Date Tue, 02 Oct 2018 00:54:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Thomas D'Silva updated PHOENIX-4845:
------------------------------------
    Description: 
RVCs along with the LIMIT clause are useful for efficiently paging through rows (see [http://phoenix.apache.org/paged.html]). 
 However if the sorder order of the PK columns in a table varies we cannot use RVCs. 

For eg. if the PK of a table is (A  DESC, B) we cannot use the following query to page through
the data
{code:java}
SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B 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 LIMIT 20
{code}
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.
{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]

  was:
RVCs along with the LIMIT clause are useful for efficiently paging through rows (see [http://phoenix.apache.org/paged.html]). 
 However if the sorder order of the PK columns in a table varies we cannot use RVCs. 

For eg. if the PK of a table is (A  DESC, B) we cannot use the following query to page through
the data
{code:java}
SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B LIMIT 20
{code}
Since the rows are sorted by A desc and then by B descending we need to use the following
query
{code:java}
SELECT * FROM TABLE WHERE (A < ? OR (A=? AND B>?))  ORDER BY A DESC, B LIMIT 20
{code}
If we supported using RVCs in the offset clause we could use the offset to set the start row
of the scan. Also clients would not have to generate a complicated query.
{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 to support 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
>            Priority: Major
>              Labels: DESC, SFDC
>
> RVCs along with the LIMIT clause are useful for efficiently paging through rows (see
[http://phoenix.apache.org/paged.html]). 
>  However if the sorder order of the PK columns in a table varies we cannot use RVCs. 
> For eg. if the PK of a table is (A  DESC, B) we cannot use the following query to page
through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B 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 LIMIT 20
> {code}
> 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.
> {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]



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message