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

Оба представленных выше запроса возвращают данные о трех сотрудниках, которые получают наибольшие зарплаты. Запрос, использующий аналитические функции, позволяет получить эти сведения без особ1х усилий: для этого требуется 0,01 секунды процессорного времени и 27 логических операций ввода-вывода. А реляционный запрос требует выполнения более 22000 логических операций ввода-вывода, и на это уходит 0,80 секунде! процессорного времени. Для выполнения запроса, не использующего аналитические функции, необходимо выполнять подзапрос для каждой строки в таблице Т, чтобы найти три наибольших зарплаты в данном отделе. Этот запрос не только медленнее выполняется, но и сложен для написания. Его производительность можно повысить с помощью подсказок, но при этом он стал бы еще менее понятным и более хрупким . (Как любой запрос, использующий подсказки: подсказка - это всего лишь предложение, и оптимизатор может его проигнорировать). Запрос с аналитическими функциями, определенно, выигрывает как по производительности, так и по простоте.

Теперь переходим ко второму вопросу: выдать до трех человек с максимальными зарплатами.

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 count(*) over (partition by deptno

4 order by sal desc

5 range unbounded preceding)

6 cnt from t)

7 where cnt <= 3

8 order by deptno, sal desc

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

Rows

Row Source Operation

150 VIEW 100 0 WINDOW SORT 1000 TABLE ACCESS FULL T

*************************************

scott@TKYTE816> select deptno, ename, sal

2 from t el

3 where (select count(*)

4 from t e2

5 where e2.deptno = e1.deptno

6 and e2.sal >= e1.sal) <= 3

1068

Глава 12

7 order by deptno, sal desc

Misses In library cache during parse: Optimizer goal: CHOOSE Parsing user id: 54

Rows

Row Source Operation

150 SORT ORDER BY 150 FILTER

1001 TABLE ACCESS FULL T 2000 SORT AGGREGATE

10827 INDEX FAST FULL SCAN (object id 27867)

И на этот раз результаты говорят сами за себя: 0,03 секунды процессорного времени и 27 логических операций ввода-вывода по сравнению с 0,61 секунды процессорного времени и более чем 8000 логических операций ввода-вывода. Снова явное преимущество имеет запрос, использующий аналитические функции. Причина и на этот раз в том, что при отсутствии аналитических функций для каждой строки базовой таблицы приходится выполнять коррелированный подзапрос. Этот подзапрос подсчитывает количество записей в том же отделе для сотрудников с такой же или более высокой зарплатой. Выбираются только записи, для которых это количество меньше или равно 3. Результаты запросов одинаковы, но используемые во время выполнения ресурсы принципиально различны. Составить оба запроса одинаково несложно, но производительность при использовании аналитических функций несравнимо выше.

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

scott@TKYTE816>select deptno, ename, sal

from t el

where (select count(*) from t e2

where e2.deptno = el.deptno

and e2.sal >= el.sal ) <=3

order by deptno, sal desc

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

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

Rows

Row Source Operation

150 VIEW

1000 WINDOW SORT

1000 TABLE ACCESS FULL T

*************************************

scott@TKYTE816> select deptno, ename, sal

2 from t el

3 where (select count(*)

4 from t e2

5 where e2.deptno = e1.deptno

6 and e2.sal >= e1.sal

7 and (e2.sal > e1.sal OR e2.rowid

8 order by deptno, sal desc

e1.rowid)) < 3

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

Rows

Row Source Operation

150 SORT ORDER BY 150 FILTER

1001 TABLE ACCESS FULL T 2000 SORT AGGREGATE 9827 INDEX FAST FULL SCAN

(object id 27867)

И на этот раз производительность запросов несравнима. Версия с аналитической функцией во много раз превосходит по производительности реляционный запрос. Кроме того, версию с использованием аналитических функций в данном случае написать намного проще. Созданный коррелированный подзапрос весьма сложен. Необходимо подсчитать количество сотрудников в отделе, у которых зарплата больше или равна зарплате в текущей записи. Кроме того, если зарплата не больше зарплаты в текущей записи (совпадает с ней), такую запись можно учитывать, только если значение ROWID (или любого столбца с уникальными значениями) больше, чем в текущей записи. Это гарантирует, что строки не будут считаться дважды, а кажды1й раз будет выбираться другой набор строк.