1064

Глава 12

Перейдем теперь к последнему запросу: Отсортировать специалистов по продажам по убыванию зарплат и вернуть первые три строки . Это легко сделать с помощью функции ROW NUMBER():

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 row number() over (partition by deptno

4 order by sal desc)

5 rn from emp)

6 where rn <= 3

9 rows selected.

При выполнении запроса каждый фрагмент сортируется по убыванию значений зарплат, после чего по мере обработки каждой строке фрагмента присваивается последовательный номер. После этого с помощью конструкции WHERE мы получаем только первые три строки каждого фрагмента. В примере с транспонированием результирующего множества мы используем такой же прием для преобразования строк в столбцы. Следует отметить, однако, что для отдела DEPTNO=30 возвращаются в определенном смысле случайные строки. Если помните, информация в отделе 30 б1ла изменена так, что все 6 сотрудников получили значение 99 в столбце зарплаты. Можно в некоторой степени управлять тем, какие три записи будут возвращаться, с помощью конструкции ORDER BY. Например, можно использовать конструкцию ORDER BY SAL DESC, ENAME для получения упорядоченной по фамилии информации о наиболее высокооплачиваемых сотрудниках, если несколько из них имеют одинаковую зарплату.

Интересно отметить, что с помощью функции ROW NUMBER можно получать произвольную секцию данных из группы строк. Это может пригодиться в среде, не поддерживающей информацию о состоянии, когда надо выдавать данные постранично. Например, если необходимо выдавать данные из таблицы ЕМР, отсортированные по столбцу ENAME, группами по пять строк, можно использовать запрос следующего вида:

scott@TKYTE816> select ename, hiredate, sal

2 from (select ename, hiredate, sal,

3 row number() over (order by ename)

4 rn from emp)

5 where rn between 5 and 10

6 order by rn

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

6 rows selected.

И напоследок, чтобы продемонстрировать всю мощь аналитических функций, сравним запросы с аналитическими функциями с такими же запросами, где эти функции не используются. Для сравнения я создал таблицу Т, являющуюся увеличенной во всех см1слах разновидностью таблицы ЕМР:

scott@TKYTE816> create table t

select object name ename,

mod(object id,50) deptno,

object id sal from all objects where rownum <= 1000

Table created.

scott@TKYTE816> create index t idx on t(deptno,sal desc); Index created.

scotteTKYTE816> analyze table t

2 compute statistics

3 for table

4 for all indexed columns

5 for all indexes

Table analyzed.

Мы создали индекс по этой таблице, позволяющий ответить на запросы, которые мы будем к ней выполнять. Теперь сравним тексты и производительность запросов с аналитическими функциями и без них. Для сравнения производительности я использовал установки SQL TRACE, TIMED STATISTICS и утилиту TKPROF. Подробнее об этих средствах и интерпретации результатов см. в главе 10, посвященной стратегиям и средствам настройки производительности:

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 dense rank() over (partition by deptno

4 order by sal desc)

5 dr from t)

6 where dr <= 3

7 order by deptno, sal desc

1066

Глава 12

call

Parse

Execute

Fetch

count

2 11

0.00 0.00 0.01

elapsed disk

0.00 0.00 0.07

query

0 0 10

current

total

0.01

0.07

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

Rows Row Source Operation

150 VIEW

3 64 WINDOW SORT PUSHED RANK

1000 TABLE ACCESS FULL T

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

scott@TKYTE816> select deptno, ename, sal

from t el where sal

deptno

(select sal

from (select distinct sal from t e3

order by deptno, sal desc) e2 where e2.deptno = e1.deptno and rownum <= 3) 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

1000 FILTER

3700 COUNT STOPKEY

2850 VIEW

2850 SORT ORDER BY STOPKEY

20654 SORT UNIQUE

20654 TABLE ACCESS FULL T

rows