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 Thu, 07 Jan 2016 07:23:58 GMT
Sounds good, Kumar. I commented here[1] on PHOENIX-544 to help get you
started.

Thanks,
James

[1]
https://issues.apache.org/jira/browse/PHOENIX-1544?focusedCommentId=15086973&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-15086973

On Wed, Jan 6, 2016 at 9:57 PM, Kumar Palaniappan <
kpalaniappan@marinsoftware.com> wrote:

> 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