There is partitioned table:
create table CPA_MT_MSG_HISTORY
(
ID INTEGER not null,
TRANS_PK INTEGER not null,
TRANS_ID VARCHAR2(10) not null,
MSG_ID INTEGER not null,
MSISDN VARCHAR2(24) not null,
SERVICE_IDENTIFIER VARCHAR2(1024),
MSG_STATUS INTEGER default 0 not null,
TRAFFIC_TYPE INTEGER default 1 not null,
RESP_TIME DATE not null,
REPORT_TIME DATE,
REPORT_ID VARCHAR2(70),
SUBSCRIBER_CHANNEL_ID VARCHAR2(24) not null,
PARTNER_CHANNEL_ID VARCHAR2(24) not null,
REPORT_DELIVERY_REQ INTEGER default 0,
CHARGE_RESULT INTEGER default 2,
SERVICE_ID INTEGER not null,
NETWORK_ADDRESS VARCHAR2(1024),
START_TIME DATE not null
)
partition by range (START_TIME)
/
create index cpa_mt_msg_history_### on cpa_mt_msg_history
(
id,
partner_channel_id,
start_time desc
)
local
/
I need scan partitions in reverse order when using the local index, but Oracle optimizer cannot scan partitions in reverse order (Pstart = 1, Pstop = 197):
SQL> select *
2 from ( select /*+ index( mh cpa_mt_msg_history_### ) */
3 mh.*
4 from cpa_user_vk_p.cpa_mt_msg_history mh
5 where mh.id = 163081
6 and mh.partner_channel_id = 74200
7 order by mh.start_time desc )
8 where ROWNUM < 2
9 /
----------------------------------------------------------
Plan hash value: 4096800458
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1245 | 397 (1)| 00:00:05 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 1245 | 397 (1)| 00:00:05 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 110 | 397 (1)| 00:00:05 | | |
| 4 | PARTITION RANGE ALL | | 1 | 110 | 396 (0)| 00:00:05 | 1 | 197 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| CPA_MT_MSG_HISTORY | 1 | 110 | 396 (0)| 00:00:05 | 1 | 197 |
|* 6 | INDEX RANGE SCAN | CPA_MT_MSG_HISTORY_### | 1 | | 395 (0)| 00:00:05 | 1 | 197 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - filter(ROWNUM<2)
6 - access("MH"."ID"=163081)
filter(TO_NUMBER("MH"."PARTNER_CHANNEL_ID")=74200)
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
335 consistent gets
0 physical reads
0 redo size
1541 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
At the same time I can scan partitions in reverse order when using full scan (Pstart = 197, Pstop =1):
SQL> select *
2 from ( select /*+ full( mh ) */
3 mh.*
4 from cpa_user_vk_p.cpa_mt_msg_history mh
5 where mh.id = 163081
6 and mh.partner_channel_id = 74200
7 order by mh.start_time desc )
8 where ROWNUM < 2
9 /
----------------------------------------------------------
Plan hash value: 1697377122
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1245 | 284K (3)| 00:56:59 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL | | 1 | 1245 | 284K (3)| 00:56:59 | 197 | 1 |
| 3 | VIEW | | 1 | 1245 | 284K (3)| 00:56:59 | | |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 110 | 284K (3)| 00:56:59 | | |
|* 5 | TABLE ACCESS FULL | CPA_MT_MSG_HISTORY | 1 | 110 | 284K (3)| 00:56:59 | 197 | 1 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
4 - filter(ROWNUM<2)
5 - filter("MH"."ID"=163081 AND TO_NUMBER("MH"."PARTNER_CHANNEL_ID")=74200)
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
216 consistent gets
77 physical reads
116 redo size
1541 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
69 sorts (memory)
0 sorts (disk)
1 rows processed
How to force Oracle scan the table partitions in reverse order when using the local index?
Edited by: Denis Komarov on 17.12.2009 4:56