phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From William <yhxx...@163.com>
Subject Join of multi index tables.
Date Wed, 20 Jul 2016 09:03:53 GMT
Hi all,
 I have a question about global  secondary index in Phoenix 4.6.  See the following statements:


create table yy (pk integer primary key, cf.a integer, cf.b integer);
create index yya on yy(cf.a);
create index yyb on yy(cf.b);


then upsert some data into table yy; do the following query:


select pk from yy where cf.a = 1 or cf.b = 2;
select /*+INDEX(yy yya yyb)*/ pk from yy where cf.a = 1 or cf.b = 2;


I expect that both index tables will be used in this query, and join the results from both
index tables and return. 
But unfortunately, no index tables has been used but a full table scan instead. The statement
with a hint have the same behaviour. The explain plan is :


FULL SCAN OVER YY 
    SERVER FILTER BY (CF.A = 1 OR CF.B = 2) 


Another example:


create index yyi on yy (cf.a) include (cf.b);
select pk from yy where cf.a = 1 or cf.b =2;


then this query will hit the index table with a filter. The explain plan:


FULL SCAN OVER YYI 
     SERVER FILTER BY (TO_INTEGER("A") = 1 OR CF."B" = 2) 


my question is : I only have index yya and yyb, and i want the above select statement to hit
both index tables,  do we support this scenario? 
if so, how can i use both indexes?
if not, why? It is too hard to implement ? Is there a plan to support it ?


Thanks
William
Mime
View raw message