Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Phoenix secondary index not working for OR condition

Phoenix secondary index not working for OR condition

I have a table named Accounts as shown below in phoenix

AccountIdNameBranchTransactiondate
123Rajeshabc20170413
124Sachindef20170112
125Rajeshmno20170314
126Bahubalimno20170314
127Sachinpqr20170202

AccountId is the primary key

An index is created as

  • CREATE INDEX accountidx ON Accounts(Name,Transactiondate) INCLUDE (Branch)

The following query hits the index and index is used

select AccountId,Name,Transactiondate from Accounts where Name = 'Rajesh' AND Branch = 'abc'

whereas the below query doesn't make use of the index

select AccountId,Name,Transactiondate from Accounts where Name = 'Rajesh' OR Branch = 'abc'

Why is it so, AND works whereas OR doesn't work.

Any thoughts would be great

1 REPLY 1

Re: Phoenix secondary index not working for OR condition

Your OR condition include a filter on non-primary key, so Phoenix has to read the full table anyways,

There are two ways (you can try):-

* you may try adding an index hint in the query( if you want your particular index to be used)

* If you have another index on Branch (leading in primary key) as well, then you can use UNION of two queries to get result fast(select AccountId,Name,Transactiondate from Accounts where Name = 'Rajesh' UNION select AccountId,Name,Transactiondate from Accounts where Branch = 'abc')

Don't have an account?
Coming from Hortonworks? Activate your account here