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