I had an idea a while back that I’ll share here because it’s relevant. It’s
basically a combined index, or index group, and it would work in this case. It
could be implemented in both global and local indexes. The data for two or more
indexes would be interleaved. For a local index, the
Please file a JIRA for the ability to use multiple tables for an OR query
(but that's a pretty big effort). In the meantime, you may be able to work
around the lack of support for UNION by doing a merge sort yourself on the
client as an efficient way to dedup. For example, if you have a table like
Hi Noam,
You can evaluate below approach
- Create a temp-table with your target-schema for the result-set
- Have indexes created for all your OR clause queries
- Fire one-query at a time (with one OR clause) and dump data to
target-temp table (here, all later queries updates
If you're using local index, then the hint isn't necessary. However, a
covered local index may improve performance (at the expense of extra space)
by preventing a local get to retrieve the data row (when a column not in
the index is referenced).
Phoenix will only use a single index. The only
Hi James,
Thank you for the fast reply.
1. As far as we saw in Phoenix documentation UNION is not supported, only
UNION ALL. Breaking the queries to multiple queries with UNION ALL will return
duplicates. It may be possible to wrap these queries with select distinct, but
it significantly
Hi Noam,
A few ideas:
1) Use a UNION instead of an OR and you may be able to use more than one
index for one query.
2) Include the columns you're referencing in the index to make it a covered
index [1].
3) Hint [2] the query to force the index to be used.
Thanks,
James
[1]
Hi,
We have a case where we have a table with few index on different columns a, b,
c etc' . It works well if we do select with "and" condition (for example
select Where a='xyz' and b='123' )but when we have or condition (for
example select Where a='xyz' or b='123') we get full
