|
Том Кайт
Том Кайт: о спасательной аналитике и популярности
(On Rescue Analytics and Popularity, by Tom Kyte)
Источник: журнал Oracle Magazine, November-December 2006
(http://www.oracle.com/technology/oramag/oracle/06-nov/o66asktom.html).
Наш эксперт разъясняет спасительную силу аналитики и делится популярностью.
Вопрос. Первичный ключ моей таблицы – номер строки ROW_NUM, и мне нужно обновить столицы COLA и COLB, чтобы заменить неопределенные значения (nulls) на предшествующие определенные значения. После этого обновления оператор SELECT * должен показывать следующее:
SQL> select * from t1;
ROW_NUM COLA COLB
--------- ---------- --------
1 Category 1 Mango
2 Category 1 Mango
3 Category 1 Mango
4 Category 1 Banana
5 Category 1 Banana
6 Category 1 Banana
7 Category 2 Vanilla
8 Category 2 Vanilla
9 Category 2 Strawberry
9 rows selected.
Предположим, у меня есть следующая таблица:
SQL> select * from t1;
ROW_NUM COLA COLB
--------- ---------- --------
1 Category 1 Mango
2
3
4 Banana
5
6
7 Category 2 Vanilla
8
9 Strawberry
9 rows selected.
Можно ли это сделать с помощью одного SQL-оператора или нужно писать хранимую процедуру?
Ответ. Аналитические функции позволяют легко материализовать эти данные. Я покажу два подхода. Эта проблема часто возникает в хранилищах данных при анализе разреженных данных с помощью временных рядов.
В сервере Oracle Database 10g Release 2 запрос выглядит так:
SQL> select
2 row_num,
3 last_value(cola ignore nulls)
4 over (order by row_num) cola,
5 last_value(colb ignore nulls)
6 over (order by row_num) colb
7 from t1
8 order by row_num
9 /
ROW_NUM COLA COLB
--------- ---------- --------
1 Category 1 Mango
2 Category 1 Mango
3 Category 1 Mango
4 Category 1 Banana
5 Category 1 Banana
6 Category 1 Banana
7 Category 2 Vanilla
8 Category 2 Vanilla
9 Category 2 Strawberry
9 rows selected.
В этом выпуске сервера базы данных в ряде аналитических функций, таких как LAST_VALUE (она возвращает последнее значение заданного столбца в аналитическом окне), появилось предложение игнорирования неопределенных значений IGNORE NULLS. Используя это предложение, вы можете извлечь последнее определенное значение любого заданного столбца в аналитическом окне и использовать его для замены нижеследующих неопределенных значений этого столбца. В более ранних выпусках сервера такой возможности не было, поэтому нужно проявить немного больше изобретательности; равноценный запрос в более ранних выпусках выглядит так (предположим, номер ROW_NUM – 10-разрядное положительное число):
select
row_num,
substr(
max(
case
when cola is not null
then
to_char(row_num,'fm0000000000')
||cola
end
) over (order by row_num),
11 ) cola,
substr(
max(
case
when colb is not null
then
to_char(row_num,'fm0000000000')
||colb
end
) over (order by row_num),
11 ) colb
from t1
order by row_num
/
Если вам интересно узнать, как это обрабатывается, ознакомьтесь на сайте oracle.com/technology/oramag/oracle/04-mar/o24asktom.html с более ранним выпуском колонки Ask Tom "On Format, Negation, and Sliding", в котором я для группирования данных использовал схожую технику (русский перевод: Том Кайт: о форматах вывода, отрицательных нулях и смещении времени – прим. пер.).
Теперь, когда у меня есть этот запрос, обновить первоначальные исходные данные можно просто оператором MERGE:
SQL> merge into t1
2 using (
3 select
4 row_num,
5 last_value(cola ignore nulls)
6 over (order by row_num) cola,
7 last_value(colb ignore nulls)
8 over (order by row_num) colb
9 from t1
10 ) t2
11 on (t1.row_num = t2.row_num)
12 when matched
13 then update
14 set cola = t2.cola,
15 colb = t2.colb;
9 rows merged.
И снова эта функциональная возможность сервера Oracle Database 10g: оператор MERGE только с предложением WHEN MATCHED. В более ранних версиях нужно использовать также и предложение WHEN NOT MATCHED. В этом случае я знаю, что условие WHEN NOT MATCHED никогда не будет выполнено, поскольку в наборе T2 нет никаких строк ROW_NUM, которых нет в таблице T1. Поэтому я просто использую фиктивное предложение WHEN NOT MATCHED, в котором пытаюсь вставить в строку ROW_NUM значение NULL:
merge into t1
using
(
second query from above
) t2
on (t1.row_num = t2.row_num)
when matched
then update
set cola = t2.cola,
colb = t2.colb
when not matched
then insert (row_num)
values (NULL);
И это все.
Самый популярный ответ
Он был опубликован в первый раз более пяти лет назад и стал на сайте asktom.oracle.com самым читаемым – свыше четверти миллиона просмотров. Вот он:
Вопрос. Я хочу по входным параметрам процедуры объявлять множественные курсоры, в которых будут изменяться только условия в предложении WHERE. Для всех курсоров используется одно и то же тело процедуры.
Ответ. Мне представляется, здесь целесообразно использовать курсорные переменные (тип данных ref cursor). Предположим, вы хотите создать универсальную подпрограмму, которая проверяет свои входные параметры и строит предложение WHERE для каждого параметра, если он не имеет значения NULL. В результате появится большое количество статически определенных курсоров; вместо этого следует использовать курсорные переменные, которые позволяют делать это динамически.
Я наглядно покажу это – напишу подпрограмму, которая будет выдавать какие-то данные таблицы EMP. Эта подпрограмма для ограничения результирующего набора принимает до трех входных параметров. Я хочу иметь до восьми разных курсоров, которые возможны здесь:
- один без предложения WHERE (все входные параметры имеют значение null);
- три с единичными предикатами;
- три с "парами" предикативных условий;
- один с тремя предикативными условиями.
Кроме того, одна из самых важных вещей при программировании в среде Oracle –использование переменных связывания, поэтому я также хочу использовать их. Для этого потребуются некие "трюки", поскольку я не знаю, сколько их будет во время исполнения подпрограммы (ноль, одна, две или три). Для решения этой проблемы будет использоваться контекст приложения.
Его примерная реализация:
SQL> create or replace
2 context MY_CTX
3 using MY_PROCEDURE
4 /
Context created.
Здесь создается контекст моего приложения, который связывается с моей еще не созданной процедурой MY_PROCEDURE. Обратите внимание, в этом контексте может устанавливать значения только процедура MY_PROCEDURE. Более подробно о контекстах приложений и их использовании см. asktom.oracle.com/~tkyte/article2. Теперь, для удобства я инкапсулирую вызовы процедуры DBMS_OUTPUT.PUT_LINE в небольшой подпрограмме, которая предназначена для работы со строками, размер которых превышает 255 символов (это не требуется в сервере Oracle Database 10g Release 2, в котором предельный размер строк увеличен до 32K).
SQL> create or replace
procedure p ( p_str in varchar2 )
2 is
3 l_str long := p_str||chr(10);
4 l_piece long;
5 n number;
6 begin
7 loop
8 exit when l_str is null;
9 n := instr( l_str, chr(10) );
10 l_piece :=
substr( l_str, 1, n-1 );
11 l_str :=
substr( l_str, n+1 );
12 loop
13 exit when l_piece is null;
14 dbms_output.put_line
( substr( l_piece, 1, 250));
15 l_piece :=
substr( l_piece, 251 );
16 end loop;
17 end loop;
18 end;
19 /
Procedure created.
Я буду использовать эту подпрограмму P для вывода на экран динамически генерируемого запроса, так что я смогу видеть, что строится во время каждого выполнения. На самом деле для этого примера это не существенно, считайте ее просто частью этой демонстрации.
Теперь, "ядро" ответа – подпрограмма, которая динамически конструирует для нас предикат:
create or replace
procedure my_procedure
( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
as
type rc is REF CURSOR;
l_cursor rc;
l_query varchar2(512)
default 'select *
from emp
where 1 = 1 ';
cursor l_template is select * from emp;
l_rec l_template%rowtype;
Здесь я использую то, что я называю "шаблонными" (template) курсорами. Я хочу использовать их с динамически открываемыми курсорными переменными. Мне они нужны для определения записей, в которые извлекаются данные. В данном случае, в этом простом примере, я мог бы обойтись без этого и просто определить l_rec as EMP%rowtype, но я хочу показать, как выбирать не только из одной таблицы, а из нескольких таблиц. Это просто помогает мне создавать в PL/SQL хороший тип записи. В шаблонном запросе имеются только предложения SELECT и FROM. Я никогда ничего не помещаю в предложение WHERE (даже при соединениях), потому что я никогда не открываю такие курсоры. Я только использую эти курсоры в определениях записей, показанных ниже, для получения типов данных по умолчанию, имен и т.п. Кроме того, обратите внимание на "трюк" where 1 = 1. Благодаря ему я могу просто добавлять к запросу ноль, один или более предикатов, не затрудняясь проверкой необходимости добавления предложения WHERE или условия AND. У меня всегда есть предложение WHERE, поэтому мне нужно только добавить условия AND. Обратите внимание, если для соединения нескольких таблиц используется старый синтаксис условий соединения, "трюк" where 1 = 1 не нужен – у нас уже есть предикат.
begin
if ( p_ename is NOT NULL )
then
dbms_session.set_context
( 'MY_CTX', 'ENAME',
'%'||upper(p_ename)||'%');
l_query := l_query ||
' and ename like
sys_context( ''MY_CTX'',
''ENAME'' ) ';
end if;
Этот технический прием я применяю для каждого входного параметра. Я проверяю, не имеет ли параметр значения NULL, если нет, добавляю его в предложение WHERE и устанавливаю его значение в контексте. Обратите внимание, в предложении WHERE я всегда использую функцию SYS_CONTEXT. Я никогда не использую в запросе литеральные значения – это плохо из соображений:
- производительности;
- масштабируемости;
- использования разделяемого пула;
- и, возможно, самое главное: безопасности (интоксикация SQL)
Более подробно об интоксикации SQL см. более ранний выпуск этой колонки: oracle.com/technology/oramag/oracle/05-jan/o15asktom.html (русский перевод: Том Кайт: об интоксикации и сравнении – прим. пер.).
Кроме того, обратите внимание, для занесения в строковый литерал одинарной кавычки я должен ее удваивать. В сервере Oracle Database 10g Release 1 и более поздних версиях можно использовать новый способ занесения кавычек в строковые литералы:
l_query := l_query ||
q'| and ename like
sys_context( 'MY_CTX',
'ENAME' ) |';
Для подготовки ответа, который работает во всех текущих выпусках сервера Oracle Database, я буду использовать в оставшейся части текста сервер Oracle9i Database и старый подход с удвоением ординарных кавычек. Теперь, продолжая, я обрабатываю столбец с датами найма на работу, HIREDATE:
if ( p_hiredate is NOT NULL )
then
dbms_session.set_context
( 'MY_CTX', 'HIREDATE',
to_char(p_hiredate,
'yyyymmddhh24miss'));
l_query := l_query ||
' and hiredate >
to_date(
sys_context( ''MY_CTX'',
''HIREDATE'' ),
''yyyymmddhh24miss'') ';
end if;
Здесь обратите внимание, как осторожно я сохраняю компонент даты и времени (в вашем приложении вы и только вы можете знать, как нужно обрабатывать этот компонент). Кроме того, если у вас этот компонент сравнивается со значениями типа DATE, всегда "обертывайте" вызов функции SYS_CONTEXT вызовом функции TO_DATE; это позволит при выполнении запроса избежать неявного преобразования. И наконец, я обрабатываю третий столбец с окладами, SAL:
if ( p_sal is NOT NULL )
then
dbms_session.set_context
( 'MY_CTX', 'SAL', p_sal);
l_query := l_query ||
' and sal >
to_number(
sys_context( ''MY_CTX'',
''SAL'' )
) ';
end if;
Здесь обратите внимание на явное преобразование к значению типа данных NUMBER; это позволяет избежать неявных преобразований. Теперь я готов к отладке запроса, используя мою подпрограмму P и курсорную переменную:
p( l_query );
open l_cursor for l_query;
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
dbms_output.put_line
( l_rec.ename || ',' ||
l_rec.hiredate || ',' ||
l_rec.sal );
end loop;
close l_cursor;
end;
И это все. Теперь у меня есть подпрограмма, которая открывает один из восьми возможных разных курсоров. Для проверки ее работы выполним небольшой тест:
SQL> exec my_procedure
select * from emp where 1 = 1
SMITH,17-dec-1980 00:00:00,800
.
.
.
KING,,5
PL/SQL procedure successfully completed.
SQL> exec my_procedure(p_ename=>'a')
select * from emp
where 1 = 1
and ename like
sys_context( 'MY_CTX', 'ENAME' )
ALLEN,20-feb-1981 00:00:00,1600
.
.
.
JAMES,03-dec-1981 00:00:00,950
PL/SQL procedure successfully completed.
И т.д. Этот вопрос и ответ на него – самые обсуждаемые на сайте Ask Tom, поэтому эта страница сайта одна из самых больших. Я советую прочитать оригинальное обсуждение: asktom.oracle.com/~tkyte/cursor.html – интересно ознакомиться с альтернативными способами, предложенными другими читателями, и посмотреть на их различные варианты.
Подзапросы или соединения?
Вопрос. Что лучше, с точки зрения производительности системы базы данных, соединения или подзапросы? Можете ли вы объяснить это на примерах? Кроме того, чем отличаются соединения типа nested loop (вложенный цикл) от хеш-соединений, как определить, какой тип соединений использовать с целью повышения производительности?
Ответ. Вообще говоря, соединения и подзапросы – семантически различные операции. Они могут возвращать полностью отличающиеся результаты, они не взаимозаменяемые операции. Вы должны:
- использовать подзапрос, когда вам не нужны никакие столбцы из таблицы, на которую ссылается подзапрос;
- использовать соединение, когда вам нужны какие-то такие столбцы.
Например, запрос:
select *
from emp
where deptno in
( select deptno
from dept );
"лучше" запроса:
select emp.*
from emp, dept
where emp.deptno
= dept.deptno;
Первый запрос имеет больше смысла чисто по семантическим причинам. Он говорит: "извлеки для меня из таблицы EMP все строки, номера отделов в которых (EMP.DEPTNO) имеются в таблице DEPT". Второй запрос говорит: "соедини таблицу EMP с таблицей DEPT". Вы должны дополнительно анализировать этот запрос, чтобы понять его цель (заданный вопрос). Для оптимизатора эти два запроса идентичны и их производительность одинакова.
Помните также, подзапрос просто так нельзя заменить соединением (и наоборот), поскольку часто выдаются разные ответы. Рассмотрим следующее:
SQL> select *
2 from dept
3 where deptno in
4 (select deptno
5 from emp)
6 /
DEPTNO DNAME LOC
------ ------- ------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL> select dept.*
2 from dept, emp
3 where dept.deptno
4 = emp.deptno
5 /
DEPTNO DNAME LOC
------ ------- ------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
.
.
.
30 SALES CHICAGO
14 rows selected.
Оптимизатор знает, что делать – использовать надлежащую конструкцию, основываясь на заданном вопросе. В общем, подзапросы и соединения не взаимозаменяемы; используйте то, что передает наибольший смысл.
О путях доступа –типах соединений – см. на сайте download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm в книге Performance Tuning Guide:
Какой язык использовать?
Есть популярные вопросы, а есть простые "горячие" вопросы. Под горячими я подразумеваю вопросы, которые собирают много горячих откликов. Недавно меня спросили:
Вопрос. На моем клиенте язык PL/SQL используется как для кода серверной части приложения, так и на клиентском презентационном уровне. Я думаю, для использования на презентационном уровне и уровне бизнес-логики больше подходят языки третьего поколения, такие как Java и .NET, а язык PL/SQL следует использовать только для обработки большого объема данных. Ваше мнение?
Ответ. Я счел этот вопрос несколько нелепым, поскольку он был задан на сайте Ask Tom, в котором язык PL/SQL используется как для кода серверной части приложения, так и на клиентском презентационном уровне, созданном в среде быстрой разработки веб-приложений Oracle Application Express (раньше она называлась Oracle HTML DB).
Вы можете, наверное, догадаться о сути данного мною ответа: если есть больше одного языка, можно проявить здравый смысл, чтобы использовать только язык PL/SQL или Java, или .NET, или любой другой язык, который лучше всего подходит для решения поставленной задачи. Затем последовал оживленный обмен мнениями, с которым можно ознакомиться на сайте asktom.oracle.com/~tkyte/WhatLanguage.html
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector; он автор таких книг, как "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а также ряда других. | 
