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

Я даже добавил в индекс столбец ENAME, чтобы при выполнении запроса достаточно б1ло обращения только к индексу, без обращения к таблице по значению ROWID. Запрос с аналитической функцией демонстрирует следующую производительность:

scott@TKYTE816> select deptno, ename, hiredate,

2 lag(hiredate, 1, null) over (partition by deptno

3 order by hiredate, ename)

4 hiredate - lag(hiredate, 1, null)

5 over (partition by deptno

6 order by hiredate, ename) days last,

7 lead(hiredate, 1, null)

8 over (partition by deptno

9 order by hiredate, ename) next hire,

10 lead(hiredate, 1, null)

11 over (partition by deptno

12 order by hiredate, ename)

13 from emp

14 order by deptno, hiredate

15 /

call

last hire,

hiredate days next

count

elapsed disk

query current

Parse 1 0.01 0.01 0

Execute2 0.00 0.00 0 0

Fetch 1313 0.72 1.57 142

19675

total

1316

0.73

1.58

Misses in library cache during parse: Optimizer goal: FIRST ROWS Parsing user id: 54

Rows

Row Source Operation

19675

19675 WINDOW BUFFER

19675 INDEX FULL SCAN (object id 27899)

Сравним с эквивалентна запросом, где аналитические функции не используются:

scott@TKYTE816> select deptno, ename, hiredate,

2 hiredate-(select max(hiredate)

3 from t e2

4 where e2.deptno = e1.deptno

5 and e2.hiredate < e1.hiredate) last hire,

6 hiredate-(select max(hiredate)

7 from t e2

8 where e2.deptno = e1.deptno

9 and e2.hiredate < e1.hiredate) days last,

10 (select man(hiredate)

11 from t e3

12 where e3.deptno = e1.deptno

13 and e3.hiredate > e1.hiredate) next hire,

14 (select min(hiredate)

15 from t e3

16 where e3.deptno = e1.deptno

1080

Глава 12

and е3.hiredate > eLhiredate) - hiredate days next

18 from t e1

19 order by deptno,

20 /

hiredate

call

count

Parse 1

Execute 1 Fetch 1313

0.01 0.00

2.48

elapsed disk

0.01 0.00

2.69

query current

141851

rows

19675

total

1315

2.49

2.70

141851

19675

Misses in library cache during parse: 0 Optimizer goal: FIRST ROWS Parsing user id: 54

Rows Row Source Operation

19675 INDEX FULL SCAN (object id 27899)

Производительность этих двух запросов существенно отличается. Сравните: 135 логических операций ввода-вывода и 141000; 0,73 секунды процессорного времени и 2,49. Запрос с аналитической функцией и в этом случае оказался намного эффективнее. Учтите также сложность текста запросов. Мне кажется, запрос с помощью функций LAG и LEAD не только проще написать, но и понять впоследствии, что выбирается. Прием select из select - хороший трюк, но такой код сложнее придумать, а при чтении полученного запроса часто очень трудно понять, что он выбирает. Чтобы восстановить логику второго запроса, придется намного больше думать.

Теперь давайте более детально рассмотрим функции LAG и LEAD. Эти функции принимают три аргумента:

lag(Argl, Arg2, Arg3)

Argl

выражение, которое надо вернуть на основе другой строки.

Arg2 - смещение требуемой строки в группе относительно текущей. Смещение задается как положительное целое число. В случае функции LAG берется соответствующая смещению предыдущая строка, а в случае функции LEAD - следующая. Этот аргумент имеет стандартное значение 1.

Arg3 - возвращаемое значение в том случае, если смещение, заданное аргументом Arg2, выводит за границу группы. Например, первая строка в каждой группе не имеет предыдущей, так что значение функции LAG(... , 1) для этой строки определить нельзя. Можно возвращать стандартное значение NULL или указать значение явно. Следует учитывать, что окна для функций LAG и LEAD не используются - можно задавать конструкции PARTITION BY и ORDER BY, но не ROWS или RANGE.

Итак, в нашем примере:

4 hiredate - lag(hiredate, 1, null)

5 over (partition by deptno

6 order by hiredate, ename)

days last,

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

функция LAG использовалась для поиска предыдущей строки, поскольку в качестве второго параметра передавалось значение 1 (если предыдущей записи нет, возвращается значение NULL). Мы фрагментировали данные по столбцу DEPTNO, так что каж-д1й отдел просматривается независимо от остальных. Полученный фрагмент мы упорядочили по значению столбца HIREDATE, так что вызов LAG(HIREDATE, 1, NULL) возвращает максимальное значение HIREDATE, меньшее соответствующего значения в текущей строке.

Проблемы

С аналитическими функциями у меня почти не было проблем. Они позволяют получать ответ на абсолютно новые запросы намного эффективнее, чем до их появления. Если разобраться в синтаксисе, открываются безграничные возможности. Не часто бывает, когда результат можно получить практически даром, но с аналитическими функциями все обстоит, похоже, именно так. Следует, однако, помнить о четырех потенциальных проблемах.

Аналитические функции в PL/SQL

При попытке использования аналитических функций в коде на языке PL/SQL могут возникать ошибки. Если взять простой запрос и поместить его в PL/SQL-блок:

scott@TKYTE816> variable x refcursor

scott@TKYTE816> set autoprint on

scott@TKYTE816> begin

2 open :x for

3 select mgr, ename,

4 row number() over (partition by mgr

5 order by ename)

6 rn from emp;

7 end;

row number() over (partition by mgr *

ERROR at line 5:

ORA-06550: line 5, column 31:

PLS-00103: Encountered the symbol ( when expecting one of the

following:

, from into bulk

синтаксический анализатор PL/SQL его не воспримет. Анализатор SQL-операторов, используемый в PL/SQL, еще не понимает (в версиях Oracle 8i - прим. научн. ред.) синтаксис вызова аналитических функций. Сталкиваясь с подобными проблемами (есть и другие конструкции, не воспринимаемые синтаксическим анализатором PL/SQL), я использую динамически открываемую курсорную переменную. Реализация показанного выше запроса в этом случае может выглядеть так:

scott@TKYTE816> variable x refcursor

scott@TKYTE816> set autoprint on