[ https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Wong updated PHOENIX-4845: --------------------------------- Attachment: (was: PHOENIX-4845.patch) > 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-4845-4.x-HBase-1.3.patch, > PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch, > PHOENIX-4845.patch, PHOENIX-offset.txt > > Time Spent: 16h 10m > Remaining Estimate: 0h > > 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 (v8.3.4#803005)