24
There is next partitioned table:
CREATE TABLE "ERMB_LOG_TEST_BF"."OUT_SMS"(
"TRX_ID" NUMBER(19,0) NOT NULL ENABLE,
"CREATE_TS" TIMESTAMP (3) DEFAULT systimestamp NOT NULL ENABLE,
/* other fields... */
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF"
PARTITION BY RANGE ("TRX_ID") INTERVAL (281474976710656)
(PARTITION "SYS_P1358" VALUES LESS THAN (59109745109237760) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF");
CREATE INDEX "ERMB_LOG_TEST_BF"."OUT_SMS_CREATE_TS_TRX_ID_IX" ON "ERMB_LOG_TEST_BF"."OUT_SMS" ("CREATE_TS" DESC, "TRX_ID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "SYS_P1358"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF");
I have sql query, which select 20 records ordered by date and transaction:
select rd from (
select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
from OUT_SMS
where TRX_ID between 34621422135410688 and 72339069014638591
and CREATE_TS between to_timestamp('2013-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_timestamp('2013-03-06 08:57:00', 'yyyy-mm-dd hh24:mi:ss')
order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20
Oracle has generated next plan: