phoenix-user mailing list archives

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.