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.
