Ответ 1
Когда вы используете переменные связывания, Oracle вынуждена использовать обрезку динамических разделов вместо статической обрезки разделов. Результатом этого является то, что Oracle не знает во время разбора, к которым будут доступны разделы, поскольку это изменяется на основе ваших входных переменных.
Это означает, что при использовании литеральных значений (вместо переменных привязки) мы знаем, к каким разделам будет обращаться ваш локальный индекс. Поэтому count stopkey может быть применена к выходу индекса, прежде чем обрезать разделы.
При использовании переменных связывания partition range iterator должен определить, к каким разделам вы обращаетесь. Затем он имеет проверку, чтобы убедиться, что первая из ваших переменных в операциях действительно имеет меньшее значение, а второе (операция filter во втором плане).
Это можно легко воспроизвести, как показывает следующий тестовый пример:
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, 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 | FILTER | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | KEY | KEY |
| 6 | INDEX RANGE SCAN | I | 1 | KEY | 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
ОБНОВИТЬ
Причина, по которой вы не получаете count stopkey с работой filter в строке 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'))
Как вы можете видеть, есть дополнительная операция filter когда вы используете переменные связывания, появляющиеся перед sort order by stopkey. Это происходит после доступа к индексу. Это проверяет, что значения для переменных позволят возвращать данные (первая переменная в вашем случае действительно имеет меньшее значение, чем вторая). Это не обязательно при использовании литералов, потому что оптимизатор уже знает, что 50 меньше 100 (в данном случае). Он не знает, будет ли: a меньше: b во время разбора.
Почему именно это я не знаю. Это может быть преднамеренный дизайн Oracle - нет смысла делать проверку стоп-ключа, если значения, установленные для переменных, приводят к нулевым строкам - или просто надзор.