(On Favorites and CONNECT BY, by Tom Kyte)
Источник
Наш эксперт делает выбор, развертывает, загружает, вычисляет разность дат и т.д.
Вопрос. Каковы по вашему мнению наиболее предпочтительные особенности сервера базы данных Oracle Database, независимо от версии?
Ответ. На это в действительности очень легко ответить. Прежде, чем я перешел в корпорацию Oracle, особенностью, которая переключила меня на СУБД Oracle и заставила бросить другие СУБД, была модель конкурентного доступа, выразительно описанная в основных концепциях Oracle Database Concepts.
Я не могу вообразить функционирование сервера базы данных без модели конкурентного доступа и согласованности данных Oracle. Если кратко, механизм конкурентного доступа к данным в сервере Oracle позволяет нам иметь "читателей", которые не блокируют "писателей", и "писателей", которые не блокируют "читателей".
Для людей, выполняющих запросы, это выглядит так, как будто они единственные пользователи базы данных – никакого блокирования нет. Механизм согласованности данных предоставляет нам существенное свойство "согласованности по чтению", которое я также называю "атрибутом правильного ответа." Я настоятельно рекомендую всем ознакомиться с приведенной выше ссылкой, чтобы лучше понимать это фундаментальное отличие СУБД Oracle.
Список первой десятки наиболее предпочтительных особенностей сервера Oracle Database 10
g
можно посмотреть в моих презентационных материалах на конференции 2004 Oracle OpenWorld (доступны на сайте asktom.oracle.com во вкладке Files). В этом выступлении я обсуждаю мою первую десятку наиболее предпочтительных новых особенностей сервера Oracle Database 10g
Release 1:
технология выполнения ретроспективных операций; 
повышение производительности PL/SQL-кода; 
среда Oracle HTML DB; 
технология Automatic Storage Management (ASM, автоматическое управление хранением данных); 
пакет управления перезаписью запросов DBMS_ADVANCED_REWRITE; 
профилирование SQL-операторов; 
более легкое оперативное переопределение; 
независящий от регистра поиск; 
уменьшение необходимости в переводе в недействительное состояние; 
оперативное сжатие сегментов.Развертывание иерархии
Вопрос. У меня есть таблица, содержащая простую иерархию:
SQL> DESC test_table;
Name Null? Type
------ ------ --------------
A NUMBER(38)
B NUMBER(38)
Со следующими данными:
SQL> SELECT * FROM test_table;
A B
----------- ----------
1 -1
2 1
3 1
4 2
5 2
6 4
7 4
8 5
9 5
10 3
11 3
11 rows selected.
Мне нужен запрос, который выдаст мне каждый узел и всех его предков (родителей). То есть, то, что я должен получить, выглядит так:
A B
----------- ----------
...
9 9
9 5
9 2
9 1
...
Мне нужно именно это, потому что 9 связана с 9, 9 связана с 5 (в исходной таблице), 9 косвенно связана с 2 (через 5), и т.д. Как я могу добиться этого в запросе?
Ответ. На первый взгляд это действительно кажется тяжело, но сделать это довольно легко в сервере Oracle9
i
Database, а еще легче в сервере Oracle Database 10g
. Мы легко можем получить всю иерархию:SQL> SELECT a
2 FROM test_table
3 CONNECT BY PRIOR b=a
4 /
Это действительно позволяет получить ваш столбец B в желательном результате запроса. Теперь мы должны получить корневой узел каждой из строк в этой иерархии. Используя довольно новую функцию SYS_CONNECT_BY_PATH (сервер Oracle9
i
Database и более поздние версии), мы можем получить каждый корневой узел. Используя следующий запрос, мы можем увидеть то, что функция SYS_CONNECT_BY_PATH (SCBP) возвращает:SQL> SELECT a,
2 SYS_CONNECT_BY_PATH (a,'.') scbp
3 FROM test_table
4 CONNECT BY PRIOR b=a
5 ORDER BY 2
6 /
A SCBP
---------- ---------
...
9 .9
5 .9.5
2 .9.5.2
1 .9.5.2.1
...
Как видите, мы начинаем получать то, что хотим: передняя часть каждого значения SCBP – корень иерархии, а остаток – столбец A. Теперь мы воспользуемся небольшим "волшебством" получения подстрок (функция SUBSTR):
SQL> SELECT a,
2 TO_NUMBER(
3 SUBSTR(scbp,1,
4 INSTR(scbp,'.')-1)
5 ) b
6 FROM (
7 SELECT a,
8 LTRIM(
9 SYS_CONNECT_BY_PATH(a,'.'),
10 '.') ||'.' scbp
11 FROM test_table
12 CONNECT BY PRIOR b=a
13 )
14 ORDER BY 2
15 /
A B
----------- ----------
...
9 9
9 5
9 2
9 1
...
И мы получили то, что нужно. Вы будете рады узнать, что в сервере Oracle Database 10
g
это сделать еще легче. Для запросов с предложением CONNECT BY появилась целая группа новых функций, таких, как:
CONNECT_BY_ROOT – возвращает корень иерархии текущей строки CONNECT BY, эта функция значительно упрощает наш запрос. (Пример см. ниже.); 
CONNECT_BY_ISLEAF – признак, указывающий, что текущая строка имеет дочерние строки; 
CONNECT_BY_ISCYCLE – признак, указывающий, что в вашей иерархии текущая строка является началом бесконечного цикла. Например, если A – родитель B, B – родитель C, а C – родитель A, то у вас будет бесконечный цикл. Вы можете использовать этот признак для определения, какая строка или строки ваших данных являются началом бесконечного цикла; 
NOCYCLE – позволяет в запросе с предложением CONNECT BY распознать, что встретился бесконечный цикл и прекратить выполнение запроса без выдачи ошибки (вместо возврата ошибки зацикливания при выполнении предложения CONNECT BY).Для нашего вопроса важна первая новая функция, CONNECT_BY_ROOT. Следующий запрос работает на нас в сервере Oracle Database 10
g
:SQL> SELECT CONNECT_BY_ROOT a cbr,
2 a b
3 FROM test_table
4 CONNECT BY PRIOR b=a
5 ORDER BY 1
6 /
CBR B
----------- ----------
...
9 9
9 5
9 2
9 1
...
Годы и дни
Вопрос. Я имею таблицу, в которой есть два столбца: DOB (Date of Birth, дата рождения) и DOD (Date of Death, дата смерти). Оба столбца – даты. Я хочу создать представление и вычислять разность дат в точных годах и днях. Например, разность между двумя датами 25-JAN-1910 и 17-JAN-2005 равна 94 годам и 358 дням.
Ответ. Это довольно легко сделать, особенно, если вы знаете, какая из этих двух дат всегда будет больше другой (DOB всегда будет меньше чем DOD). В этом случае вы можете использовать следующее:
SQL> SELECT DOB, DOD,
2 years,
3 DOD-ADD_MONTHS(DOB,years*12) days
4 FROM
5 (
6 SELECT DOB, DOD,
7 TRUNC(MONTHS_BETWEEN(DOD,DOB)
/12)
8 years
9 FROM t
10 )
11 /
DOB DOD YEARS DAYS
------------ ----------- ------ -----
25-JAN-10 17-JAN-05 94 358
Встроенное представление в строке 7 вычисляет число полных лет между этими двумя датами. Мы делаем это, беря число месяцев между этими двумя датами и деля его на 12. (В году всегда 12 месяцев; мы должны использовать месяцы, поскольку число дней в разных годах может отличаться.) Как только мы вычислили число лет между датами, вычислить оставшееся число дней довольно просто. Мы просто добавляем вычисленные года к меньшему значению из этих двух столбцов (DOB) и вычитаем получившуюся дату из большего значения из этих двух столбцов (DOD). Именно это мы сделали в строке 3. Итак, мы вычислили число лет и дней между двумя датами.
Теперь, если мы не знаем, какая дата во всех случаях больше другой, быстрое решение состоит в использовании встроенных функций GREATEST и LEAST:
SQL> SELECT DOB, DOD,
2 ABS(years) years,
3 GREATEST(DOB,DOD)
4 - ADD_MONTHS(LEAST(DOB,DOD),
5 ABS(12*years) ) days
6 FROM
7 (
8 SELECT DOB, DOD,
9 TRUNC(MONTHS_BETWEEN(DOB,DOD)
/12)
10 years
11 FROM t
12 )
13 /
Этот запрос возвращает такой же результат, но он работает с любыми парами дат, независимо от того, какая из них больше другой.
Вызов утилиты SQL Loader из PL/SQL-кода
Вопрос. Есть ли какой-нибудь способ вызова утилиты SQL Loader не на уровне ОС UNIX, а из хранимой процедуры PL/SQL?
Ответ. В былые времена это был ужасный вопрос. В сервере Oracle8
i
Database и более ранних версиях ответ был долгим и запутанным, в него включалось использование хранимых процедур на языке Java или, возможно, внешних процедур на языке C. В любом случае, сделать это было тяжело.В сервере Oracle9
i
Database и более поздних версиях это стало легким делом. Для этого следует использовать внешнюю таблицу, которая предоставляет нам возможность выполнения запроса к файлу, как если бы он был таблицей базы данных. Таким образом, вызов утилиты SQLLDR из SQL- или PL/SQL-кода становится просто оператором INSERT INTO таблица SELECT * FROM внешняя_таблица. То есть, мы используем только язык SQL.Быстрый и легкий способ начать работу с внешними таблицами состоит в том, чтобы взять какие-то ваши старомодные управляющие файлы утилиты SQL Loader (CTL-файлы) и преобразовать их в определения внешних таблиц. Это поможет нам понять синтаксис и получить операторы CREATE TABLE, которые заработают без первоначальных больших усилий. Например, используя поставляемые демонстрационные управляющие файлами из каталога $ORACLE_HOME/rdbms/demo, мы можем ввести в командной строке SQLLDR SCOTT/TIGER ulcase1.ctl external_table=generate_only. В результате будет создан протокольный файл ulcase1.log (фактической загрузки данных не будет; здесь мы попросили, чтобы утилита SQL Loader только сгенерировала бы все SQL-операторы, обеспечивающие загрузку данных при помощи внешней таблицы). В этом протокольном файле мы найдем:
оператор CREATE DIRECTORY. Внешним таблицам требуется объект базы данных типа DIRECTORY (каталог); 
оператор CREATE TABLE для создания внешней таблицы. В него включен ваш преобразованный управляющий файл утилиты SQL Loader; 
оператор INSERT, который выполняет загрузку; 
пара операторов DROP для удаления объектов, созданных предыдущими операторами.Наибольший интерес для вас представляет оператор CREATE TABLE. На листинге 1 показан сгенерированный по файлу ulcase1.ctl оператор CREATE TABLE. Вы можете редактировать его, изменяя имя внешней таблицы, каталога и т.д. Вся тяжелая работа для вас уже сделана.
ЛИСТИНГ 1: сгенерированный оператор создания внешней таблицы.
|
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ulcase1.bad'
LOGFILE 'ulcase1.log_xt'
READSIZE 1048576
SKIP 20
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
DEPTNO CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
DNAME CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
LOC CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'ulcase1.ctl'
)
)REJECT LIMIT UNLIMITED
|
Благодаря внешними таблицам обычная загрузка выполняется оператором INSERT INTO таблица SELECT * FROM внешняя_таблица. А прямая загрузка – оператором INSERT /*+ APPEND */ INTO таблица.... Параллельная загрузка в прямом режиме столь же проста: ALTER TABLE внешняя_таблица PARALLEL и CREATE TABLE новая_таблица PARALLEL AS SELECT * FROM внешняя_таблица. Коме того, в вашем распоряжении вся мощность языка SQL для обработки данных, применения сложных предложений WHERE и т.д. Эти средства лучше утилиты SQL Loader.
Ошибка ORA-01031: недостаточные привилегии
Вопрос. Я написал хранимую процедуру, DISABLECONS, которая динамически отключает ограничения целостности. Она создает оператор ALTER TABLE имя_таблицы DISABLE CONSTRAINT имя_ограничения и, используя оператор EXECUTE IMMEDIATE, выполняет созданный оператор.
Эта процедура разработана для использования различными схемами, имеющими сходные структуры таблиц. Пользователь задает имена таблицы и ограничения, все остальное для схемы делается автоматически. Когда я вызываю эту процедуру в разных схемах, я получаю следующую ошибку:
SQL> EXECUTE common.disablecons('BCBS');
SQL> EXECUTE common.disablecons('BCBS');
BEGIN common.disablecons('BCBS'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "COMMON.DISABLECONS",
line 24
ORA-06512: at line 1
Тем не менее, когда я выполняю мой оператор непосредственно, я не получаю этой ошибки. Почему это происходит?
Ответ. Такие вопросы поступают настолько часто, что на моем сайте появилась страница часто задаваемых вопросов об этом: asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html. Сухой остаток: хранимые процедуры по умолчанию выполняются с основными привилегиями их владельцев (поэтому их называют процедурами с правами владельцев). Основной набор привилегий –это только те привилегии, которые предоставлены владельцу процедуры непосредственно, а также непосредственно группе пользователей PUBLIC. На упомянутой выше странице показано, как протестировать работоспособность вашей процедуры, используя для этого в утилите SQL*Plus оператор SET ROLE NONE.
Отклоняясь от темы, замечу, вы,
возможно
, хотите, чтобы это была процедура с правами вызывающего (с предложением AUTHID CURRENT_USER). Это означает, что если ее вызывает пользователь GEORGE, то она будет выполняться с его привилегиями (роли и так далее), а если MARY, то она будет выполняться с ее привилегиями (роли и так далее). В противном случае, вы (владелец этой процедуры) будете нуждаться в чрезвычайно мощных привилегиях типа ALL, предоставленных непосредственно вам, чтобы вы могли отключать в системелюбые
ограничения. Если вы имеете книгуExpert One on One: Oracle
(Oracle для профессионалов), можете посмотреть в ней, как я более подробно раскрываю эту тему прав вызывающего и владельца, а также когда использовать их. Кроме того, руководство по языку PL/SQL PL/SQL User's Guide and Reference имеет хороший раздел, названный "Invoker Rights Versus Definer Rights".Числа слишком большие
Вопрос. Есть ли какой-нибудь способ возвратить ошибку при преобразовании чисел с плавающей точкой в целые числа? СУБД Oracle автоматически округляет их. Можно ли вместо этого инициировать нарушение ограничения?
Ответ. Следующий код демонстрирует проблему, о которой вы пишете:
SQL> CREATE TABLE t
2 ( X NUMBER (3)
3 );
Table created.
SQL> INSERT INTO t VALUES ( 134 );
1 row created.
SQL> INSERT INTO t VALUES ( 134.1 );
1 row created.
SQL> SELECT * FROM t;
X
----------
134
134
По умолчанию, если вы определяете что-то как, скажем, NUMBER(3), то сервер Oracle Database будет округлять значение, чтобы данные подходили к этому определению, если это возможно. Только если после округления данные не могут подходить, они будут отвергнуты.
Теперь, если мы объявим NUMBER(3) не как определение данных, а как ограничение целостности, мы можем изменить это поведение: <
SQL> CREATE TABLE t
2 ( X NUMBER
3 CHECK
4 (CAST(X AS NUMBER(3)) = x)
5 );
Table created.
SQL> INSERT INTO t VALUES ( 134 );
1 row created.
SQL> INSERT INTO t VALUES ( 134.1 );
insert into t values ( 134.1 )
*
ERROR at line 1:
ORA-02290: check constraint
(X.SYS_C005627) violated
В этом подходе используется встроенная функция CAST для преобразования X в NUMBER(3) и применяется округление. Когда мы сравниваем округленное значение с неокругленным, то они должны быть идентичными, иначе инициируется нарушение ограничения. А если же мы пытаемся вставить число, которое после округления не сможет поместиться в поле NUMBER(3), мы получаем ожидаемую ошибку:
SQL> INSERT INTO t VALUES ( 1234 );
insert into t values ( 1234 )
*
ERROR at line 1:
ORA-01438: value larger than
specified precision allows for
this column
Это число на самом деле слишком большое.
Материализованные представления и подход "сделай сам"
Вопрос. Я должен подготовить документ о материализованных представлениях и таблицах итогов (созданных пользователем). Я хотел бы знать аргументы "за" и "против" каждого из этих подходов.
Ответ. Такого понятия, как созданная пользователем таблица итогов, нет; есть только созданные пользователями таблицы без метаданных о своем содержимом, без какого-либо контроля, управления и администрирования. Я имею ввиду, что таблица T, созданная по 14 таблицам с помощью конструкции SELECT FROM, – только таблица T. Вы не знаете запрос ее определения (эти детали потеряны), вы даже не знаете не является ли информация, содержавшаяся в таблице T, слишком устаревшей (эти детали потеряны); ваш конечный пользователь должен владеть изящным искусством "интеллектуального индексирования" и т.д. Фактически я не вижу ничего положительного в том, чтобы разрешать конечным пользователям создать такие таблицы.
С другой стороны, материализованные представления (МП):
ведут себя как индексы для хранилищ данных. Они используются автоматически когда это целесообразно, и конечные пользователи не должны обучаются их использованию; 
могут рекомендоваться системой (когда она наблюдает за выполняемыми вами запросами, она может подсказать: "Вы знаете, если бы вы вместо этого использовали МП..."); 
содержат метаданные о своем происхождении (запросы определения и зависимости); 
знают, когда они становятся устаревшими; 
знают, как обновлять себя инкрементно; 
могут администрироваться профессионально (пакетное обновление и корректное сопровождение); 
инкрементное обновление даже еще лучше работает в случае секционирования (отслеживание изменений секций).Дополнительные соображения об использовании МП см. в руководстве по хранилищам данных Oracle Data Warehousing Guide. Это руководство имеет очень обширный раздел, посвященный этой технологии.
Как администратор базы данных я не вижу никаких преимуществ, когда конечные пользователи создают и сопровождают таблицы. Как конечный пользователь, я тоже не вижу никаких преимуществ – если я начну заниматься этим, я просто становлюсь еще одним администратором базы данных.
| < Предыдущая | Следующая > |
|---|
