[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16896562#comment-16896562
]
Daniel Wong commented on PHOENIX-4845:
--------------------------------------
I'll try, I could require INDEX or NO INDEX hint relatively easily, which maybe
I should. However I cannot determine all the user defined index PKs given the
current syscat as far as I can tell.
Consider the following code fragment and the table and indexes it creates:
{code:java}
String ddlTemplate = "CREATE TABLE %s (k1 TINYINT NOT NULL,\n" + "k2 TINYINT
NOT NULL,\n" + "k3 TINYINT NOT NULL,\n" + "k4 TINYINT NOT NULL,\n" + "k5
TINYINT NOT NULL,\n" + "k6 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "v2
INTEGER,\n" + "v3 INTEGER,\n" + "v4 INTEGER,\n" + "CONSTRAINT pk PRIMARY KEY
(k1, k2, k3, k4, k5, k6)) ";
String longKeyTableName = "T_" + generateUniqueName(); String longKeyIndex1Name
= "INDEX_1_" + longKeyTableName;
String longKeyIndex2Name = "INDEX_2_" + longKeyTableName;
String ddl = String.format(ddlTemplate,longKeyTableName);
conn.createStatement().execute(ddl);
String createIndex1 = "CREATE INDEX IF NOT EXISTS " + longKeyIndex1Name + " ON
" + longKeyTableName + " (k2 ,v1, k4)";
String createIndex2 = "CREATE INDEX IF NOT EXISTS " + longKeyIndex2Name + " ON
" + longKeyTableName + " (v1, v3)";
{code}
Here the PK columns for the table are PKs = \{K1,K2,K3,K4,K5,K6}
For index 1 it is \{K2, 0:V1, K4, K1, K3, K5, K6} as we append the PKs to the
indexed row key. However I cannot differentiate that PK form a create index
that had for example CREATE INDEX (k2, v1, k4, k1). So how will I be able to
force the user to provide either k1 or not provide k1? I didn't see anything in
SYSCAT or PTable Interface that can allow me to differentiate these scenarios.
I could be missing something however.
> 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)
