[jira] [Comment Edited] (PHOENIX-1560) Join between global index and data table if INDEX hint used

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-1560?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14277396#comment-14277396 ] 

James Taylor edited comment on PHOENIX-1560 at 1/14/15 6:48 PM:
----------------------------------------------------------------

I see your point for the no where clause case. With an order by and no where clause, we'd be choosing between:
- re-ordering the data table versus
- a scan over the index table plus a skip scan per row for the data table

Not sure how to compare those, but I know that the re-order is expensive, as it essentially causes the data table to be re-written. I guess I'd lean toward using the optimization, as that's what the hint is directing us to do. If perf is bad, the user can always remove the hint.


was (Author: jamestaylor):
I see your point for the no where clause case. With an order by and no where clause, we'd be choosing between:
- re-ordering the data table versus
- a scan over the index table plus a skip scan per row for the data table
Not sure how to compare those, but I know that the re-order is expensive, as it essentially causes the data table to be re-written. I guess I'd lean toward using the optimization, as that's what the hint is directing us to do. If perf is bad, the user can always remove the hint.

> Join between global index and data table if INDEX hint used
> -----------------------------------------------------------
>
>                 Key: PHOENIX-1560
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1560
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: James Taylor
>            Assignee: Maryann Xue
>         Attachments: 1560.patch
>
>
> We already have an INDEX hint, and we already have a mechanism to collect referenced columns in the data table that are not in the index table (used only for local indexes currently). Instead of not using the global index when a referenced data column is not found in the index, we should rewrite the query to join back to the data table when the INDEX hint is present. This is always possible, as we always have the data PK columns in the index table, and our join optimization would kick in as well.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)