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 Thu, 07 Jan 2016 05:57:25 GMT
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 <jamestaylor@apache.org> wrote:

> 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