Thanks James. We will look into it. we need to find a way to overcome the full scan.
On Wed, Jan 6, 2016 at 9:26 AM, James Taylor <[email protected]> wrote: > In that case, you'd need PHOENIX-1544 to be implemented. > > On Wed, Jan 6, 2016 at 8:52 AM, Kumar Palaniappan < > [email protected]> wrote: > >> Unfortunately changing the table is not an option for us at this time. >> >> On Tue, Jan 5, 2016 at 6:27 PM, James Taylor <[email protected]> >> wrote: >> >>> If the "finding customers that have a particular account" is a common >>> query, you might consider modifying your schema by pulling the account into >>> an optional/nullable row key column, like this: >>> >>> CREATE TABLE T (CID VARCHAR NOT NULL, AID BIGINT, V1 VARCHAR, V2 VARCHAR >>> CONSTRAINT pk PRIMARY KEY (CID,AID)); >>> >>> Your non PK columns (V1 and V2 in this example) would only be set on the >>> row where AID is null, but you'd have new rows for all accounts for a given >>> customer, and these rows wouldn't have any other column values. >>> >>> Then you could create a secondary index on AID: >>> CREATE INDEX IDX ON T(AID); >>> >>> and you'd be able to find all customers for a given account quickly. >>> >>> You could still efficiently iterate over the account of a given customer >>> too: >>> SELECT * FROM T WHERE CID=? >>> but your application would need to know that the first row would be the >>> customer row and the next rows would contain only the account IDs for that >>> customer. >>> >>> On Tue, Jan 5, 2016 at 3:46 PM, Kumar Palaniappan < >>> [email protected]> wrote: >>> >>>> Thanks James for the response. Our use case is, that array holds all >>>> the accounts for a particular customer. so the table and query is >>>> >>>> CREATE TABLE T ( ID VARCHAR PRIMARY KEY, A BIGINT ARRAY); >>>> >>>> find by account is a use case - >>>> >>>> select ID from table T where ANY (A); >>>> >>>> On Tue, Jan 5, 2016 at 3:34 PM, James Taylor <[email protected]> >>>> wrote: >>>> >>>>> There is some limited indexing you can do on an array by creating a >>>>> functional index for a particular array element. For example: >>>>> CREATE TABLE T (K VARCHAR PRIMARY KEY, A BIGINT ARRAY); >>>>> CREATE INDEX IDX ON T (A[3]); >>>>> >>>>> In this case, the following query would use the index: >>>>> SELECT K FROM T WHERE A[3] = 5; >>>>> >>>>> Does this help for your usage? >>>>> >>>>> Thanks, >>>>> James >>>>> >>>>> On Tue, Jan 5, 2016 at 2:51 PM, Kumar Palaniappan < >>>>> [email protected]> wrote: >>>>> >>>>>> >>>>>> >>>>>> We have a table with a data type BIGINT[], Since phoenix doesnt >>>>>> support to index this data type, our queries are doing a full table scan >>>>>> when we have to do filtering on this field. >>>>>> >>>>>> What are the alternate approaches? Tried looking into Views, but nope. >>>>>> >>>>>> Appreciate your time. >>>>>> >>>>>> Kumar >>>>>> >>>>> >>>>> >>>> >>> >> >
