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

ENAME COMM OR

MARTIN 1400 1

HARD 500 2

ALLEN 300 3

либо использовать NULLS LAST в конструкции ORDER BY:

scott@TKYTE816> select ename, comm, dr

2 from (select ename, comm,

3 dense rank() over (order by comm desc nulls last)

4 dr from emp

5 where comm is not null)

6 where dr <= 3

7 order by comm desc

ENAME COM4 DR

MARTIN 1400 1

WARD 500 2

ALLEN 300 3

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

Производительность

До сих пор все, что удалось узнать об аналитических функциях, свидетельствует о них как об универсальном средстве повышения производительности. Однако при неправильном использовании они могут отрицательно повлиять на производительность.

При использовании этих функций надо опасаться видимой легкости, с которой они позволяют сортировать и фильтровать множества немыслимыми в стандартном языке SQL способами. Каждый вызов аналитической функции в списке выбора оператора SELECT может использовать свои фрагменты, окна и порядок сортировки. Если они несовместимы (не являются подмножествами друг друга), может выполняться огромный объем сортировки и фильтрования. Например, ранее мы выполняли следующий запрос:

ops$tkyte@DEV816> select ename, deptno,

2 sum(sal) over (order by ename, deptno) sum ename deptno,

3 sum(sal) over (order by deptno, ename) sum deptno ename

4 from emp

5 order by ename, deptno

В этом запросе имеются три конструкции ORDER BY, то есть может потребоваться три сортировки. Две сортировки можно объединить, поскольку они выполняются по одним и тем же столбцам, но третью - придется выполнять отдельно. Это - не повод для беспокойства или отказа от использования аналитических функций. Просто надо это учитывать. С помощью аналитических функций можно так же легко написать запрос, использующий все ресурсы компьютера, как и запросы, элегантно и эффективно решающие сложные задачи.

1086 Глава 12

Резюме

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

Материализованныe представления

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

Предположим, в компании имеется база данных продаж, в которую загружены сведения о миллионах заказов, и необходимо проанализировать продажи по регионам (весьма типичный запрос). Будут просмотрены все записи, данные - агрегированы по регионам с выполнением необходимых вычислений. С помощью материализованного представления можно сохранить итоговые данные продаж по регионам и обеспечить автоматическую поддержку этих данных системой. При наличии десяти регионов продаж итоговые данные будут состоять из десяти записей, так что мы будем обращаться не к миллиону фактических записей, а только к десяти. Более того, при выполнении несколько измененного запроса, например об объеме продаж по определенному региону, ответ на него тоже можно получить по этому материализованному представлению.

В этой главе мы разберемся, что такое материализованные представления, их возможности и, самое главное, как они устроены. Я покажу, как обеспечить использование созданного материализованного представления всеми запросами, для которых оно позволяет получить ответ (иногда очевидно, что сервер Oracle мог бы использовать ма-