Бьюсь уже целый день, оптимизатор побеждает :)
Поле боя: секционированная таблица вида
create table ane_test (pkey date, p1 number, p2 number, p3 date, val char(100))
partition by range(pkey)
(partition ane_test_p2 values less than (to_date('2006-03','yyyy-mm'))
,partition ane_test_p3 values less than (to_date('2006-04','yyyy-mm'))
,partition ane_test_p4 values less than (to_date('2006-05','yyyy-mm'))
,partition ane_test_p5 values less than (to_date('2006-06','yyyy-mm'))
);
create index ane_test$p1$p2$p3 on ane_test(p1,p2,p3) local;
insert into ane_test select
to_date('2006-03','yyyy-mm')+rownum/200, mod(rownum,4),
rownum*3,
to_date('2006-03','yyyy-mm')+rownum/200+dbms_random.value,
'Val'||rownum
from dual connect by level <=18000;
commit;
exec dbms_stats.gather_table_stats(user,'ANE_TEST',cascade=>TRUE);
|
где pkey - ключ секционирования, который мешает жить.
p1 - некий условный источник данных.
p2 - целочисленная последовательность. Определяет порядок следования записей от источника p1.
Шаг варьируется в широких пределах и носит достаточно случайный характер
(эта особенность не позволяет ограничить p2 снизу без ущерба для логики).
Последовательности p2 для различных значений p1 независимы.
p3 - просто некая дата, коррелирующая с p2, но к рассматриваемой проблеме отношения не имеющая :)
Одним из условий задачи является запрет на изменение предложенного окружения :)
Задача:
задешево получить max(val)
среди N последних записей при заданном p1.
Вроде все просто, но не совсем:
Пусть для определенности p1=2, N=10, pkey>sysdate-90
-- Просто отбор данных, без агрегации.
SELECT TRIM(VAL)
FROM
( SELECT /*+ index_desc(t ane_test$p1$p2$p3) */ * FROM ANE_TEST T WHERE PKEY
> :B2 AND P1 = :B1 ORDER BY P2 DESC ) WHERE ROWNUM < :B3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 11 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 11 0 4
-- Всего 11 логических чтений, замечательный результат
Rows Row Source Operation
------- ---------------------------------------------------
4 COUNT STOPKEY (cr=11 pr=0 pw=0 time=1219 us)
4 VIEW (cr=11 pr=0 pw=0 time=1203 us)
4 SORT ORDER BY STOPKEY (cr=11 pr=0 pw=0 time=1168 us)
12 PARTITION RANGE ITERATOR PARTITION: 4 KEY (cr=11 pr=0 pw=0 time=543 us)
12 COUNT STOPKEY (cr=11 pr=0 pw=0 time=616 us) -- Вот оно, счастье!
-- Умный оптимизатор отбирает в каждой просматриваемой секции
-- индекса не более N записей. Очень логично и замечательно работает.
12 TABLE ACCESS BY LOCAL INDEX ROWID ANE_TEST PARTITION: 4 KEY (cr=11 pr=0 pw=0 time=591 us)
12 INDEX RANGE SCAN DESCENDING ANE_TEST$P1$P2$P3 PARTITION: 4 KEY (cr=7 pr=0 pw=0 time=351 us)(object id 523680)
--------------------------------------------------------------------------------
-- НО СТОИТ ДОБАВИТЬ АГРЕГАЦИЮ - и счастья как не бывало:
SELECT MAX(TRIM(VAL))
FROM
(SELECT VAL FROM ( SELECT /*+ index_desc(t ane_test$p1$p2$p3) */ * FROM
ANE_TEST T WHERE PKEY > :B2 AND P1 = :B1 ORDER BY P2 DESC ) WHERE ROWNUM < :B3 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.02 0.02 0 351 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 351 0 1
-- Та же задача, те же параметры, но уже 351 логическое чтение.
-- В "боевой" обстановке - до 300000, что непозволительно много.
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: 25939 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=351 pr=0 pw=0 time=24574 us)
4 VIEW (cr=351 pr=0 pw=0 time=24346 us)
4 COUNT STOPKEY (cr=351 pr=0 pw=0 time=24340 us)
4 VIEW (cr=351 pr=0 pw=0 time=24325 us)
4 SORT ORDER BY STOPKEY (cr=351 pr=0 pw=0 time=24287 us)
4500 PARTITION RANGE ITERATOR PARTITION: 4 KEY (cr=351 pr=0 pw=0 time=18084 us) -- Обратите внимание, COUNT STOPKEY отсутствует.
-- Т.е. в каждом разделе сканируется все поддерево индекса и
-- отсечение "излишков" производится лишь при сортировке.
4500 TABLE ACCESS BY LOCAL INDEX ROWID ANE_TEST PARTITION: 4 KEY (cr=351 pr=0 pw=0 time=13769 us)
4500 INDEX RANGE SCAN DESCENDING ANE_TEST$P1$P2$P3 PARTITION: 4 KEY (cr=23 pr=0 pw=0 time=4701 us)(object id 523680)
--------------------------------------------------------------------------------
|
Трасса 10053 показывает, что для запроса с агрегацией "SINGLE TABLE ACCESS PATH (First K Rows)", дающий требуемый эффект, даже не рассматривается.
Тот же печальный результат получается даже если написать
with tab as ("отбор данных без агрегации") select * from tab; |
Так что пришлось остановиться на компромиссном варианте (выборка №1 в массив на стороне PL/SQL и ручное вычисление max(val).)
Если кто-то сумеет решить задачу средствами SQL (т.е. сумеет впихнуть COUNT STOPKEY обратно под PARTITION RANGE ITERATOR) - буду рад если поделитесь :)