1040

Глава 12

12 / 1000 rows selected. Elapsed: 00:00:00.61 Execution Plan

SELECT STATEMENT Optimizer=CHOOSE

0 WINDOW (BUFFER)

1 TABLE ACCESS (BY INDEX ROWID) OF T

2 INDEX (FULL SCAN) OF T IDX (NON-UNIQUE)

Statistics

0 recursive calls

2 db block gets

292 consistent gets

6 6 physical reads

0 redo size

106978 bytes sent via SQL*Net to client

7750 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk) 1000 rows processed

Итак, потребовалось 0,61 секунды и 294 логические операции ввода-вывода. Теперь выполним эквивалентный запрос, но используя только стандартные возможности языка SQL:

tkyte@TKE816> select ename, deptno, sal,

2 (select sum(sal)

3 from t e2

4 where e2.deptno < emp.deptno

5 or (e2.deptno = emp.deptno and e2.ename

6 running total,

7 (select sum(sal)

8 from t e3

9 where e3.deptno = emp.deptno

10 and e3.ename <= emp.ename)

11 department total,

12 (select count(ename)

13 from t e3

14 where e3.deptno = emp.deptno

15 and e3.ename <= emp.ename)

16 seq

17 from t emp

18 order by deptno, ename

<= emp.ename ))

19 /

1000 rows selected. Elapsed: 00:00:06.89

Аналитические функции 41

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF T

2 1 INDEX (PULL SCAN) OF T IDX (NON-UNIQUE)

Statistics

0 recursive calls

0 db block gets

665490 consistent gets

0 physical reads

0 redo size

106978 bytes sent via SQL*Net to client

7750 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1000 rows processed

tkyte@TKYTE816> set autotrace off

Оба запроса дали одинаковые результаты, но производительность отличается существенно. Время выполнения больше во много раз, а количество логических операций ввода-вывода увеличилось на несколько порядков. Аналитические функции обработали результирующее множество, использовав намного меньше ресурсов и, соответственно, быстрее. Более того, если рассмотреть синтаксис аналитических функций, оказывается, что записывать с их помощью запросы намного проще, чем на стандартном языке SQL. Чтобы почувствовать разницу, сравните текст двух представленных выше запросов.

Как работают аналитические функции

В первой части этого раздела будут представлены подробности синтаксиса и определены термины. После этого мы перейдем непосредственно к примерам. Я продемонстрирую многие из 26 новых функций (не все, потому что при этом некоторые примеры повторялись бы). Аналитические функции используют общий синтаксис и предоставляют специфические возможности, создававшиеся для нужд технических дисциплин, незнакомых большинству разработчиков. Поняв принцип написания аналитических функций - как фрагментировать данные, как задавать окна данных и так далее, - использовать эти функции будет очень легко.

Синтаксис

Синтаксис вызова аналитической функции на вид весьма прост, но эта простота может быть обманчивой. Все начинается с такой конструкции:

ИМЯ ФУНКЦИИ(<аргумент>,< аргумент >, ...) OVER

(<конструкция фрагментации> <конструкция упорядочения> <конструкция окна>)

1042

Глава 12

Вызов аналитической функции может содержать до четырех частей: аргументы, конструкция фрагментации, конструкция упорядочения и конструкция, задающая окно. В представленном выше примере:

4 sum(sal) over

5 (partition by deptno

6 order by ename) departmenttotal,

SUM - имя функции.

(SAL) - аргумент аналитической функции. Аналитические функции принимают от нуля до трех аргументов. В качестве аргументов передаются выражения, т.е. вполне можно было бы использовать SUM(SAL+COMM).

OVER - ключевое слово, идентифицирующее эту функцию как аналитическую. В противном случае синтаксический анализатор не мог бы отличить функцию агрегирования SUM() от аналитической функции SUM(). Конструкция после ключевого слова OVER описывает срез данных, по которому будет вычисляться аналитическая функция.

PARTITION BY DEPTNO - необязательная конструкция фрагментации. Если конструкция фрагментации не задана, все результирующее множество считается одним большим фрагментом. Это используется для разбиения результирующего множества на группы, так что аналитическая функция применяется к группам, а не ко всему результирующему множеству. В первом примере главы, когда конструкция фрагментации не указывалась, функция SUM по столбцу SAL вычислялась для всего результирующего множества. Фрагментируя результирующее множество по столбцу DEPTNO, мы вычисляли SUM по столбцу SAL для каждого отдела (DEPTNO), сбрасывая промежуточную сумму для каждой группы.

ORDER BY ENAME - необязательная конструкция ORDER BY; для некоторых функций она обязательна, для других - нет. Функции, зависящие от упорядочения данных, например LAG и LEAD, которые позволяют обратиться к предыдущим и следующим строкам в результирующем множестве, требуют обязательного указания конструкции ORDER BY. Другие функции, например AVG, не требуют. Эта конструкция обязательна, если используется любая функция работы с окном (подробнее см. далее в разделе Конструкция окна ). Конструкция ORDER BY определяет, как упорядочиваются данные в группах при вычислении аналитической функции. В нашем случае упорядочивать по DEPTNO и ENAME не нужно, потому что по столбцу DEPTNO выполнялась фрагментация, т.е. неявно предполагается, что столбцы, по которым выполняется фрагментация, по определению входят в ключ сортировки (конструкция ORDER BY применяется к каждому фрагменту поочередно).

Конструкция окна в данном примере отсутствует. Именно ее синтаксис иногда кажется сложным. Подробно возможные способы задания конструкции окна будут рассмотрены ниже.

Теперь более детально рассмотрим каждую из четырех частей вызова аналитической функции, чтобы понять, как их можно задавать.