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 17:26:03 GMT
In that case, you'd need PHOENIX-1544 to be implemented.

On Wed, Jan 6, 2016 at 8:52 AM, Kumar Palaniappan <
kpalaniappan@marinsoftware.com> 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 <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