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 the records if they are
already present)
- Read data from target-temp table (after firing all queries) , and drop
temp-table
though this approach results in firing more queries, it can complete in
less time than firing queries without using index
On Wed, Sep 20, 2017 at 9:31 PM, James Taylor <jamestay...@apache.org>
wrote:
> 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 exception is with UNION
> ALL.
>
> Thanks,
> James
>
> On Tue, Sep 19, 2017 at 12:27 PM Sapir, Yoav <yoav.sa...@teoco.com> wrote:
>
>> 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 complicates the queries and will have performance impact.
>> 2. In case of select …. Where a='xyz' or b='123'
>> I don't see how adding column b as covered column to index on column a
>> will help for finding a row such as the row (a: 'abc', b: '123')
>> It will help only for a row such as (a: 'xyz', b: '123')
>> 3. Hint on global index works for a single index. Is there a way to use
>> multiple indexes? Hint on using multiple indexes?
>> Hint on local index cause an error of unknown field if the where clause
>> refer to a field in the index and to another field that is not part of the
>> specific local index. There is an open bug on it.
>>
>> BR,
>>
>> Yoav Sapir
>>
>>
>> On 19 Sep 2017, at 18:21, James Taylor <jamestay...@apache.org> wrote:
>>
>> 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] http://phoenix.apache.org/secondary_indexing.html#Index_Usage (#1)
>> [2] http://phoenix.apache.org/secondary_indexing.html#Index_Usage (#2)
>>
>> On Tue, Sep 19, 2017 at 4:21 AM Bulvik, Noam <noam.bul...@teoco.com>
>> wrote:
>>
>>> 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 scan even though we have index on a and on b.
>>>
>>>
>>>
>>> Is there a way to get this query to use indexes and not full scan beside
>>> creating index on all available column combination (index on a+b , index on
>>> a +c …)
>>>
>>>
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> *Noam *
>>>
>>>
>>>
>>> ------------------------------
>>>
>>> PRIVILEGED AND CONFIDENTIAL
>>> PLEASE NOTE: The information contained in this message is privileged and
>>> confidential, and is intended only for the use of the individual to whom it
>>> is addressed and others who have been specifically authorized to receive
>>> it. If you are not the intended recipient, you are hereby notified that any
>>> dissemination, distribution or copying of this communication is strictly
>>> prohibited. If you have received this communication in error, or if any
>>> problems occur with transmission, please contact sender. Thank you.
>>>
>>
>> ------------------------------
>>
>> PRIVILEGED AND CONFIDENTIAL
>> PLEASE NOTE: The information contained in this message is privileged and
>> confidential, and is intended only for the use of the individual to whom it
>> is addressed and others who have been specifically authorized to receive
>> it. If you are not the intended recipient, you are hereby notified that any
>> dissemination, distribution or copying of this communication is strictly
>> prohibited. If you have received this communication in error, or if any
>> problems occur with transmission, please contact sender. Thank you.
>>
>
--
*Venkata Subbarayudu Amanchi.*
