Оконные функции сортируют дорого, можем ли мы преодолеть это?
Мое требование: определить 10 лучших аккаунтов DEPT_NUM, упорядоченный по номеру счета в порядке возрастания.
Запрос:
SELECT * FROM
(
select acctnum,dept_num,row_number() OVER (PARTITION BY DEPT_NUM ORDER BY ACCTNUM) as row_identifier
FROM MYTABLE
)
WHERE row_identifier between 1 and 10;
Трассировка:
7532 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1480074522
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 577K| 15M| | 3855 (1)| 00:00:47 |
|* 1 | VIEW | | 577K| 15M| | 3855 (1)| 00:00:47 |
|* 2 | WINDOW SORT PUSHED RANK| | 577K| 7890K| 13M| 3855 (1)| 00:00:47 |
| 3 | INDEX FAST FULL SCAN | IMTAB05 | 577K| 7890K| | 987 (1)| 00:00:12 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=5)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY "ACCTNUM")<=5)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
4298 consistent gets
0 physical reads
0 redo size
144367 bytes sent via SQL*Net to client
486 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7532 rows processed
Индекс:
index scan говорит, INDEX STORAGE на колонне DEPT_NUM,
форсирование Full Table Сканирование сделано с 3855 по 11092
Общее количество строк в таблице - 632667;
Все вышеперечисленное является результатом тестирования области. Производство на самом деле в два раза больше.
Моя база данных - Exadata, Quarter RAC. Запуск Oracle 11g R2. База данных достаточно мощная, чтобы выполнять ее мгновенно, но администратор базы данных не хотел использовать tempSpc 13M. Бизнес сообщил, что частота этого отчета будет 4 раза в час. И главное, эта таблица получает много вставок / обновлений в реальном времени
Можем ли мы импровизировать процесс, как
1) Увеличение PGA для сеанса?(Не уверен, действительно ли это возможно?)
2) Поможет ли дополнительный индекс?
Просто хочу взглянуть на это другими глазами, так как наша группа полностью сосредоточена только на параметрах DBA.
Спасибо за любые предложения!
1 ответ
Производительность аналитической функции может зависеть от порядка столбцов индекса. Изменение индекса с (ACCTNUM,DEPT_NUM) в (DEPT_NUM,ACCTNUM) может снизить стоимость и устранить необходимость во временном табличном пространстве.
partition by COL_2 order by COL_1 => INDEX FAST FULL SCAN|WINDOW SORT PUSHED RANK
partition by COL_1 order by COL_2 => INDEX FULL SCAN|WINDOW NOSORT
INDEX FAST FULL SCAN использует более быстрый многоблочный ввод-вывод, но также требует сортировки данных и, возможно, временного табличного пространства для области сортировки.
INDEX FULL SCAN использует более медленный одноблочный ввод-вывод, но возвращает данные по порядку и избегает сортировки.
Пример схемы и данных
--drop table mytable;
create table mytable(dept_num number not null, acctnum number not null
,a number, b number, c number, d number, e number);
insert into mytable
select 1 dept_num, 1 acctnum, 0,0,0,0,0 from dual union all
select 1 dept_num, 2 acctnum, 0,0,0,0,0 from dual union all
select 1 dept_num, 3 acctnum, 0,0,0,0,0 from dual union all
select 2 dept_num, 1 acctnum, 0,0,0,0,0 from dual union all
select 2 dept_num, 2 acctnum, 0,0,0,0,0 from dual union all
select 3 dept_num, 1 acctnum, 0,0,0,0,0 from dual;
--Create 600K similar rows.
insert into mytable
select dept_num + rownumber*3, acctnum, a,b,c,d,e
from mytable
cross join (select level rownumber from dual connect by level <= 100000);
begin
dbms_stats.gather_table_stats(user, 'mytable');
end;
/
(ACCTNUM, DEPT_NUM) = WINDOW SORT PUSHED RANK
create index mytable_idx on mytable(acctnum, dept_num);
explain plan for
select dept_num, acctnum from
(
select dept_num, acctnum
,row_number() over (partition by dept_num order by acctnum) as row_identifier
from mytable
)
where row_identifier between 1 and 10;
select * from table(dbms_xplan.display);
Plan hash value: 952182109
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 600K| 22M| | 1625 (3)| 00:00:23 |
|* 1 | VIEW | | 600K| 22M| | 1625 (3)| 00:00:23 |
|* 2 | WINDOW SORT PUSHED RANK| | 600K| 4687K| 9424K| 1625 (3)| 00:00:23 |
| 3 | INDEX FAST FULL SCAN | MYTABLE_IDX | 600K| 4687K| | 239 (3)| 00:00:04 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY "ACCTNUM")<=10)
(DEPT_NUM, ACCTNUM) = WINDOW NOSORT
drop index mytable_idx;
create index mytable_idx on mytable(dept_num, acctnum);
explain plan for
select dept_num, acctnum from
(
select dept_num, acctnum
,row_number() over (partition by dept_num order by acctnum) as row_identifier
from mytable
)
where row_identifier between 1 and 10;
select * from table(dbms_xplan.display);
Plan hash value: 1773829932
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 600K| 22M| 792 (2)| 00:00:12 |
|* 1 | VIEW | | 600K| 22M| 792 (2)| 00:00:12 |
|* 2 | WINDOW NOSORT | | 600K| 4687K| 792 (2)| 00:00:12 |
| 3 | INDEX FULL SCAN| MYTABLE_IDX | 600K| 4687K| 792 (2)| 00:00:12 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY
"ACCTNUM")<=10)