You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by 刘春珲 <le...@gmail.com> on 2015/09/01 08:04:05 UTC
paged queries failed with index on 3.3.1
Hi,
Recently, I'v updated phoenix from 2.2.3 to 3.3.1. I am confused about
paged queries with index.
first, I create a table and a index
----------------------------------------------------------------------
----------
CREATE TABLE IF NOT EXISTS BD_PASS_INFO_T (
LICENSE_NO VARCHAR not null,
PASS_TIME DATE not null,
PASS_PORT_SN VARCHAR not null,
SPEED decimal,
DATA_SOURCE VARCHAR
CONSTRAINT PK PRIMARY KEY (LICENSE_NO, PASS_TIME DESC, PASS_PORT_SN)
);
CREATE INDEX IDX_BD_PASS_INFO_T ON BD_PASS_INFO_T
(
PASS_TIME DESC, PASS_PORT_SN, LICENSE_NO
)
INCLUDE
(
SPEED, DATA_SOURCE
)SALT_BUCKETS = 8;
----------------------------------------------------------------------
----------
then, I run these paged queries with different conditions (each time with
one
-- comment condition, and the result is in the /**/)
BTW, These paged queries works well on 2.2.3.
----------------------------------------------------------------------
----------
EXPLAIN
SELECT
TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
FROM BD_PASS_INFO_T
WHERE
(PASS_TIME, PASS_PORT_SN, LICENSE_NO) <= (TO_DATE('2015-08-09
12:59:58'),'141207', 'AU4853')
AND PASS_TIME > TO_DATE('2014-08-01 16:00:00') /* DEGENERATE
SCAN OVER BD_PASS_INFO_T */
--AND PASS_TIME = TO_DATE('2014-08-07 16:00:00') /* DEGENERATE
SCAN OVER BD_PASS_INFO_T */
--AND PASS_PORT_SN = '141207' /* DEGENERATE
SCAN OVER BD_PASS_INFO_T */
--AND LICENSE_NO='AU4853' /* Exec */
--AND DATA_SOURCE='CY' /* Exec */
--AND SPEED>0 /* Exec */
ORDER BY PASS_TIME DESC
LIMIT 10;
----------------------------------------------------------------------
----------
Then, I tried the following paged queries. All queries have results;
----------------------------------------------------------------------
----------
EXPLAIN
SELECT
TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
FROM BD_PASS_INFO_T
WHERE
(LICENSE_NO, PASS_TIME, PASS_PORT_SN) <= ('AU4853', TO_DATE('2015-08-09
12:59:58'),'141207')
AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')
--AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')
--AND PASS_PORT_SN = '141207'
--AND LICENSE_NO='AU4853'
--AND DATA_SOURCE='CY'
--AND SPEED>0
ORDER BY PASS_TIME DESC
LIMIT 10;
----------------------------------------------------------------------
----------
Any help?
Chunhui Liu
Thanks.
Re: paged queries failed with index on 3.3.1
Posted by Chunhui Liu <le...@gmail.com>.
Hello,
Phoenix 4.5.2 on CDH 5.3.6 still has the same issue.
Thanks,
Chunhui
2015-09-01 15:17 GMT+08:00 刘春珲 <le...@gmail.com>:
> Hello,
> We are now using CDH 4.5.0, so update HBase to 0.98+ may take a little
> more time.
>
> Thanks,
> Chunhui
>
> 2015-09-01 14:54 GMT+08:00 James Taylor <ja...@apache.org>:
>
>> Hello,
>> Both 2.2.3 and 3.3.1 are no longer supported. Would it be possible for
>> you to move on to our 4.x code line on top of HBase 0.98?
>> Thanks,
>> James
>>
>> On Mon, Aug 31, 2015 at 11:04 PM, 刘春珲 <le...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> Recently, I'v updated phoenix from 2.2.3 to 3.3.1. I am confused about
>>> paged queries with index.
>>>
>>> first, I create a table and a index
>>>
>>> ----------------------------------------------------------------------
>>> ----------
>>> CREATE TABLE IF NOT EXISTS BD_PASS_INFO_T (
>>> LICENSE_NO VARCHAR not null,
>>> PASS_TIME DATE not null,
>>> PASS_PORT_SN VARCHAR not null,
>>> SPEED decimal,
>>> DATA_SOURCE VARCHAR
>>> CONSTRAINT PK PRIMARY KEY (LICENSE_NO, PASS_TIME DESC, PASS_PORT_SN)
>>> );
>>>
>>> CREATE INDEX IDX_BD_PASS_INFO_T ON BD_PASS_INFO_T
>>> (
>>> PASS_TIME DESC, PASS_PORT_SN, LICENSE_NO
>>> )
>>> INCLUDE
>>> (
>>> SPEED, DATA_SOURCE
>>> )SALT_BUCKETS = 8;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> then, I run these paged queries with different conditions (each time
>>> with one
>>> -- comment condition, and the result is in the /**/)
>>> BTW, These paged queries works well on 2.2.3.
>>> ----------------------------------------------------------------------
>>> ----------
>>> EXPLAIN
>>> SELECT
>>> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
>>> FROM BD_PASS_INFO_T
>>> WHERE
>>> (PASS_TIME, PASS_PORT_SN, LICENSE_NO) <= (TO_DATE('2015-08-09
>>> 12:59:58'),'141207', 'AU4853')
>>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00') /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00') /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND PASS_PORT_SN = '141207' /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND LICENSE_NO='AU4853' /* Exec */
>>> --AND DATA_SOURCE='CY' /* Exec */
>>> --AND SPEED>0 /* Exec */
>>> ORDER BY PASS_TIME DESC
>>> LIMIT 10;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> Then, I tried the following paged queries. All queries have results;
>>> ----------------------------------------------------------------------
>>> ----------
>>> EXPLAIN
>>> SELECT
>>> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
>>> FROM BD_PASS_INFO_T
>>> WHERE
>>> (LICENSE_NO, PASS_TIME, PASS_PORT_SN) <= ('AU4853', TO_DATE('2015-08-09
>>> 12:59:58'),'141207')
>>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')
>>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')
>>> --AND PASS_PORT_SN = '141207'
>>> --AND LICENSE_NO='AU4853'
>>> --AND DATA_SOURCE='CY'
>>> --AND SPEED>0
>>> ORDER BY PASS_TIME DESC
>>> LIMIT 10;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> Any help?
>>>
>>> Chunhui Liu
>>> Thanks.
>>>
>>
>>
>
Re: paged queries failed with index on 3.3.1
Posted by 刘春珲 <le...@gmail.com>.
Hello,
We are now using CDH 4.5.0, so update HBase to 0.98+ may take a little more
time.
Thanks,
Chunhui
2015-09-01 14:54 GMT+08:00 James Taylor <ja...@apache.org>:
> Hello,
> Both 2.2.3 and 3.3.1 are no longer supported. Would it be possible for you
> to move on to our 4.x code line on top of HBase 0.98?
> Thanks,
> James
>
> On Mon, Aug 31, 2015 at 11:04 PM, 刘春珲 <le...@gmail.com> wrote:
>
>> Hi,
>>
>> Recently, I'v updated phoenix from 2.2.3 to 3.3.1. I am confused about
>> paged queries with index.
>>
>> first, I create a table and a index
>>
>> ----------------------------------------------------------------------
>> ----------
>> CREATE TABLE IF NOT EXISTS BD_PASS_INFO_T (
>> LICENSE_NO VARCHAR not null,
>> PASS_TIME DATE not null,
>> PASS_PORT_SN VARCHAR not null,
>> SPEED decimal,
>> DATA_SOURCE VARCHAR
>> CONSTRAINT PK PRIMARY KEY (LICENSE_NO, PASS_TIME DESC, PASS_PORT_SN)
>> );
>>
>> CREATE INDEX IDX_BD_PASS_INFO_T ON BD_PASS_INFO_T
>> (
>> PASS_TIME DESC, PASS_PORT_SN, LICENSE_NO
>> )
>> INCLUDE
>> (
>> SPEED, DATA_SOURCE
>> )SALT_BUCKETS = 8;
>> ----------------------------------------------------------------------
>> ----------
>>
>> then, I run these paged queries with different conditions (each time with
>> one
>> -- comment condition, and the result is in the /**/)
>> BTW, These paged queries works well on 2.2.3.
>> ----------------------------------------------------------------------
>> ----------
>> EXPLAIN
>> SELECT
>> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
>> FROM BD_PASS_INFO_T
>> WHERE
>> (PASS_TIME, PASS_PORT_SN, LICENSE_NO) <= (TO_DATE('2015-08-09
>> 12:59:58'),'141207', 'AU4853')
>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00') /* DEGENERATE
>> SCAN OVER BD_PASS_INFO_T */
>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00') /* DEGENERATE
>> SCAN OVER BD_PASS_INFO_T */
>> --AND PASS_PORT_SN = '141207' /* DEGENERATE
>> SCAN OVER BD_PASS_INFO_T */
>> --AND LICENSE_NO='AU4853' /* Exec */
>> --AND DATA_SOURCE='CY' /* Exec */
>> --AND SPEED>0 /* Exec */
>> ORDER BY PASS_TIME DESC
>> LIMIT 10;
>> ----------------------------------------------------------------------
>> ----------
>>
>> Then, I tried the following paged queries. All queries have results;
>> ----------------------------------------------------------------------
>> ----------
>> EXPLAIN
>> SELECT
>> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
>> FROM BD_PASS_INFO_T
>> WHERE
>> (LICENSE_NO, PASS_TIME, PASS_PORT_SN) <= ('AU4853', TO_DATE('2015-08-09
>> 12:59:58'),'141207')
>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')
>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')
>> --AND PASS_PORT_SN = '141207'
>> --AND LICENSE_NO='AU4853'
>> --AND DATA_SOURCE='CY'
>> --AND SPEED>0
>> ORDER BY PASS_TIME DESC
>> LIMIT 10;
>> ----------------------------------------------------------------------
>> ----------
>>
>> Any help?
>>
>> Chunhui Liu
>> Thanks.
>>
>
>
Re: paged queries failed with index on 3.3.1
Posted by James Taylor <ja...@apache.org>.
Hello,
Both 2.2.3 and 3.3.1 are no longer supported. Would it be possible for you
to move on to our 4.x code line on top of HBase 0.98?
Thanks,
James
On Mon, Aug 31, 2015 at 11:04 PM, 刘春珲 <le...@gmail.com> wrote:
> Hi,
>
> Recently, I'v updated phoenix from 2.2.3 to 3.3.1. I am confused about
> paged queries with index.
>
> first, I create a table and a index
>
> ----------------------------------------------------------------------
> ----------
> CREATE TABLE IF NOT EXISTS BD_PASS_INFO_T (
> LICENSE_NO VARCHAR not null,
> PASS_TIME DATE not null,
> PASS_PORT_SN VARCHAR not null,
> SPEED decimal,
> DATA_SOURCE VARCHAR
> CONSTRAINT PK PRIMARY KEY (LICENSE_NO, PASS_TIME DESC, PASS_PORT_SN)
> );
>
> CREATE INDEX IDX_BD_PASS_INFO_T ON BD_PASS_INFO_T
> (
> PASS_TIME DESC, PASS_PORT_SN, LICENSE_NO
> )
> INCLUDE
> (
> SPEED, DATA_SOURCE
> )SALT_BUCKETS = 8;
> ----------------------------------------------------------------------
> ----------
>
> then, I run these paged queries with different conditions (each time with
> one
> -- comment condition, and the result is in the /**/)
> BTW, These paged queries works well on 2.2.3.
> ----------------------------------------------------------------------
> ----------
> EXPLAIN
> SELECT
> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
> FROM BD_PASS_INFO_T
> WHERE
> (PASS_TIME, PASS_PORT_SN, LICENSE_NO) <= (TO_DATE('2015-08-09
> 12:59:58'),'141207', 'AU4853')
> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00') /* DEGENERATE
> SCAN OVER BD_PASS_INFO_T */
> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00') /* DEGENERATE
> SCAN OVER BD_PASS_INFO_T */
> --AND PASS_PORT_SN = '141207' /* DEGENERATE
> SCAN OVER BD_PASS_INFO_T */
> --AND LICENSE_NO='AU4853' /* Exec */
> --AND DATA_SOURCE='CY' /* Exec */
> --AND SPEED>0 /* Exec */
> ORDER BY PASS_TIME DESC
> LIMIT 10;
> ----------------------------------------------------------------------
> ----------
>
> Then, I tried the following paged queries. All queries have results;
> ----------------------------------------------------------------------
> ----------
> EXPLAIN
> SELECT
> TO_CHAR( PASS_TIME) , PASS_PORT_SN, LICENSE_NO
> FROM BD_PASS_INFO_T
> WHERE
> (LICENSE_NO, PASS_TIME, PASS_PORT_SN) <= ('AU4853', TO_DATE('2015-08-09
> 12:59:58'),'141207')
> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')
> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')
> --AND PASS_PORT_SN = '141207'
> --AND LICENSE_NO='AU4853'
> --AND DATA_SOURCE='CY'
> --AND SPEED>0
> ORDER BY PASS_TIME DESC
> LIMIT 10;
> ----------------------------------------------------------------------
> ----------
>
> Any help?
>
> Chunhui Liu
> Thanks.
>