Документация Oracle на русском языке





Сайт посвящен разработке информационных систем с использованием технологий Oracle. На сайте можно найти полезную литературу и документацию на русском языке по программированию и администрированию Oracle.Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации.

Главная :: Карта


Oracle Database или Oracle RDBMS — объектно-реляционная система управления базами данных компании Oracle.



 

DeepEdit!

Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

Том Кайт: о наиболее предпочтительных особенностях и предложении CONNECT BY


(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 10

g

 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 10

g

. Мы легко можем получить всю иерархию: 
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. Это руководство имеет очень обширный раздел, посвященный этой технологии. 
Как администратор базы данных я не вижу никаких преимуществ, когда конечные пользователи создают и сопровождают таблицы. Как конечный пользователь, я тоже не вижу никаких преимуществ – если я начну заниматься этим, я просто становлюсь еще одним администратором базы данных. 
 


Картина сваровски жираф. . Световые короба, в.т ч. Двусторонние Краснодар
jAntivirus