phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kumar Palaniappan <kpalaniap...@marinsoftware.com>
Subject Re: array of BIGINT index
Date Wed, 06 Jan 2016 16:52:29 GMT
Unfortunately changing the table is not an option for us at this time.

On Tue, Jan 5, 2016 at 6:27 PM, James Taylor <jamestaylor@apache.org> 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 <
> 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