Май 2005
Профессионалу разработчику
Том Кайт
Том Кайт: о числах и аналитике
(On
Numbers and Analytics, by Tom Kyte)
Источник: журнал Oracle Magazine, July-August 2004
(
http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html
).
Наш эксперт рассказывает о поиске, числах, аналитике и
упорядочении.
Вопрос. У меня есть управляющая таблица,
содержащая различные значения: числа, строки и даты. Все они хранятся как данные
типа VARCHAR, но могу ли я выполнить какую-то легкую проверку, что значение –
это число? Существует ли функция "isnumeric", которая делает это?
Ответ. Я предпочитаю хранить даты как даты, а
числа как числа, и, вероятно, искал бы отличное от вашего решение, если эта
таблица будет иметь некоторый реальный размер. В СУБД Oracle "родных" функций
типа "isnumeric" не существует; тем не менее, их очень легко написать. Возьмем,
например, следующую функцию: SQL> create or replace
2 function isnumeric
3 ( p_string in varchar2)
4 return boolean
5 as
6 l_number number;
7 begin
8 l_number := p_string;
9 return TRUE;
10 exception
11 when others then
12 return FALSE;
13 end;
14 /
Function created.
Эта функция возвратит TRUE, если входная строка не может быть
преобразована в число (с использованием текущих установок NLS на уровне сеанса).
Для дат приходится передавать формат: SQL> create or replace
2 function isdate
3 ( p_string in varchar2,
4 p_fmt in varchar2 := null)
5 return boolean
6 as
7 l_date date;
8 begin
9 l_date :=
10 to_date(p_string,p_fmt);
11 return TRUE;
12 exception
13 when others then
14 return FALSE;
15 end;
Function created.
Аналитика приходит на помощь (еще один раз)
Вопрос. У меня есть следующие тестовые
данные: select *
from test_sort;
T_NM P_NM ID
---- ---- --
01t 02P 36
01t 01p 37
03t 02P 38
03t 01p 39
02T 02P 40
02T 01p 41
Мне нужен запрос, который выдает данные в следующем
порядке: T_NM P_NM ID
---- ---- --
01t 01p 37
01t 02P 36
03t 01p 39
03t 02P 38
02T 01p 41
02T 02P 40
Сортировка базируется на минимальном значении в столбце ID
среди всех одинаковых значений в столбце T_NM (36 для всех 01t, 38 для всех 03t
и 40 для всех 02T), а затем это сортируется по значениям столбца P_NM. Я
пробовал различные подзапросы и аналитические функции (rank, dense_rank,
row_number). Это кажется настолько простым, но я никак не могу добиться этого.
Какие-либо идеи?
Ответ. Ответ скрыт в вашем вопросе; вы пишете,
что "сортировка базируется на минимальном значении в столбце ID среди всех
одинаковых значений в столбце T_NM". В данном случае это –почти решение. Все,
что вам нужно, делается в этом SQL-операторе: SQL> select t_nm, p_nm, id,
2 min(id)
3 over (partition by t_nm)
4 min_id
5 from test_sort
6 order by 4, 2
7 /
T_NM P_NM ID MIN_ID
---- ---- -- ------
01t 01p 37 36
01t 02P 36 36
03t 01p 39 38
03t 02P 38 38
02T 01p 41 40
02T 02P 40 40
6 rows selected.
Теперь вы можете сортировать по минимальным ID с
группированием запроса по столбцу T_NM, а затем в каждой группе сортировать по
столбцу P_NM.
Независящий от регистра поиск
Вопрос. Мы имеем приложение стороннего
производителя, которое мы используем для выборки определенных значений из
нескольких таблиц. Символьные значения, хранимые в поле, могут быть любыми:
Bell, bell, BELL, beLL и т.д. Существует ли способ изменения регистра значений
(в верхний или нижний регистр)во время выполнения DML-операций, но без
использования триггеров? И если нет, существует ли способ выполнения операторов
SELECT (без использования функций UPPER или LOWER), чтобы возвратить все
значения независимо от их регистра?
Ответ. В сервере базы данных Oracle 10g
существует способ сделать это. Перед Oracle 10g вы должны были применять
один из следующих методов:
- использовать индекс по ключу-функции UPPER (имя_столбца) и модифицировать
запрос, вставив предложение WHERE UPPER (column_name) = значение;
- использовать триггер для преобразовании значения столбца при его
модификации в верхний или нижний регистр;
- использовать инструмент Oracle Text для создания ТЕКСТОВОГО индекса
столбца; текстовые индексы (они позволяют использовать в предикате операцию
CONTAINS) могут быть чувствительными или нечувствительными к регистру;
В каждом из этих методов нарушается одно из ваших условий –
нужно использовать триггеры или функции UPPER() или CONTAINS в предложении
WHERE.
В Oracle 10g это можно сделать прозрачно. Предположим,
у вас имеется следующая таблица данных: SQL> select * from t;
DATA
--------------
Hello
HeLlO
HELLO
И, предположим, вы выполняете следующий запрос: SQL> variable x varchar2(25)
SQL> exec :x := 'hello';
SQL> select *
2 from t
3 where data = :x;
no rows selected
В этой таблице не найдено совпадений со словом "hello",
написанном строчными буквами. Хорошо, вы можете изменить все это с помощью
некоторых операторов ALTER SESSION: SQL> alter session
2 set nls_comp=ansi;
Session altered.
SQL> alter session
2 set nls_sort=binary_ci;
Session altered.
SQL> select *
2 from t
3 where data = :x;
DATA
--------------
Hello
HeLlO
HELLO
Это – тот же самый запрос с теми же самыми входными данными,
но совершенно с другим результатом. Эффект от этих двух операторов ALTER SESSION
– включение нечувствительного к регистру режима сравнения для условий =, < и
>.
В действительности, вы можете сделать еще один шаг и даже
создать индекс данных, не чувствительных к регистру, который может
использоваться при их поиске. Рассмотрим пример: SQL> create index t_idx on
2 t( nlssort( data,
3 'NLS_SORT=BINARY_CI' ) );
Index created.
SQL> begin
2 dbms_stats.set_table_stats
3 (ownname=>user,
4 tabname=>'T',
5 numrows=> 100000);
6 end;
7 /
Итак, я создал индекс столбца DATA по ключу-функции и
использовал для этого двоичную нечувствительную к регистру сортировку. Затем я
"обманул" оптимизатор, сказав ему, что в таблице содержится масса строк.
Используя режим AUTOTRACE, вы можете убедиться, что оптимизатор может и будет
использовать индекс для доступа к этой информации: SQL> set autotrace on
SQL> set linesize 121
SQL> select *
2 from t
3 where data = :x;
DATA
--------------
Hello
HeLlO
HELLO
Execution Plan
------------------------------
SELECT STATEMENT (Cost=2)
TABLE ACCESS (BY INDEX ROWID)
INDEX (RANGE SCAN) OF 'T_IDX'
Удаление дубликатов
Вопрос. Объясните, пожалуйста, как быстро удалить
записи-дубликаты за один проход из большой таблицы, содержащей около 5 миллионов
записей. Я пытался сделать это с помощью следующего оператора, но он выполнялся
10 часов: delete from test1
where rowid not in (select min(rowid)
from test1 group by rc_no);
Даже после увеличения размера сегмента отката до 7ГБ мы не
добились приемлемого результата.
Ответ. Если вы используете оптимизатор по
синтаксису (RBO, rule-based optimizer), вероятнее всего, запрос в предложении
NOT IN выполняется для каждой строки из внешнего запроса! Так что, надеюсь, вы
используете более интеллектуальный оптимизатор по стоимости (CBO, cost-based
optimizer).
Но, в любом случае, мой подход к удалению записей-дубликатов
несколько отличается. Я бы для удаления сгенерировал набор идентификаторов строк
(rowed), используя аналитические функции, а затем удалил бы строки: SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;
COUNT(*) COUNT(CUST_SEG_NBR)
--------- -------------------
1871652 756667
Здесь вы можете видеть, я имею свыше 1,8 миллиона строк, но
только 756 667 из них – уникальны. Мне нужно удалить около 1,1 миллиона
моих строк. Посмотрим, как быстро это можно сделать: delete from t
where rowid in
(select rid
from
(select rowid rid,
row_number() over
(partition by
cust_seg_nbr
order by rowid) rn
from t
)
where rn <> 1
)
1114985 rows deleted.
Elapsed: 00:01:46.06
SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;
COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
--------- ---------------------------
756667 756667
Это общее время выполнения (Elapsed) было получено на
портативном компьютере (так что у вас оно может отличаться). Кроме того, таблица
у меня не была проиндексирована. Каждый индекс увеличивает время выполнения.
Рассмотрите возможность отключения ваших индексов перед массовым удалением,
подобным этому, после завершения которого мы можете повторно создать ваши
индексы. Сопровождение индексов при выполнении большой операции массовой
обработки данных, в которой вы планируете удалить большинство данных, – очень
дорогое удовольствие.
Что касается размера сегмента отката, он обязательно станет
настолько большим, насколько это потребуется для выполнения удаления. Для
каждого индекса, который вы имеете, потребуется больше места, не считая того,
что это займет больше времени.
Другой весьма жизнеспособный вариант массового удаления данных
– создание новой таблицы, которая содержит только корректные записи, и удаление
старой таблицы, как в следующем примере: SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;
COUNT(*) COUNT(CUST_SEG_NBR)
--------- -------------------
1871652 756667
create table t2
as
select cust_seg_nbr, ...
from
(select t.*,
row_number() over
(partition by cust_seg_nbr
order by rowid) rn
from t
)
where rn = 1
/
Table created.
Elapsed: 00:00:10.93
Как видите, в этом примере для копирования нужных строк
потребовалось только 11 секунд по сравнению с 1 минутой и 46 секундами для
удаления не нужных строк, и вы можете делать это, не генерируя никакую
информацию для возможного отката.
Еще о числах
Вопрос. Я имею таблицу с полем типа VARCHAR2 и
хочу выбирать из нее все записи, в которых в поле VARCHAR2 достоверно содержится
число. Например, если в моей таблице имеются следующие данные: name seq
---- ---
ab 10
cd 1-
ef 1a
gh 12
ij 1.0
Я хочу, чтобы результат запроса был таким: ab 10
gh 12
Я отвергаю все, что не имеет числовой формат. Запрос, который
вы предоставите мне, я буду использовать очень часто для выполнения обновлений
этой таблицы.
Ответ. Это очень похоже на рассмотренный ранее
вопрос о функции "isnumeric". Решение, фактически, такое же, но я добавлю индекс
по ключу-функции (см. также asktom.oracle.com/~tkyte/article1)
для ускорения идентификации строк.
Я напишу небольшую PL/SQL-функцию, которая является
детерминированной (deterministic, это означает, что при одинаковых значениях
аргументов функция всегда возвращает одно и то же значение.). В данном случае я
сделаю так, чтобы возвращалось только два значений: либо 1 (означает "да",
аргумент был числом) или NULL (означает "нет", аргумент не был числом). SQL> create or replace
2 function is_number
3 ( p_string in varchar2 )
4 return number
5 deterministic
6 as
7 l_num number;
8 begin
9 l_num := p_string;
10 return 1;
11 exception
12 when others then
1P class=copybody> 13 return null;
14 end;
15 /
Function created.
Теперь я могу взять одну из ваших таблиц со столбцом типа
VARCHAR2 и сделать следующее: SQL> create index t_idx
2 on t(is_number(data));
Если вы выполните запрос SELECT * FROM T WHERE IS_NUMBER(data)
= 1, вы получите все содержащие числа строки с помощью индекса. Заметим, этот
индекс будет очень компактным, поскольку в индексах Oracle на основе B*-деревьев
не создаются элементы, содержащие только NULL-значения, так что любая строка
таблицы, в которой нет числа, не будет представлена в этом индексе.
Упорядочение всего, чего угодно
Вопрос. Если я жестко не кодирую предложение
ORDER BY в жестком курсоре типа ref cursor, то при извлечении данных из курсора
предложение ORDER BY игнорируется. Могу ли я добиться динамического упорядочения
в среде Oracle8i Release 3 (8.1.7) или более поздних версий? Я могу использовать
параметры во всех других случаях, которые я пробовал, исключая ORDER BY. Пример
небольшого участка кода: open p_cursor for
select ename, empno
From emp
order by
decode( variable,
'1', 'ENAME',
'2', 'EMPNO' )
Ответ. Это не тот случай, когда ORDER BY DECODE
игнорируется; это использование ORDER BY DECODE совпадает с select *
from t
order by 'hello world';
Это то, что вы делаете на самом деле – упорядочение по
строковой константе. Так что, предложение ORDER BY не было проигнорировано –
просто это не то, что вы хотели сделать!
Существует два способа добиться желаемого. В первом
используется функция DECODE; в другом используется динамический SQL. Рассмотрим
сначала функцию DECODE. Я могу написать: order by
decode( l_sort_by,
'1', ename,
'2', to_char(empno, '0009'))
Эта функция DECODE всегда возвращает символьную строку, по
которой выполняется сортировка. Если l_sort_by = 1, она для сортировки возвратит
ename, или же возвратит EMPNO, которое сформатировано как строка фиксированной
длины, поэтому сортировка выполняется надлежащим образом. Если вы имеете дату,
вы будете использовать функцию to_char(dt, 'yyyymmddhh24miss'). Если вы имеете
числа, которые могут быть отрицательными, вам потребуется формат, который
позволит сортировать их правильно. Главное – возвращать символьную строку,
которая обеспечит во всех ваших случаях правильную сортировку.
Затем, вы можете использовать динамический SQL, это я считаю
самым простым способом; или же, если у вас немного столбцов, статический
SQL-оператор, открываемый в блоке IF/THEN/ELSIF. Сначала вариант с динамическим
SQL: begin
l_query := 'select ...';
if (l_sort_by = '1') then
l_query := l_query || 'ename';
elsif (l_sort_by = '2') then
l_query := l_query || 'empno';
...
end if;
open l_cursor for l_query;
Следующий пример со статическим SQL не слишком отличается и
был бы выбран мною в случае использования не более двух или трех предложений
ORDER BY (по многим причинам статический SQL предпочтительнее динамического
SQL): begin
if l_sort_by = '1'
then
open l_cursor
for select ...
order by ename;
else
open l_cursor
for select ...
order by empno;
end if;
loop
fetch l_cursor into ...;
exit when l_cursor%notfound;
Реорганизация в среде Oracle 10g
Вопрос. Вы упоминали, что сервер Oracle Database
10 g, в некоторых ситуациях, таких, как удаление каждой второй строки таблицы,
перераспределяет пространство. Пожалуйста, поясните.
Ответ. Оперативное сжатие сегментов может
выполняться для таблиц, находящимся в табличных пространствах с режимом
автоматического управления пространством хранения сегментов (ASSM, Automatic
Segment Space Management). Концептуально речь идет о том, что сервер Oracle
читает таблицу, начиная с ее конца, обнаружив внизу таблицы строки, сервер
удаляет их и повторно вставляет их в вверху таблицы. Когда свободное
пространство в вверху заканчивается, сервер останавливается, оставляя все
свободное пространство в низу таблицы. Затем сервер Oracle переписывает маркер
максимального заполнения (high-water mark) этой таблицы и освобождает это
выделенное пространство. Быстрый пример: SQL> create table t
2 ENABLE ROW MOVEMENT
3 as
4 select *
5 from all_objects;
Table created.
Здесь я создал таблицу с предложением ENABLE ROW MOVEMENT
(разрешить перемещение строк). Сервер Oracle будет физически перемещать строки,
и это предложение разрешает серверу изменять идентификаторы строк (rowid). Вот
что происходит при полном просмотре этой большой таблицы: SQL> set autotrace on statistics
SQL> select count(*) from t;
COUNT(*)
-----------
47266
Statistics
-------------------------------
0 db block gets
724 consistent gets
651 physical reads
SQL> set autotrace off
Для чтения этой таблицы и подсчета числа строк потребовалось
724 операции логического ввода-вывода (consistent gets). Быстрый взгляд на
представление словаря данных USER_EXTENTS (экстенты сегментов текущего
пользователя) показывает, что таблица занимает 768 блоков в 20 экстентах.
Спустя какое-то время я кое-что удалил из таблицы, оставив
кучу незаполненного пространства. Я буду имитировать это, удаляя каждую вторую
строку таблицы: SQL> delete from t
2 where mod(object_id,2) = 0;
23624 rows deleted.
|
Следующие шаги
СПРАШИВАЙТЕ Тома
Том Кайт – вице-президент группы
Oracle Government, Education, and Healthcare – отвечает на наиболее
трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие
материалы этого форума публикуются в данной колонке.
ЧИТАЙТЕ еще о индексах по
ключам-функциям |
Теперь я хочу вернуть это незаполненное пространство, отняв
его у таблицы, для использования, возможно, в других объектах или, может быть, я
часто выполняю полный просмотр этой таблицы, и просто хочу, чтобы она была
меньше. До сервера Oracle Database 10g были только такие варианты, как
пересоздание таблицы утилитами EMP/IMP и оператором ALTER TABLE MOVE или же ее
оперативное переопределение. В сервере Oracle Database 10g я могу
уплотнять (compact) и сжимать (shrink) ее: SQL> alter table t shrink space compact;
Table altered.
SQL> alter table t shrink space;
Table altered.
Еще один взгляд на представление USER_EXTENTS показывает, что
теперь эта таблица занимает 320 блоков в 17 экстентах. Таблица действительно
была сжата без повторного создания, оставаясь по-прежнему в оперативном режиме.
Ее исходный размер в блоках уменьшился на половину, поскольку освобожденные
экстенты возращены назад в систему –ранее этого делать было нельзя. Более того,
посмотрите, что это сжатие делает для полного просмотра: SQL> select count(*) from t;
COUNT(*)
-----------
23642
Statistics
-------------------------------
0 db block gets
409 consistent gets
62 physical reads
Количество операций ввода-вывода теперь соответствует
фактическому размеру данных.
Ведущий данной колонки Том Кайт (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 г.).
| 
