Ноябрь/Декабрь 2003
Профессионалу администратору
Том Кайт
Том Кайт: о секционировании, поиске по справочным таблицам, ссылочной целостности
(On Partitions, Lookups, and Integrity, By Tom Kyte)
Источник: журнал Oracle Magazine, March-April 2003
(
http://otn.oracle.com/oramag/oracle/03-mar/o23asktom.html).
Наш эксперт показывает, как выполнять секционирование, поиск по справочным таблицам, проверку ссылочной целостности, запросы для аудита операций DDL, а также, как определять число записей в файлах ОС.
Мы хотим преобразовать все наши обычные таблицы в секционированные таблицы. Эти обычные таблицы могут иметь миллионы строк. Следующая информация показывает, что мы имеем сейчас:
ИмяТаблицы Структура
---------- --------------------
TABLE_1
TABLE_2 Такая же как TABLE_1
TABLE_3 Такая же как TABLE_1
TABLE_4
TABLE_5 Такая же как TABLE_4
TABLE_6 Такая же как TABLE_4
TABLE_7 Такая же как TABLE_4
TABLE_8 Такая же как TABLE_4
TABLE_9
TABLE_10 Такая же как TABLE_1
TABLE_11 Такая же как TABLE_1
TABLE_12
... ...
Как видно, структура некоторых таблиц совпадает. Теперь, мы хотим секционировать таблицы следующим образом:
Имя Таблицы Примечания
---------- ------------------------
TABLE_1 будет также содержать данные таблиц TABLE_2, TABLE_3, TABLE_10 и TABLE_11
TABLE_4 будет также содержать данные таблиц TABLE_5, TABLE_6, TABLE_7 & TABLE_8
TABLE_12 только свои данные
Как преобразовать все обычные таблицы в секционированные таблицы? Заметим, мы используем один из столбцов данных в качестве ключа секционирования. Какой способ переноса данных является наиболее быстрым и безопасным?
Это делается достаточно просто. Представим далее, что таблицы
t1, t2 и t3 – ваши существующие таблицы. Таблица t – это таблица, которую вы хотите создать
(позднее, если требуется, вы можете уничтожить t1 и переименовать t в t1).
Я буду использовать следующий подход:
SQL> create table t1
as select sysdate dt,
all_objects.* from all_objects;
Table created.
SQL> create table t2
as select add_months(sysdate,-12) dt,
all_objects.* from all_objects;
Table created.
SQL> create table t3
as select add_months(sysdate,-24) dt,
all_objects.* from all_objects;
Table created.
SQL> create table t (dt, OWNER,
OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY)
partition by range(dt) (
partition part2000 values less than
(to_date('01-jan-2001','dd-mon-yyyy')),
partition part2001 values less than
(to_date('01-jan-2002','dd-mon-yyyy')),
partition part2002 values less than
(to_date('01-jan-2003','dd-mon-yyyy'))
) as
select sysdate dt, all_objects.*
from all_objects where 1=0;
Table created.
Я только что создал секционированную таблицу, которая секционирована по дате и имеет точно такую же структуру, что и ваша существующая таблица. Установлены такие диапазоны, чтобы таблица t1 размещалась в одной секции, таблица t2 – в другой и т.д. Теперь нужно загрузить вашу заполненную таблицу t1 в пустую секцию part2000:
SQL> alter table t
2 exchange partition part2000
3 with table t3
4 without validation
5 /
Table altered.
То же самое нужно сделать и для оставшихся пар "таблица-секция": перенести t2 в part2001, а t3 – в par2003. Теперь t – это таблица, секции которой представляют собой то, что раньше было таблицами t1 , t2 и t3.
Все о поиске по справочным таблицам
Мне говорили, что вместо многократного поиска справочных данных в справочной таблице (lookup) (например, поиск в таблице описаний (description) описания (description) по его идентификатору (description_id)), более эффективно загружать справочную таблицу в PL/SQL-таблицу и выполнять поиск по ней. Можете ли вы продемонстрировать, как мне загружать данные в PL/SQL-таблицу? Как выполнять поиск с помощью PL/SQL-таблицы? Я хочу загружать в PL/SQL-таблицу значения более чем из одного только столбца, могу ли я делать это? Не имеют ли PL/SQL-таблицы только одну размерность?
Ответ будет следующим: "все зависит". Предположим, у вас есть следующий код:
For x in ( select * from t )
Loop
select description
into l_description
from lookup
where id = x.description_id;
вставка в другую_таблицу
Для данного кода поиск по PL/SQL-таблице может быть более быстрым; но не таким быстрым, чтобы получить корректный ответ! Следуя моей мантре: "если вы можете сделать это одним SQL-оператором, делайте это", вам нужно использовать соединения. В следующем примере сравнивается вставка с использованием справочной таблицы и без нее (для определения общего затраченного времени используется измерительный инструментарий, доступный по адресу asktom.oracle.com/~tkyte/runstats.html):
SQL> create table lookup
2 (id primary key,
3 description )
4 organization index
5 as
6 select object_id, object_name
7 from all_objects;
Table created.
Это будет справочной таблицей: таблицей идентификаторов (id) и описаний (description). Через минуту вы увидите, как загружать ее в ассоциативную PL/SQL-таблицу, с помощью которой будет выполняться поиск. Но сначала я сгенерирую другую таблицу с тестовыми данными – входными данными, по которым я хочу выполнять поиск:
SQL> create table t
2 as
3 select object_id
4 from all_objects;
Table created.
А сейчас я создам таблицу для вставки результатов поиска. Я буду моделировать типичную загрузку данных: чтение некоторых данных, поиск по справочной таблице и загрузка в другую таблицу.
SQL> create table target
2 (id number, descript varchar2(30));
Table created.
Я начну с подхода, в котором используется справочная PL/SQL-таблица. Сначала я должен создать в PL/SQL тип таблицы; это делается в строках 8-12. Затем я выполняю итерацию по входным данным, поиск справочных данных и их загрузку в выходную таблицу:
SQL> declare
2 type lookupTableType is table of
3 lookup.description%type index by
4 binary_integer;
5
6 l_lookup lookupTableType;
7 begin
8 for x in (select * from lookup)
9 loop
10 l_lookup(x.id) :=
11 x.description;
12 end loop;
13
14 for x in (select * from t)
15 loop
16 begin
17 insert into target
18 values
19 ( x.object_id,
20 l_lookup(x.object_id) );
21 exception
22 when no_data_found then
23 insert into target
24 values
25 ( x.object_id, null );
26 end;
27 end loop;
28 end;
29 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.24
Как видно, для выполнения поиска по справочной таблице требуется немногим более 13 секунд. Можем ли мы сделать это быстрее? Несомненно:
SQL> begin
2 insert into target
3 select t.object_id, l.description
4 from t, lookup l
5 where t.object_id = l.id(+);
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.43
Одна операция вставки делает то же самое за какие-то доли секунды. Почти всегда вы можете обойтись без процедурного кода и делать то же самое одним SQL-оператором, вы только выиграете от этого. Чем меньше кода вводится и выполняется, тем, как правило, быстрее он работает.
Медленно ли выполняется проверка ссылочной целостности
У нас есть таблица city, содержащая список городов, в которых находятся офисы наших клиентов. В таблице city хранятся метаданные – значения столбцов вряд ли будут изменяться. Мы имеем некоторые пособия для служащих, рассчитываемые на основании городов, в которых служащие работают.
Мы используем форму, написанную на языке Visual Basic, для вставки информации о служащих в таблицу employee. Для столбца city таблицы employee определен внешний ключ, ссылающийся на первичный ключ таблицы city.
Один из наших консультантов порекомендовал нам выбросить проверку правильности вводимых городов в сервере базы данных и перенести ее в клиентскую машину. В качестве причины было указано, что проверка ссылочной целостности в сервере требует слишком больших затрат времени и замедляет задания, обрабатывающие вводимые данные.
Первоначально у нас будет много ввода данных, а количество городов в таблице city равно 25. Я не был убежден доводами консультанта. Действительно ли они обоснованы?
Вы благоразумны, консультант неправ.
Выполнение этой проверки на стороне клиента не будет быстрее. Если справочная таблица не кешируется в клиентской машине, клиент должен выполнять "круговой" обмен данными с сервером базы данных. Если же клиент пытается кешировать эту информацию, загружая таблицу по сети, то при обновлении таблицы кешированная информация будет неправильной (или перед обновлением вы должны будете попросить всех ваших пользователей выйти из программы!).
Вы должны спросить себя: во все времена к этим данным будет обращаться только одно приложение? (Это риторический вопрос, история показывает, что ответ будет отрицательным.) Так, если вы скрываете все правила связей между данными в приложении, то что тогда? Периодически вам придется их извлекать и переделывать.
Выполнял ли консультант тестирование, чтобы проверить влияние на производительность? Давайте выполним быструю проверку. Я буду использовать следующую простую схему:
drop table cities;
create table cities as
select username city from all_users
where rownum <= 50;
alter table cities add constraint cities_pk primary key(city);
create table t1 ( x char(80), city
references cities );
create table t2 ( x char(80), city varchar2(30) );
Теперь для нагрузочного тестирования я вставлю в таблицу t1 большой объем данных с включенной проверкой ссылочной целостности, а в таблицу t2 без проверки. Для этого я буду использовать следующий код:
alter session set sql_trace=true;
declare
type array is table of varchar2(30)
index by binary_integer;
l_data array;
begin
select * BULK COLLECT into l_data
from cities;
for i in 1 .. 1000
loop
for j in 1 .. l_data.count
loop
insert into t1
values ('x', l_data(j) );
end loop;
end loop;
end;
/
Копируем этот блок PL/SQL и заменяем t1 на t2 для второго теста. Теперь, используя утилиту TKPROF для форматирования результатов трассировки SQL_TRACE, обнаруживаем следующее (для экономии места часть информации вычеркнута):
INSERT into t1
values
('x', :b1 )
call count cpu query current
----- ----- ---- ----- -------
Parse 1 0.00 2 0
Execute 37000 9.25 479 78969
Fetch 0 0.00 0 0
----- ----- ---- ----- -------
total 37001 9.26 481 78969
INSERT into t2
values
('x', :b1 )
call count cpu query current
---- ----- ---- ----- -------
Parse 1 0.00 0 0
Execute 37000 7.87 484 41900
Fetch 0 0.00 0 0
---- ----- ---- ----- -------
total 37001 7.88 484 41900
Итак, с включенной проверкой ссылочной целостности мы затрачиваем 0.00025 (9.25/37,000) секунд времени ЦП на вставку одной строки и 0.000212703 (7.87/37,000) секунд времени ЦП на вставку одной строки без проверки. Заметят ли это ваши конечные пользователи? Заметите ли это вы? Подавляющее большинство задержек при работе конечных пользователей будет связано с программой Visual Basic, рисующей экраны. Не думаете ли вы, что справочная подпрограмма этого консультанта будет выполняться за .000037297 секунд? Если не думаете, вы лучше разбираетесь в базах данных.
И не только это. Предположим, позднее вы решаете выполнять нерегламентированные запросы данных этого приложения. Инструментарию для этой работы будут доступны ограничения ссылочной целостности. Когда позднее вы будете расширять это приложение, никакое новое приложение не сможет нарушить ограничения целостности, что можно было бы легко сделать в противном случае. А также, когда вы изменяете список городов, вы не должны будете сначала останавливать работу всех клиентов (для очистки их кешей), а потом вновь запускать их. Вы сможете спокойно спать по ночам, зная что ваша база данных защищена.
Получаемые вами выгоды: целостность данных, легкость сопровождения, самодокументированный код. И это значительно перевешивает любые ощущаемые проблемы времени.
Запросы для операций DDL
Мне нужно знать, как идентифицировать операции языка определения данных (DDL, data definition language), выполненные за последние 24 часа. Если разработчик перезаписывает какую-то функцию, есть ли какой-либо способ извлечения ее старого кода из таблиц схемы SYSTEM? Кроме того, как мне идентифицировать зависимые объекты и получить список объектов во всех схемах, зависящих от данного объекта?
Вы хотите организовать аудит. Я не смогу помочь вам получить информацию за последние 24 часа, но завтра вы будете иметь ее. Когда кто-то перезаписывает код, старый код пропадает, стирается, и уже нигде не хранится. Исключая копирования старой резервной копии базы данных и восстановления базы данных на момент времени в прошлом (point-in-time recovery), перед тем, когда кто-то удалил процедуру, код которой пропал.
Вы можете использовать DDL-триггер (BEFORE CREATE – перед созданием) для "захвата" кода, а также и для аудита. Это полезно во многих случаях, поэтому я подробно остановлюсь на идее и покажу здесь ее реализацию. Я начну с создания таблицы для хранения старого кода (и его множественных версий) и регистрации, кто и когда перезаписал его:
SQL> create table old_code
2 as
3 select user username, 0 version,
sysdate date_changed,
user_source.*
4 from user_source
5 where 1=0
6 /
Table created.
SQL> create sequence version_seq;
Sequence created.
Теперь для сохранения "старого кода" перед его перезаписыванием я могу использовать триггер BEFORE CREATE:
SQL> create or replace
trigger create_trigger
2 before create on schema
3 declare
4 l_date date := sysdate;
5 l_ver number;
6 begin
7 if (ora_dict_obj_type in
( 'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'FUNCTION' ) )
8 then
9 select version_seq.nextval
into l_ver
from dual;
10
11 insert into old_code
12 select user, l_ver,
l_date, user_source.*
13 from user_source
14 where name = ora_dict_obj_name
15 and type = ora_dict_obj_type;
16 end if;
17 end;
18 /
Trigger created.
Для демонстрации этого триггера в действии в следующем примере я заменяю в базе данных функцию:
SQL> create or replace
function f return number
2 as
3 begin
4 return 0;
5 end;
6 /
Function created.
SQL> create or replace
function f return date
2 as
3 begin
4 return sysdate;
5 end;
6 /
Function created.
SQL> select line, text from
old_code order by line;
LINE TEXT
---- -------------------------
1 function f return number
2 as
3 begin
4 return 0;
5 end;
Исходный код гарантированно защищен. Я отслеживаю, кто и когда перезаписал его (таким образом я смогу найти эту персону, если это будет нужно), а также у меня есть исходный текст на тот случай, когда потребуется его восстановление.
Что касается зависимостей, представления словаря данных USER_DEPENDENCIES, ALL_DEPENDENCIES и DBA_DEPENDENCIES содержат все что вам нужно.
Определение количества записей в файлах ОС
По ночам мы загружаем в наше хранилище данных от 4 до 15 плоских файлов из двух разных унаследованных систем. Как часть нашего обычного процесса ежедневного аудита загрузки данных, нам нужно проверять счетчики записей (передаются по FTP). Все файлы имеют фиксированную длину записей. Длина записей изменяется от 100 символов до 3,500 и больше. Во всех файлах используется кодировка US7ASCII. Число записей в "узких" файлах находится в диапазоне от менее 50 до 250,000; число записей в "широких" файлах находится в диапазоне от 10,000 до 4 миллионов. Общий диапазон размера файлов колеблется от нескольких килобайтов до 100МБ и выше. На выполнение команды "wc -l" (подсчет числа строк) для наших самых больших файлов обычно затрачивается около минуты. Определение фактического количества записей в плоском файле ОС представляет собой трудную задачу. Я видел примеры загрузки листинга каталогов в таблицу базы данных с помощью Java и примеры выполнения команд ОС из PL/SQL-кода (также с помощью Java), но я не Java-программист, и мне нужно скомпоновать эти два примера так, чтобы подсчитать число записей во всех файлах данного каталога и сохранить результат в таблице базы данных. Есть какие-нибудь предложения?
Файлы имеют фиксированную длину записей, поэтому все что вам нужно сделать – извлечь размер файла и разделить его на размер записей. Это можно сделать просто. Используя тип данных BFILE (указатель на большой двоичный файл), вы можете извлекать размеры файлов и, зная длину логических записей (LRECL,
logical record length) в этих файлах, вы можете вычислить количество записей:
SQL> create or replace directory
2 MY_FILES as '/tmp/';
Directory created.
SQL> create or replace
2 function number_of_records
( p_filename in varchar2,
p_lrecl in number )
3 return number
4 as
5 l_bfile bfile;
6 l_recs number;
7 begin
8 l_bfile :=
bfilename('MY_FILES',
p_filename);
9 dbms_lob.fileopen( l_bfile );
10 l_recs :=
dbms_lob.getlength(l_bfile) /
p_lrecl;
11 dbms_lob.fileclose( l_bfile );
12 return l_recs;
13 end;
14 /
Function created.
SQL> !wc -l /tmp/foo.dat
18 /tmp/foo.dat
SQL> exec dbms_output.put_line
(number_of_records( 'foo.dat', 20 ));
18
PL/SQL procedure successfully completed.
Если по каким-то причинам вы не знаете LRECL, вам потребуется выполнить еще один шаг:
SQL> create or replace
2 function number_of_records
( p_filename in varchar2 )
3 return number
4 as
5 l_bfile bfile;
6 l_lrecl number;
7 l_recs number;
8 begin
9 l_bfile :=
bfilename( 'MY_FILES',
p_filename );
10 dbms_lob.fileopen( l_bfile );
11 l_lrecl :=
dbms_lob.instr
( l_bfile,
utl_raw.cast_to_raw(chr(10)));
12 l_recs :=
dbms_lob.getlength( l_bfile )/
l_lrecl;
13 dbms_lob.fileclose( l_bfile );
14 return l_recs;
15 end;
16 /
Function created.
SQL> exec dbms_output.put_line
(number_of_records('foo.dat'));
18
PL/SQL procedure successfully completed.
Эта функция ищет в файле позицию первого символа новой строки (chr(10)) и предполагает, что это и есть LRECL для данного файла.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) с 1993 года работает в Oracle. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книги “Expert One on One: Oracle”( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.) и соавтор книги “Beginning Oracle Programming” – “Введение в программирование баз данных Oracle” (обе книги были опубликованы издательством Wrox Press,
www.wrox.com).
| 
