有下一个分区表：
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");
我有SQL查询，它选择按日期和事务排序的20条记录：
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已经制定了下一个计划：
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%cpu)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 240 | | 4788K (1)| 00:05:02 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 312M| 3576M| | 4788K (1)| 00:05:02 | | |
|* 3 | SORT ORDER BY STOPKEY | | 312M| 9G| 12G| 4788K (1)| 00:05:02 | | |
| 4 | PARTITION RANGE ITERATOR| | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 |
|* 5 | COUNT STOPKEY | | | | | | | | |
|* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
5 - filter(ROWNUM<=20)
6 - access(SYS_OP_DESCEND("CREATE_TS")>=HEXTORAW('878EFCF9F6C5FEFAFF') AND
SYS_OP_DESCEND("TRX_ID")>=HEXTORAW('36F7E7D7F8A4F0BFA9A3FF') AND
SYS_OP_DESCEND("CREATE_TS")<=HEXTORAW('878EFDFEF8FEF8FF') AND
SYS_OP_DESCEND("TRX_ID")<=HEXTORAW('36FBD0E9D4E9DBD5F8A6FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TIMESTAMP' 2013-03-06 08:57:00,000000000' AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=72339069014638591 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=34621422135410688 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TIMESTAMP' 2013-02-01 00:00:00,000000000')
它完美地运作。
顺便说一下，表OUT_SMS由TRX_ID字段分区，OUT_SMS_CREATE_TS_TRX_ID_IX是每个分区上的本地索引(CREATE_TS DESC，TRX_ID DESC)。
但是，如果我将此查询转换为预准备语句：
select rd from (
select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
from OUT_SMS
where TRX_ID between ? and ?
and CREATE_TS between ? and ?
order by CREATE_TS DESC,TRX_ID DESC
) where rownum <= 20
Oracle生成下一个计划：
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 240 | 14743 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1964 | 23568 | 14743 (1)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1964 | 66776 | 14743 (1)| 00:00:01 | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
4 - filter(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss') AND
TO_NUMBER(:ABC)<=TO_NUMBER(:EBC))
6 - access(SYS_OP_DESCEND("CREATE_TS")>=SYS_OP_DESCEND(TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss')) AND
SYS_OP_DESCEND("TRX_ID")>=SYS_OP_DESCEND(TO_NUMBER(:EBC)) AND
SYS_OP_DESCEND("CREATE_TS")<=SYS_OP_DESCEND(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')) AND
SYS_OP_DESCEND("TRX_ID")<=SYS_OP_DESCEND(TO_NUMBER(:ABC)))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=TO_NUMBER(:ABC) AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=TO_NUMBER(:EBC) AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss') AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss'))
操作COUNT STOPKEY从计划中消失。此操作应该在分析索引之后，从第一个查询中获取每个分区的20行。
如何编写准备好的声明以在计划中使用COUNT STOPKEY？
解决方法
当您使用绑定变量时，Oracle被迫使用
dynamic partition pruning而不是
static partition pruning.结果是Oracle在分析时不知道将访问哪些分区，因为这会根据您的输入变量而变化。
这意味着当使用文字值(而不是绑定变量)时，我们知道本地索引将访问哪些分区。因此，在修剪分区之前，可以将计数停止键应用于索引的输出。
使用绑定变量时，分区范围迭代器必须确定您正在访问的分区。然后进行检查以确保操作之间的第一个变量实际上具有比第二个更低的值(第二个计划中的过滤操作)。
这可以很容易地重现，如下面的测试用例所示：
create table tab (
x date,y integer,filler varchar2(100)
) partition by range(x) (
partition p1 values less than (date'2013-01-01'),partition p2 values less than (date'2013-02-01'),partition p3 values less than (date'2013-03-01'),partition p4 values less than (date'2013-04-01'),partition p5 values less than (date'2013-05-01'),partition p6 values less than (date'2013-06-01')
);
insert into tab (x,y)
select add_months(trunc(sysdate,'y'),mod(rownum,5)),rownum,dbms_random.string('x',50)
from dual
connect by level <= 1000;
create index i on tab(x desc,y desc) local;
exec dbms_stats.gather_table_stats(user,'tab',cascade => true);
explain plan for
SELECT * FROM (
SELECT rowid FROM tab
where x between date'2013-01-01' and date'2013-02-02'
and y between 50 and 100
order by x desc,y desc
)
where rownum <= 5;
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +ROWS +PARTITION'));
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
| 3 | SORT ORDER BY STOPKEY | | 1 | | |
| 4 | PARTITION RANGE ITERATOR| | 1 | 2 | 3 |
| 5 | COUNT STOPKEY | | | | |
| 6 | INDEX RANGE SCAN | I | 1 | 2 | 3 |
--------------------------------------------------------------------
explain plan for
SELECT * FROM (
SELECT rowid FROM tab
where x between to_date(:st,'dd/mm/yyyy') and to_date(:en,'dd/mm/yyyy')
and y between :a and :b
order by x desc,'BASIC +ROWS +PARTITION'));
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
| 3 | SORT ORDER BY STOPKEY | | 1 | | |
| 4 | FILTER | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | KEY | KEY |
| 6 | INDEX RANGE SCAN | I | 1 | KEY | KEY |
---------------------------------------------------------------------
在您的示例中，第二个查询只能在分析时将分区过滤到密钥，而不是像第一个示例中那样过滤确切的分区。
这是少数情况之一，其中文字值可以提供比绑定变量更好的性能。您应该调查这是否可能。
最后，你说你想要从每个分区20行。你的查询不会这样做，它只会根据你的订单返回前20行。对于20行/分区，您需要执行以下操作：
select rd from (
select rowid rd,row_number() over (partition by trx_id order by create_ts desc) rn
from OUT_SMS
where TRX_ID between ? and ?
and CREATE_TS between ? and ?
order by CREATE_TS DESC,TRX_ID DESC
) where rn <= 20
UPDATE
你没有得到计数停止密钥的原因是与“坏”计划的第4行中的过滤操作有关。如果重复上面的示例，但没有分区，则可以更清楚地看到这一点。
这为您提供了以下计划：
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY| |
|* 4 | TABLE ACCESS FULL | TAB |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - filter("X">=TO_DATE(' 2013-01-01 00:00:00','syyyy-mm-dd
hh24:mi:ss') AND "X"<=TO_DATE(' 2013-02-02 00:00:00','syyyy-mm-dd
hh24:mi:ss') AND "Y">=50 AND "Y"<=100)
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY| |
|* 4 | FILTER | |
|* 5 | TABLE ACCESS FULL | TAB |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B) AND
TO_DATE(:ST,'dd/mm/yyyy')<=TO_DATE(:EN,'dd/mm/yyyy'))
5 - filter("Y">=TO_NUMBER(:A) AND "Y"<=TO_NUMBER(:B) AND
"X">=TO_DATE(:ST,'dd/mm/yyyy') AND "X"<=TO_DATE(:EN,'dd/mm/yyyy'))
如您所见，当您使用通过stopkey排序顺序之前出现的绑定变量时，会有一个额外的过滤操作。访问索引后会发生这种情况。这是检查变量的值是否允许返回数据(中间的第一个变量实际上具有比第二个更低的值)。使用文字时不需要这样做，因为优化器已经知道50小于100(在这种情况下)。它不知道：a是否小于：b在分析时。
为什么这是我不知道的。它可能是Oracle的有意设计 – 如果为变量设置的值导致零行 – 或者只是疏忽，那么进行stopkey检查是没有意义的。
