Re: Is there any way to using appropriate index automatically?

2019-08-29 Thread you Zhuang
Thanks, I will get a try

> On Aug 21, 2019, at 5:24 AM, Vincent Poon  wrote:
> 
> check out PHOENIX-5109 , it likely fixes your issue.
> Unfortunately it's targeted for 4.15.0 which hasn't been released yet.  Maybe 
> you can backport and see if it works for your query.
> 
> On Tue, Aug 20, 2019 at 11:38 AM Ankit Singhal  > wrote:
> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h)   ASYNC;
> (Has been filled data with bulkload and index is active)
> 
> Query:
> select /*+ INDEX(test_phoenix.app local_c_h_index) */  * from 
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> select  * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> 
> The first query will use index local_c_h_index and result shortly, the second 
> query won’t , and response slowly.
> Yeah, the local index should be used in both the cases, looks like a bug to 
> me, can you please raise a JIRA in Phoenix project for the same. 
> QueryOptimizer.java may have a relevant code to fix the issue, so the patch 
> would really be appreciated.
> 
> And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from 
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting 
> used.
> 
> Regards,
> Ankit Singhal
> 
> On Tue, Aug 20, 2019 at 1:49 AM you Zhuang  > wrote:
> Er, I also read the sentence “Unlike global indexes, local indexes will use 
> an index even when all columns referenced in the query are not contained in 
> the index. This is done by default for local indexes because we know that the 
> table and index data co-reside on the same region server thus ensuring the 
> lookup is local.”
> 
> I ‘m totally confused.
> 
> 
>> On Aug 20, 2019, at 12:32 AM, Josh Elser > > wrote:
>> 
>> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used 
>> 
>> 
>> On 8/19/19 6:06 AM, you Zhuang wrote:
>>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>>> hbase-version: 1.4.6
>>> Table:
>>> CREATE TABLE test_phoenix.app (
>>> dt integer not null,
>>> a bigint not null ,
>>> b bigint not null ,
>>> c bigint not null ,
>>> d bigint not null ,
>>> e bigint not null ,
>>> f bigint not null ,
>>> g bigint not null ,
>>> h bigint not null ,
>>> i bigint not null ,
>>> j bigint not null ,
>>> k bigint not null ,
>>> m decimal(30,6) ,
>>> n decimal(30,6)
>>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>>> Index:
>>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>>> (Has been filled data with bulkload and index is active)
>>> Query:
>>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
>>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> The first query will use index local_c_h_index and result shortly, the 
>>> second query won’t , and response slowly.
>>> The explain plan is weird, all showing without using index.
 On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka >>>  >> wrote:
 
 We have no problems with that. I mean indexes are used even without hints, 
 if they're suitable for a query.
 Maybe you can share your Phoenix version, query, index definition and exec 
 plan ?
 
 On Mon, Aug 19, 2019 at 12:46 PM you Zhuang >>>  >> wrote:
 
Yeah, I mean no hint , use appropriate index automatically. I
create a local index  and a query with corresponding index column
filter in where clause. But the query doesn’t use index, with
index hint it uses it.
 
 
 
 -- 
Aleksandr Saraseka
 DBA
 380997600401
 > *•* asaras...@eztexting.com 
  > *•* eztexting.com 
 >
  
 
 >
  
 >
  
 

Re: Is there any way to using appropriate index automatically?

2019-08-29 Thread you Zhuang
No, the index can’t be used .

> On Aug 21, 2019, at 2:38 AM, Ankit Singhal  > wrote:
> 
> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h)   ASYNC;
> (Has been filled data with bulkload and index is active)
> 
> Query:
> select /*+ INDEX(test_phoenix.app local_c_h_index) */  * from 
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> select  * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> 
> The first query will use index local_c_h_index and result shortly, the second 
> query won’t , and response slowly.
> Yeah, the local index should be used in both the cases, looks like a bug to 
> me, can you please raise a JIRA in Phoenix project for the same. 
> QueryOptimizer.java may have a relevant code to fix the issue, so the patch 
> would really be appreciated.
> 
> And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from 
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting 
> used.
> 
> Regards,
> Ankit Singhal
> 
> On Tue, Aug 20, 2019 at 1:49 AM you Zhuang  > wrote:
> Er, I also read the sentence “Unlike global indexes, local indexes will use 
> an index even when all columns referenced in the query are not contained in 
> the index. This is done by default for local indexes because we know that the 
> table and index data co-reside on the same region server thus ensuring the 
> lookup is local.”
> 
> I ‘m totally confused.
> 
> 
>> On Aug 20, 2019, at 12:32 AM, Josh Elser > > wrote:
>> 
>> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used 
>> 
>> 
>> On 8/19/19 6:06 AM, you Zhuang wrote:
>>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>>> hbase-version: 1.4.6
>>> Table:
>>> CREATE TABLE test_phoenix.app (
>>> dt integer not null,
>>> a bigint not null ,
>>> b bigint not null ,
>>> c bigint not null ,
>>> d bigint not null ,
>>> e bigint not null ,
>>> f bigint not null ,
>>> g bigint not null ,
>>> h bigint not null ,
>>> i bigint not null ,
>>> j bigint not null ,
>>> k bigint not null ,
>>> m decimal(30,6) ,
>>> n decimal(30,6)
>>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>>> Index:
>>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>>> (Has been filled data with bulkload and index is active)
>>> Query:
>>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
>>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> The first query will use index local_c_h_index and result shortly, the 
>>> second query won’t , and response slowly.
>>> The explain plan is weird, all showing without using index.
 On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka >>>  >> wrote:
 
 We have no problems with that. I mean indexes are used even without hints, 
 if they're suitable for a query.
 Maybe you can share your Phoenix version, query, index definition and exec 
 plan ?
 
 On Mon, Aug 19, 2019 at 12:46 PM you Zhuang >>>  >> wrote:
 
Yeah, I mean no hint , use appropriate index automatically. I
create a local index  and a query with corresponding index column
filter in where clause. But the query doesn’t use index, with
index hint it uses it.
 
 
 
 -- 
Aleksandr Saraseka
 DBA
 380997600401
 > *•* asaras...@eztexting.com 
  > *•* eztexting.com 
 >
  
 
 >
  
 >
  
 >
  
 

Re: Is there any way to using appropriate index automatically?

2019-08-22 Thread Josh Elser
Sorry 'bout that. Missed that you were doing a local index. Thanks for 
catching my slack, Ankit and Vincent.


On 8/20/19 4:49 AM, you Zhuang wrote:
Er, I also read the sentence “Unlike global indexes, local indexes 
/will/ use an index even when all columns referenced in the query are 
not contained in the index. This is done by default for local indexes 
because we know that the table and index data co-reside on the same 
region server thus ensuring the lookup is local.”


I ‘m totally confused.


On Aug 20, 2019, at 12:32 AM, Josh Elser > wrote:


http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used

On 8/19/19 6:06 AM, you Zhuang wrote:

Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
hbase-version: 1.4.6
Table:
CREATE TABLE test_phoenix.app (
dt integer not null,
a bigint not null ,
b bigint not null ,
c bigint not null ,
d bigint not null ,
e bigint not null ,
f bigint not null ,
g bigint not null ,
h bigint not null ,
i bigint not null ,
j bigint not null ,
k bigint not null ,
m decimal(30,6) ,
n decimal(30,6)
CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
Index:
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
(Has been filled data with bulkload and index is active)
Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
The first query will use index local_c_h_index and result shortly, 
the second query won’t , and response slowly.

The explain plan is weird, all showing without using index.
On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka 
> wrote:


We have no problems with that. I mean indexes are used even without 
hints, if they're suitable for a query.
Maybe you can share your Phoenix version, query, index definition 
and exec plan ?


On Mon, Aug 19, 2019 at 12:46 PM you Zhuang 
> wrote:


   Yeah, I mean no hint , use appropriate index automatically. I
   create a local index  and a query with corresponding index column
   filter in where clause. But the query doesn’t use index, with
   index hint it uses it.



--
Aleksandr Saraseka
DBA
380997600401
 *•*asaras...@eztexting.com 
 
*•*eztexting.com 



 
 
 
 
 
 





Re: Is there any way to using appropriate index automatically?

2019-08-20 Thread Vincent Poon
check out PHOENIX-5109 , it likely fixes your issue.
Unfortunately it's targeted for 4.15.0 which hasn't been released yet.
Maybe you can backport and see if it works for your query.

On Tue, Aug 20, 2019 at 11:38 AM Ankit Singhal 
wrote:

> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
> (Has been filled data with bulkload and index is active)
>
> Query:
> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>
> The first query will use index local_c_h_index and result shortly, the
> second query won’t , and response slowly.
>
> Yeah, the local index should be used in both the cases, looks like a bug
> to me, can you please raise a JIRA in Phoenix project for the same.
> QueryOptimizer.java may have a relevant code to fix the issue, so the patch
> would really be appreciated.
>
> And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting
> used.
>
> Regards,
> Ankit Singhal
>
> On Tue, Aug 20, 2019 at 1:49 AM you Zhuang 
> wrote:
>
>> Er, I also read the sentence “Unlike global indexes, local indexes *will* use
>> an index even when all columns referenced in the query are not contained in
>> the index. This is done by default for local indexes because we know that
>> the table and index data co-reside on the same region server thus ensuring
>> the lookup is local.”
>>
>> I ‘m totally confused.
>>
>>
>> On Aug 20, 2019, at 12:32 AM, Josh Elser  wrote:
>>
>> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used
>>
>> On 8/19/19 6:06 AM, you Zhuang wrote:
>>
>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>> hbase-version: 1.4.6
>> Table:
>> CREATE TABLE test_phoenix.app (
>> dt integer not null,
>> a bigint not null ,
>> b bigint not null ,
>> c bigint not null ,
>> d bigint not null ,
>> e bigint not null ,
>> f bigint not null ,
>> g bigint not null ,
>> h bigint not null ,
>> i bigint not null ,
>> j bigint not null ,
>> k bigint not null ,
>> m decimal(30,6) ,
>> n decimal(30,6)
>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>> Index:
>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>> (Has been filled data with bulkload and index is active)
>> Query:
>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> The first query will use index local_c_h_index and result shortly, the
>> second query won’t , and response slowly.
>> The explain plan is weird, all showing without using index.
>>
>> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka > > wrote:
>>
>> We have no problems with that. I mean indexes are used even without
>> hints, if they're suitable for a query.
>> Maybe you can share your Phoenix version, query, index definition and
>> exec plan ?
>>
>> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang > mailto:zhuangzixiao...@gmail.com >> wrote:
>>
>>Yeah, I mean no hint , use appropriate index automatically. I
>>create a local index  and a query with corresponding index column
>>filter in where clause. But the query doesn’t use index, with
>>index hint it uses it.
>>
>>
>>
>> --
>> Aleksandr Saraseka
>> DBA
>> 380997600401
>> > *•* asaras...@eztexting.com <
>> mailto:asaras...@eztexting.com > *•*
>> eztexting.com<
>> http://eztexting.com/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature
>> >
>>
>> <
>> http://facebook.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> http://linkedin.com/company/eztexting/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> http://twitter.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> https://www.youtube.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> https://www.instagram.com/ez_texting/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> https://www.facebook.com/alex.saraseka?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
>> <
>> https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature
>> >
>>
>>
>>


Re: Is there any way to using appropriate index automatically?

2019-08-20 Thread Ankit Singhal
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
(Has been filled data with bulkload and index is active)

Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

The first query will use index local_c_h_index and result shortly, the
second query won’t , and response slowly.

Yeah, the local index should be used in both the cases, looks like a bug to
me, can you please raise a JIRA in Phoenix project for the same.
QueryOptimizer.java may have a relevant code to fix the issue, so the patch
would really be appreciated.

And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from
TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting
used.

Regards,
Ankit Singhal

On Tue, Aug 20, 2019 at 1:49 AM you Zhuang 
wrote:

> Er, I also read the sentence “Unlike global indexes, local indexes *will* use
> an index even when all columns referenced in the query are not contained in
> the index. This is done by default for local indexes because we know that
> the table and index data co-reside on the same region server thus ensuring
> the lookup is local.”
>
> I ‘m totally confused.
>
>
> On Aug 20, 2019, at 12:32 AM, Josh Elser  wrote:
>
> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used
>
> On 8/19/19 6:06 AM, you Zhuang wrote:
>
> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
> hbase-version: 1.4.6
> Table:
> CREATE TABLE test_phoenix.app (
> dt integer not null,
> a bigint not null ,
> b bigint not null ,
> c bigint not null ,
> d bigint not null ,
> e bigint not null ,
> f bigint not null ,
> g bigint not null ,
> h bigint not null ,
> i bigint not null ,
> j bigint not null ,
> k bigint not null ,
> m decimal(30,6) ,
> n decimal(30,6)
> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
> Index:
> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
> (Has been filled data with bulkload and index is active)
> Query:
> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> The first query will use index local_c_h_index and result shortly, the
> second query won’t , and response slowly.
> The explain plan is weird, all showing without using index.
>
> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka  mailto:asaras...@eztexting.com >> wrote:
>
> We have no problems with that. I mean indexes are used even without hints,
> if they're suitable for a query.
> Maybe you can share your Phoenix version, query, index definition and exec
> plan ?
>
> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang  mailto:zhuangzixiao...@gmail.com >> wrote:
>
>Yeah, I mean no hint , use appropriate index automatically. I
>create a local index  and a query with corresponding index column
>filter in where clause. But the query doesn’t use index, with
>index hint it uses it.
>
>
>
> --
> Aleksandr Saraseka
> DBA
> 380997600401
> > *•* asaras...@eztexting.com <
> mailto:asaras...@eztexting.com > *•*
> eztexting.com<
> http://eztexting.com/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature
> >
>
> <
> http://facebook.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> http://linkedin.com/company/eztexting/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> http://twitter.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> https://www.youtube.com/eztexting?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> https://www.instagram.com/ez_texting/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> https://www.facebook.com/alex.saraseka?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature>
> <
> https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp_medium=email_term=_content=_campaign=signature
> >
>
>
>


Re: Is there any way to using appropriate index automatically?

2019-08-20 Thread you Zhuang
Er, I also read the sentence “Unlike global indexes, local indexes will use an 
index even when all columns referenced in the query are not contained in the 
index. This is done by default for local indexes because we know that the table 
and index data co-reside on the same region server thus ensuring the lookup is 
local.”

I ‘m totally confused.


> On Aug 20, 2019, at 12:32 AM, Josh Elser  wrote:
> 
> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used 
> 
> 
> On 8/19/19 6:06 AM, you Zhuang wrote:
>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>> hbase-version: 1.4.6
>> Table:
>> CREATE TABLE test_phoenix.app (
>> dt integer not null,
>> a bigint not null ,
>> b bigint not null ,
>> c bigint not null ,
>> d bigint not null ,
>> e bigint not null ,
>> f bigint not null ,
>> g bigint not null ,
>> h bigint not null ,
>> i bigint not null ,
>> j bigint not null ,
>> k bigint not null ,
>> m decimal(30,6) ,
>> n decimal(30,6)
>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>> Index:
>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>> (Has been filled data with bulkload and index is active)
>> Query:
>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> The first query will use index local_c_h_index and result shortly, the 
>> second query won’t , and response slowly.
>> The explain plan is weird, all showing without using index.
>>> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka >>  >> >> wrote:
>>> 
>>> We have no problems with that. I mean indexes are used even without hints, 
>>> if they're suitable for a query.
>>> Maybe you can share your Phoenix version, query, index definition and exec 
>>> plan ?
>>> 
>>> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang >>  >> >> wrote:
>>> 
>>>Yeah, I mean no hint , use appropriate index automatically. I
>>>create a local index  and a query with corresponding index column
>>>filter in where clause. But the query doesn’t use index, with
>>>index hint it uses it.
>>> 
>>> 
>>> 
>>> -- 
>>> Aleksandr Saraseka
>>> DBA
>>> 380997600401
>>> > *•* asaras...@eztexting.com 
>>>  >> > *•* eztexting.com 
>>> >>  
>>> >
>>>  
>>> 
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >



Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
You are right, thanks so much, but it’s so limited that I can’t include all 
columns due to hundreds of columns existing in one table.

I think covered columns must be queried in global indexes is reasonable, local 
index isn’t.

Because we query rowkey from local index first , then get actual row from data 
table. 

Thus we have no necessity to limit local index usage including all queried 
columns.  



> On Aug 20, 2019, at 12:32 AM, Josh Elser  wrote:
> 
> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used 
> 
> 
> On 8/19/19 6:06 AM, you Zhuang wrote:
>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>> hbase-version: 1.4.6
>> Table:
>> CREATE TABLE test_phoenix.app (
>> dt integer not null,
>> a bigint not null ,
>> b bigint not null ,
>> c bigint not null ,
>> d bigint not null ,
>> e bigint not null ,
>> f bigint not null ,
>> g bigint not null ,
>> h bigint not null ,
>> i bigint not null ,
>> j bigint not null ,
>> k bigint not null ,
>> m decimal(30,6) ,
>> n decimal(30,6)
>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>> Index:
>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>> (Has been filled data with bulkload and index is active)
>> Query:
>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> The first query will use index local_c_h_index and result shortly, the 
>> second query won’t , and response slowly.
>> The explain plan is weird, all showing without using index.
>>> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka >>  >> >> wrote:
>>> 
>>> We have no problems with that. I mean indexes are used even without hints, 
>>> if they're suitable for a query.
>>> Maybe you can share your Phoenix version, query, index definition and exec 
>>> plan ?
>>> 
>>> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang >>  >> >> wrote:
>>> 
>>>Yeah, I mean no hint , use appropriate index automatically. I
>>>create a local index  and a query with corresponding index column
>>>filter in where clause. But the query doesn’t use index, with
>>>index hint it uses it.
>>> 
>>> 
>>> 
>>> -- 
>>> Aleksandr Saraseka
>>> DBA
>>> 380997600401
>>> > *•* asaras...@eztexting.com 
>>>  >> > *•* eztexting.com 
>>> >>  
>>> >
>>>  
>>> 
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >



Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread Josh Elser

http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used

On 8/19/19 6:06 AM, you Zhuang wrote:

Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
hbase-version: 1.4.6
Table:
CREATE TABLE test_phoenix.app (
dt integer not null,
a bigint not null ,
b bigint not null ,
c bigint not null ,
d bigint not null ,
e bigint not null ,
f bigint not null ,
g bigint not null ,
h bigint not null ,
i bigint not null ,
j bigint not null ,
k bigint not null ,
m decimal(30,6) ,
n decimal(30,6)
CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;

Index:
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
(Has been filled data with bulkload and index is active)

Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

The first query will use index local_c_h_index and result shortly, the 
second query won’t , and response slowly.


The explain plan is weird, all showing without using index.



On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka 
mailto:asaras...@eztexting.com>> wrote:


We have no problems with that. I mean indexes are used even without 
hints, if they're suitable for a query.
Maybe you can share your Phoenix version, query, index definition and 
exec plan ?


On Mon, Aug 19, 2019 at 12:46 PM you Zhuang > wrote:


Yeah, I mean no hint , use appropriate index automatically. I
create a local index  and a query with corresponding index column
filter in where clause. But the query doesn’t use index, with
index hint it uses it.



--
Aleksandr Saraseka
DBA
380997600401
 *•* asaras...@eztexting.com 
 *•* eztexting.com 
 



 
 
 
 
 
 







Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT 
hbase-version: 1.4.6
Table:
CREATE TABLE test_phoenix.app (
   dt  integer not null,
   a  bigint not null  ,
   b  bigint not null  ,
   c  bigint not null  ,
   d  bigint not null  ,
   e  bigint not null  ,
   f  bigint not null  ,
   g  bigint not null  ,
   h  bigint not null  ,
   i  bigint not null  ,
   j  bigint not null  ,
   k  bigint not null  ,
   m  decimal(30,6) ,
   n  decimal(30,6) 
  
  CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
  ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;

Index: 
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h)   ASYNC;
(Has been filled data with bulkload and index is active)

Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */  * from TEST_PHOENIX.APP 
where c=2 and h = 1 limit 5;
select  * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

The first query will use index local_c_h_index and result shortly, the second 
query won’t , and response slowly.

The explain plan is weird, all showing without using index.



> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka  
> wrote:
> 
> We have no problems with that. I mean indexes are used even without hints, if 
> they're suitable for a query. 
> Maybe you can share your Phoenix version, query, index definition and exec 
> plan ?
> 
> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang  > wrote:
> Yeah, I mean no hint , use appropriate index automatically. I create a local 
> index  and a query with corresponding index column filter in where clause. 
> But the query doesn’t use index, with index hint it uses it.
> 
> 
> -- 
>   Aleksandr Saraseka
> DBA
> 380997600401
>   •  asaras...@eztexting.com 
>   •  eztexting.com 
> 
>   
>  
> 
>
> 
> 
> 
>   
> 
>  
> 
>  
> 
> 
> 



Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread Aleksandr Saraseka
We have no problems with that. I mean indexes are used even without hints,
if they're suitable for a query.
Maybe you can share your Phoenix version, query, index definition and exec
plan ?

On Mon, Aug 19, 2019 at 12:46 PM you Zhuang 
wrote:

> Yeah, I mean no hint , use appropriate index automatically. I create a
> local index  and a query with corresponding index column filter in where
> clause. But the query doesn’t use index, with index hint it uses it.



-- 
Aleksandr Saraseka
DBA
380997600401
 *•*  asaras...@eztexting.com  *•*  eztexting.com










Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
Yeah, I mean no hint , use appropriate index automatically. I create a local 
index  and a query with corresponding index column filter in where clause. But 
the query doesn’t use index, with index hint it uses it.