phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: array of BIGINT index
Date Wed, 06 Jan 2016 02:27:36 GMT
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 <
kpalaniappan@marinsoftware.com> 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 <jamestaylor@apache.org>
> 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 <
>> kpalaniappan@marinsoftware.com> 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
>>>
>>
>>
>

Mime
View raw message