Март 2005
Профессионалу разработчику
Том Кайт
Том Кайт: о сортировке, выборке и избирательном индексировании
(On Sorts, Selecting, and Selectivity, By Tom Kyte)
Источник: журнал Oracle Magazine, May-June 2004,
(http://www.oracle.com/technology/oramag/oracle/04-may/o34asktom.html).
Наш эксперт рассматривает сортировку файлов, курсоры, архитектуру, избирательное индексирование и извлечение DDL-операторов.
Можете вы привести пример, как написать на языке PL/SQL код, чтобы прочитать данные из файла, а затем отсортировать их, скажем, по двум полям, и поместить результат в переменные?
Мой ответ: не используйте для этого PL/SQL. Гораздо проще это сделать, используя внешнюю таблицу. Использование внешней таблицы позволяет легко воспользоваться всей мощностью языка SQL (включая предложение ORDER BY).
Для того чтобы использовать внешнюю таблицу, мне нужно в базе данных создать объект directory (каталог), который содержит ссылку на каталог с нужным файлом:
SQL> create or replace directory
2 data_dir as '/tmp/'
3 /
Directory created.
А теперь для работы мне нужны какие-то данные. Я буду использовать для этого мою утилиту flat, доступную на сайте http://asktom.oracle.com/~tkyte/flat/:
SQL> host flat scott/tiger -
> emp > /tmp/emp.dat
SQL> host head /tmp/emp.dat
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81, ...
Итак, в операционной системе я имею файл с простыми разделителями, и теперь я хочу отсортировать его (или, вообще говоря, просто запросить из него данные). Я могу использовать таблицу с предложением ORGANIZATION EXTERNAL, которое сообщает серверу Oracle, что данные таблицы постоянно хранятся не в базе данных, а в файле за ее пределами. Синтаксис для создания указанной выше таблицы EMP может быть таким:
SQL> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.
В оператор создания внешней таблицы (EXTERNAL_TABLE) включено нечто, что во многом похоже на управляющий файл утилиты SQL*Loader (строки с 12 по 16 в приведенном выше синтаксисе). Это не случайность; применение внешней таблицы очень похоже на применение утилиты SQL*Loader внутри сервера базы данных. В действительности, внешняя таблица позволяет делать почти все, что может делать SQL*Loader, и намного больше, поскольку теперь вам доступна вся мощность языка SQL. Для ознакомления с внешними таблицами читайте на сайте download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/part3.htm#436392 руководство Oracle9i Database Utilities. (Имеется русский перевод "Oracle9i. Утилиты сервера": http://www.rdtex.ru/win/store.get_book?bookno=209. – Прим. пер.)
Теперь, когда я создал внешнюю таблицу, сортировка файла и обработка результатов становится такой же простой, как и использование языка SQL:
SQL> select empno, ename
2 from external_table
3 order by empno;
EMPNO ENAME
----------- ----------
7369 SMITH
7499 ALLEN
...
7934 MILLER
14 rows selected.
SQL> select empno, ename
2 from external_table
3 order by ename;
EMPNO ENAME
----------- ----------
7876 ADAMS
7499 ALLEN
...
7521 WARD
14 rows selected.
Типы курсоров: Cursor или Ref Cursor
На собеседовании при поступлении на работу в качестве разработчика приложений на языке PL/SQL, меня спросили о различии между CURSOR и REF CURSOR, и когда следует использовать каждый из них. Не могли бы вы подсказать мне ответ?
Формально, на самом базовом уровне, это одно и то же. Обычный курсор в языке PL/SQL – по определению статический. REF CURSOR, с другой стороны, может открываться динамически (запрос не известен вплоть до времени его выполнения) или открываться с использованием набора статических SQL-операторов, выбор основывается на логике (блок операторов IF/THEN/ELSE будет "открывать" один или другой запрос). Например, в следующем блоке кода демонстрируется обычный статический курсор PL/SQL, курсор C. Также в нем показано, как использовать REF CURSOR (в этом примере L_CURSOR) для открытия запроса с динамическим или статическими SQL:
Declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if (to_char(sysdate,'dd') = 30)
then
-- ref cursor с динамическим sql
open l_cursor for
'select * from emp';
elsif (to_char(sysdate,'dd') = 29)
then
-- ref cursor со статическим sql
open l_cursor for
select * from dept;
else
-- ref cursor со статическим sql
open l_cursor for
select * from dual;
end if;
-- обычный статический курсор
open c;
end;
/
В этом блоке кода вы, возможно, увидите наиболее заметное различие: независимо от количества выполнений этого блока курсор C всегда будет содержать запрос select * from dual. REF CURSOR, с другой стороны, может изменяться, поскольку строковый литерал 'select * from emp' может быть заменен переменной, содержащей, практически, любой запрос.
В число других различий, не видимых в этом примере, входит возможность возврата REF CURSOR клиентскому приложению. Так возвращаются результирующие наборы из хранимых процедур в сервере Oracle (более подробно об этом см. http://asktom.oracle.com/~tkyte/ResultSets/). Статический курсор PL/SQL, с другой стороны, не может возвращаться клиентскому приложению; с ним может работать только PL/SQL.
Кроме того, статический курсор PL/SQL может быть глобальным, а REF CURSOR не может. То есть, вы не можете определять REF CURSOR в спецификации или теле пакета (за пределами функции или процедуры). REF CURSOR может обрабатываться только в процедуре, в которой он определен, или возвращаться клиентскому приложению.
REF CURSOR также можно передавать между подпрограммами, а обычный статический курсор нельзя. Для такого совместного использования статического курсора его нужно объявлять глобальным в спецификации или теле пакета. Использование глобальных переменных, в общем, не является очень хорошей практикой программирования, поэтому для совместного использования курсора в PL/SQL лучше использовать не глобальные переменные, а REF CURSOR.
И наконец, использование статических курсоров (в статическом SQL), а не REF CURSOR, более эффективно по сравнению с REF CURSOR. Применение REF CURSOR следует ограничивать:
- возвращением результирующих наборов клиентским приложениям;
- совместным использованием курсоров многими подпрограммами (это, фактически, очень похоже на предыдущий пункт);
- если нет никаких других экономичных или результативных средств для достижения вашей цели, например, когда необходимо использовать динамический SQL.
Вкратце, сначала вы пытаетесь использовать статический SQL, а REF CURSOR используете только тогда, когда это вам безусловно необходимо.
Архитектурное разногласие
У нас в промышленной эксплуатации на массе серверов находится масса баз данных, обслуживающих различные потребности бизнеса. Теперь нам для проектирования и создания веб-приложений, которым нужна информация из этих множественных баз данных, требуется использовать архитектуру J2EE. Мы обсуждаем варианты достижения такого результата.
Мы разделены на две группы с различными подходами. У нас однородный источник данных (базы данных Oracle ),поэтому одна группа для получения данных из множественных баз данных предлагает использовать связи баз данных Oracle, которые обеспечили бы лучшее управление конкурентным доступом и прозрачны для приложения. Другая группа против создания каких-либо связей баз данных и хочет иметь соединения и данные, захваченные на уровне пула соединений Java, которые обеспечат работу приложения. Таким образом, полностью устраняется необходимость тесной интеграции с программным обеспечением стороннего производителя.
Мне хотелось бы узнать, что вы думаете по этому поводу. Я хочу знать, как лучше всего проектировать и создавать веб-приложения, использующие множественные источники данных.
Я поражаюсь, как часто приходят подобные вопросы. Действительно. Кажется просто принять решение: использовать то, что вы купили и за что заплатили деньги (а это СУБД), или же писать кучу заказного кода, который нужно проектировать, отлаживать, настраивать, а затем всю оставшуюся жизнь сопровождать. Я всегда сильно переживал, стараясь понять, почему поступают такие вопросы.
В двух словах, люди в группе 2 хотят писать массу кода и иметь гарантию занятости до конца своей жизни. Они, фактически, никогда не закончат свою работу, для ответа на запросы новых функциональных возможностей потребуется в 10 раз больше времени, чем следует. Конечные пользователи будут задаваться вопросом, почему эта технологическая штука настолько трудная. Из-за группы 2 весь технический персонал будет выглядеть плохо.
Люди в группе 1 хотят использовать более рентабельный и быстрый способ. Они также будут способны поддерживать "новую технологию 532143", которая появится в следующем году. J2EE – "новая технология 532140" и она всего-навсего – архитектура промежуточного звена.
Я ушел с предыдущей своей работы из-за людей, похожих на людей группы 2! Они были полны решимости написать свои средства двухфазного завершения транзакций (даже несмотря на то, что СУБД уже умела делать это) и написать свои собственные шлюзы к другим базам данных (даже несмотря на то, что они уже имелись в сервере). Они собирались, ни больше ни меньше, реализовать свой собственный язык хранимых процедур – и все это во имя независимости от сервера базы данных. Оглядываясь назад, я вижу, они ничего из этого не закончили. Чтобы сделать хотя бы что-нибудь, они в конце концов должны были вернуться к серверу базы данных и использовать некоторые из его возможностей. Уровни, которые они добавили сверху, не добавили никаких новых функциональных возможностей; фактически, они скрыли многие функциональные возможности от конечных пользователей, препятствуя им использовать сервер базы данных.
Не знаю, почему наличие одной базы данных Oracle (благодаря связям баз данных), вместо N баз данных, принуждает вас к "тесной интеграции с программным обеспечением стороннего производителя" – чтобы это не означало. По моему мнению тесная интеграция в действительности означает для вас использование того, за что вы заплатили! Это означает, что вы не изобретаете заново оптимизатор, механизм соединений и сервер базы данных. Это означает, что вы используете передовые функциональные возможности продукта, за который вы заплатили хорошие деньги.
Эти люди в группе 2 изобретают свой собственный механизм рулевого управления, просто потому что они управляют больше чем одним автомобилем?
Эта тема вызвала большую интерактивную дискуссию (см. исходные вопрос и ответ в Вебе: asktom.oracle.com/~tkyte/architecture.html). Одно из действительно хороших вставленных замечаний:
У варианта II много проблем. Если вы не используете связи баз данных и пытаетесь все реализовать в J2EE, производительность будет кошмарной. Все экраны запросов должны иметь сообщение, говорящее: "Чтобы увидеть результат, приходите, пожалуйста, завтра"…
Рассмотрим случай, когда запрос_1 из базы_данных_1 возвращает 300 000 записей, а запрос_2 из базы_данных_2 – 10 000 записей. Окончательный запрос, результаты которого вы хотите возвратить пользователю, – соединение обоих запросов, возвращающее в конце только 1 000 строк. Если вы используете связи баз данных, сервер Oracle Database выполнит всю оптимизацию и в кратчайшее время возвратит вам 1 000 записей.
Если кто-то в вашей группе будет писать для этого код, то он будет извлекать в оперативную память сервера все 300 000+ записей и подкачка страниц "даст дуба". Это "убьет" производительность всей системы. Пострадают все пользователи. Если ваше приложение сильно ориентировано на данные, реализация на уровне пула соединений – не правильный выбор.
Но на минуту забудем о производительности – подумали ли вы, как сложна эта работа? Вы на самом деле хотите, чтобы ваши разработчики бизнес-приложений писали код, который фактически эмулирует все функции сервера базы данных? Они должны будут создавать временные пространства для загрузки и выгрузки результирующих наборов. Они должны будут реализовывать механизмы вложенных циклов, соединений, хеш-соединений, сортировок и т.д. Все это встроено в сервер базы данных (в любой сервер базы данных, в большой степени). Возможно, решение о повторном создании сервера базы данных на J2EE-уровне было наихудшим решением. Подумайте, что случится, если у конечных пользователей изменятся источники данных и технические требования. Как эти разработчики бизнес-приложений могут ответить на это, если 100 процентов их внимания сосредоточено на создании новой собственной машины базы данных? (Для разработки каждого из перечисленных механизмов требуется какое-то время; корпорация Oracle занимается этим свыше 25 лет.)
Избирательное индексирование
Я использую сервер базы данных Oracle8i Release 3 (8.1.7). Можно ли в этой версии создавать индексы только по отобранным строкам? Например, когда индексируемый столбец содержит значения NULL (я видел много примеров в различных приложениях, с которыми я работал) я, используя индекс, не вижу строк, имеющих NULL в индексируемом столбце. Это наталкивает меня на мысль, что есть потенциальная возможность, экономящая место (в памяти, а также на диске, как я предполагаю), если серверу можно "сказать": "индексировать только те столбцы, которые имеют эти значения...", или обратное: "не индексировать эти столбцы....".
Это то, что стало доступным, начиная с сервера версии Oracle8i Release 1 (8.1.5), – индексы по ключам-функциям (в редакции Enterprise Edition сервера Oracle Database). Работает это следующим образом: вы индексируете функцию, возвращающую значения данных, которые вы хотите индексировать, или, в противном случае, значение NULL. Ключи, содержащие только значения NULL, не вставляются в индексы на основе B*-деревьев, поэтому, используя эту технику, вы действительно можете избирательно индексировать только интересующие вас строки. Рассмотрим небольшой пример:
SQL> create table t ( x int );
Table created.
SQL> create index t_idx on t(x);
Index created.
SQL> insert into t
2 select null from all_users;
51 rows created.
SQL> analyze index t_idx
2 validate structure;
Index analyzed.
SQL> select name, lf_rows
2 from index_stats;
NAME LF_ROWS
------------------------- --------
T_IDX 0
Этот индекс – пустой. В таблице содержится 51 строка, а в индексной структуре – 0, поскольку в ключе содержатся только значения NULL. Теперь, если я вставлю значение, отличное от NULL-значения:
SQL> insert into t values ( 1 );
1 row created.
SQL> analyze index t_idx
2 validate structure;
Index analyzed.
SQL> select name, lf_rows
2 from index_stats;
NAME LF_ROWS
------------------------- ---------
T_IDX 1
Наконец-то у меня появился элемент индекса. Теперь я полностью избавлюсь от NULL-значений:
SQL> update t set x = rownum;
52 rows updated.
SQL> analyze index t_idx
2 validate structure;
Index analyzed.
SQL> select name, lf_rows
2 from index_stats;
NAME LF_ROWS
------------------------- ---------
T_IDX 53
И ссылки на все строки появляются в индексе. Теперь, для избирательного индексирования я могу в сложных предикатах для идентификации нужных значений использовать выражения CASE (или функцию DECODE или, фактически, любую другую функцию). В следующем примере индексируются все строки, в которых значение X меньше 25. Это может быть полезно, если в таблице много строк и большинство значений X, скажем, равных 100, указывает, что обработка записи была завершена, а небольшой процент значений X, равных 1, 2, 3, ... 25, указывает, что записи находятся в некоторой стадии обработки. Это не только экономит пространство, но также делает индекс очень привлекательным для оптимизатора, поскольку он маленький и очень селективный. В следующем примере в индексе появятся ссылки только на строки, в которых x < 25:
SQL> create index t_idx2 on
2 t( case when x < 25 then x end );
Index created.
SQL> analyze index t_idx2
2 validate structure;
Index analyzed.
SQL> select name, lf_rows
2 from index_stats;
NAME LF_ROWS
------------------------- ----------
T_IDX2 24
Более того, я могу (и, думаю, мне следует) использовать представление для скрытия сложности выполнения запросов с помощью этого индекса:
SQL> create or replace view v
2 as
3 select x,
4 case when x<25 then x end another_x
5 from t;
View created.
SQL> analyze table t
2 compute statistics;
Table analyzed.
SQL> set autotrace traceonly explain
SQL> select * from v
2 where another_x = 5;
Execution Plan
-----------------------------------
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS (BY INDEX ROWID) OF 'T'
INDEX (RANGE SCAN) OF 'T_IDX2'
SQL> set autotrace off
Как видите, я могу избирательно индексировать только интересующие меня строки, а также могу обеспечить прозрачный доступ приложений к этому индексу. Более подробно об индексах по ключам-функциям см. asktom.oracle.com/~tkyte/article1.
Извлечение DDL-операторов
Один из моих коллег работал над скриптом для извлечения DDL-операторов. Он спросил меня, существует ли какой-либо способ определить по словарю данных было ли ограничение целостности NOT NULL создано оператором типа create table (a number NOT NULL) или же это проверочное ограничение с условием IS NOT NULL. Мы по представлениям словаря данных не смогли определить это. Можете помочь?
Иногда самый легкий способ сделать что-то состоит в том, чтобы не делать этого вообще. Это как раз относится к извлечению DDL-операторов. Вам не нужно писать скрипты для извлечения DDL-операторов. Вы можете воспользоваться одним из следующих двух способов:
- использовать утилиты EXP (экспорт) и IMP (импорт);
- использовать в СУБД Oracle9i и выше пакет DBMS_METADATA, который превращает это более чем недельное упражнение в простой запрос к таблице dual!
В этом примере используются утилиты EXP и IMP:
$ exp userid=/ tables=t rows=n
Export: Release 9.2.0.4.0 -
...
About to export specified tables
. . exporting table T
Export terminated successfully.
$ imp userid=/ full=y indexfile=t.sql
Import: Release 9.2.0.4.0
...
Import terminated successfully.
$ cat t.sql
REM CREATE TABLE "OPS$TKYTE"."T"
REM ("X" NUMBER(*,0), "Y" NUMBER(*,0))
REM PCTFREE 10 PCTUSED 40 INITRANS
REM 1 MAXTRANS 255 STORAGE(INITIAL
REM 65536 FREELISTS 1 FREELIST GROUPS
REM 1) TABLESPACE "USERS" LOGGING
REM NOCOMPRESS ;
CONNECT OPS$TKYTE;
CREATE INDEX "OPS$TKYTE"."T_IDX" ON
"T" ("X" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "USERS" LOGGING ;
Как видите, файл T.SQL содержит все, что вам нужно. В СУБД Oracle9i это даже еще проще сделать с помощью пакета
DBMS_METADATA:
SQL> select dbms_metadata.get_ddl
2 ( 'TABLE', 'T' ) ddl
3 from dual;
DDL
------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
( "X" NUMBER(*,0),
"Y" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
В самом деле, вам не нужно писать никакого кода; инструментальные средства уже делают это!
Хешировать или не хешировать
Я начинаю проект по шифрованию паролей и хранению их в базе данных. На своем веб-сайте вы отмечаете, что этого никогда не нужно делать, и что для этого нужно использовать хеширование. Какие преимущества у хеширования по сравнению с шифрованием? Возможно ли обратное преобразование полученного хеш-значения?
Наиболее существенное преимущество – обратное преобразование хеш-значений невозможно, а шифрование должно быть обратимым. Шаги процесса входа в систему:
- Пользователь предоставляет имя пользователя и пароль.
- Вы проверяете их корректность.
- Если они корректны, вы предоставляете право доступа.
- Если они не корректны, вы аннулируете попытку входа в систему.
Нужно ли хранить пароли для их проверки на шаге 2? Совершенно нет. Мы будем делать это так, как делают Oracle, UNIX и большинство других систем: мы можем использовать хеширование. Шаги использования хеширования просты. Нам нужно только передать заданные имя пользователя и пароль в стандартную хеш-функцию. (Корпорация Oracle исключительно для этих целей предоставляет функцию DBMS_OBFUSCATION_TOOLKIT.MD5 в Oracle8i и Oracle9i и функцию DBMS_CRYPTO.HASH в Oracle 10g.) Мы берем хеш-значение, возвращенное этой функцией, и сравниваем его с хеш-значением, хранимым для данного пользователя. Таким способом мы можем выполнять аутентификацию, но никогда не заботиться о том, что кто-то украдет наши пароли, ведь мы же не храним их! Не существует никакого способа вывода паролей по хранимым хеш-значениям.
Каждый ответственный за информационную безопасность будет спать спокойнее, зная, что у них нет таблицы, заполненной паролями (зашифрованными или незашифрованными), которые могут быть украдены. Более подробно об этой важной теме см. asktom.oracle.com/~tkyte/hash.html.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в Oracle с 1993 года. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книг "Effective Oracle by Design" (издательство Oracle Press, 2003) – "Проектирование эффективных приложений Oracle" и "Expert One on One: Oracle" (издательство Apress, 2003) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.).
|
