Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Февраль 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : Поиск связей главная-подчиненная между таблицами


Информационный Канал Subscribe.Ru

Выпуск 27

Поиск связей главная-подчиненная между таблицами

Уважаемые подписчики! Большое спасибо всем, кто откликнулся на мою просьбу как-то прокомментировать работу рассылки. Тем из вас, кто писал в гостевую, я постарался ответить. Остальным, возможно, еще отвечу подробнее. Вероятно, в одном из следующих выпусков подведу и формальные итоги. Пока же прокомментирую полученные отзывы так.

Большинство ответивших считает, что выпуски, посвященные формальному синтаксису, не нужны. Тем не менее, они изредка будут выходить, хотя бы потому, что представленные в документации синтаксические диаграммы во многих случаях неудобно использовать.

Рассылка не предназначалась для начинающих. Но, я собираюсь опубликовать серию выпусков об основных концепциях Oracle, основных задачах администрирования и способах их решения.

Рассылка никогда не станет коммерческой или платной. Если вы, однако, очень хотите срочно заказать авторский материал или перевод по какой-то теме, связанной с СУБД Oracle, и готовы оплатить эту работу - шлите соответствующее письмо мне по электронной почте. Возможно, мы договоримся о приемлемых условиях сотрудничества.

Этот выпуск посвящен поиску всех отношений главная-подчиненная между таблицами базы данных. Последний раз Том Кайт вернулся к этому вопросу 10 января 2003 года. Выпуск выходит намного позже обещанного, но тому были свои причины, о которых можно почитать здесь.

Получение списка всех отношений главная-подчиненная между таблицами.

Том!

Не мог бы ты объяснить, как получить список всех отношений главная-подчиненная между таблицами базы данных.

В списке сначала должны идти таблицы, не являющиеся подчиненными, а в конце - не имеющие подчиненных.

Например,

 
Главная                         Подчиненная 
---------------------------------------------------------------- 
 BDP_INFO                       BAA 
 CONTR                          ABP 
 CONTR                          B_INCE 
 CONTR                          BDP_INFO

Таблица BDP_INFO является главной по отношению к BAA, и одновременно подчиненной для CONTR. Поэтому список должен иметь следующий вид:

 
Contr ---------> BDP_info 
BDP_info ------> BAA 
 ...

Такой список надо построить для всех таблиц, к которым имеет доступ данный пользователь.

Ответ Тома Кайта

Если НЕТ требований целостности, ссылающихся на ту же таблицу:

 
create table emp (empno int primary key, mgr int references emp(empno));

подойдет следующее решение:

Для упрощения решения необходимо использовать временную таблицу -- нельзя выполнять запрос connect by по соединению, поэтому необходимо скопировать user_constraints:

 
tkyte@TKYTE816> create table p (x int primary key); 
 
Table created. 
 
tkyte@TKYTE816> create table c1 (x primary key references p); 
 
Table created. 
 
tkyte@TKYTE816> create table c2 (x primary key references c1); 
 
Table created. 
 
tkyte@TKYTE816> create table c3 (x primary key references c2); 
 
Table created. 
 
tkyte@TKYTE816> create table c4 (x primary key references c2); 
 
Table created. 
 
tkyte@TKYTE816> create global temporary table temp_constraints 
  2  as 
  3  select a.table_name, 
  4         a.constraint_name pkey_constraint, 
  5             b.constraint_name fkey_constraint, 
  6             b.r_constraint_name 
  7    from user_constraints a, user_constraints b 
  8   where 1=0 
  9  / 
 
Table created. 
 
tkyte@TKYTE816> alter table temp_constraints modify fkey_constraint NULL; 
 
Table altered. 
 
tkyte@TKYTE816> delete from temp_constraints; 
 
0 rows deleted. 
 
tkyte@TKYTE816> insert into temp_constraints 
  2  select table_name, constraint_name, null, null from user_constraints where constraint_type 
  3  = 'P'; 
 
5 rows created. 
 
tkyte@TKYTE816> insert into temp_constraints 
  2  select a.table_name, 
  3         a.constraint_name pkey_constraint, 
  4             b.constraint_name fkey_constraint, 
  5             b.r_constraint_name 
  6    from user_constraints a, user_constraints b 
  7   where a.table_name = b.table_name 
  8     and a.constraint_type = 'P' 
  9     and b.constraint_type = 'R' 
 10  / 
 
4 rows created. 
 
tkyte@TKYTE816> column table_name format a2 
tkyte@TKYTE816> select * from temp_constraints; 
 
TA PKEY_CONSTRAINT                FKEY_CONSTRAINT                 
R_CONSTRAINT_NAME 
-- ------------------------------ ------------------------------  
------------------------------ 
C1 SYS_C004927 
C2 SYS_C004929 
C3 SYS_C004931 
C4 SYS_C004933 
P  SYS_C004926 
C1 SYS_C004927                    SYS_C004928                    SYS_C004926 
C2 SYS_C004929                    SYS_C004930                    SYS_C004927 
C3 SYS_C004931                    SYS_C004932                    SYS_C004929 
C4 SYS_C004933                    SYS_C004934                    SYS_C004929 
 
9 rows selected. 
 
tkyte@TKYTE816> column table_name format a10 
tkyte@TKYTE816> select rpad('*', (level-1)*2, '*') || table_name table_name 
  2    from temp_constraints 
  3    start with fkey_constraint is null 
  4   connect by prior pkey_constraint = r_constraint_name 
  5  / 
 
TABLE_NAME 
---------- 
C1 
**C2 
****C3 
****C4 
C2 
**C3 
**C4 
C3 
C4 
P 
**C1 
****C2 
******C3 
******C4 
 
14 rows selected.

Как получить операторы ЯОД по словарю данных?

Том, я был бы очень благодарен, если бы ты подсказал, как написать сценарий, выбирающий операторы ЯОД, CREATE TABLE и т.д. для всех доступных объектов из словаря данных.

Я знаю, что могу получить эту информацию с помощью утилиты EXP, но я хотел бы по-своему сформатировать результат.

Если я не ошибаюсь, где-то на сайте Oracle есть похожий сценарий. Не пришлете ссылку?

Ответ Тома Кайта

Лично я о таком сценарии ничего не знаю -- я использую утилиту EXP (на самом деле, я использую утилиту exp ТОЛЬКО для получения информации о чужих схемах, поскольку я всегда поддерживаю свои сценарии в актуальном состоянии и выбирать операторы ЯОД из базы мне не нужно)...

В любом случае, в версии 9i есть соответствующая возможность:

 
scott@ORA9I.WORLD> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual; 
 
DBMS_METADATA.GET_DDL('TABLE','EMP') 
-------------------------------------------------------------------------------- 
 
  CREATE TABLE "SCOTT"."EMP" 
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
        "ENAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0), 
         CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO") 
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"  ENABLE, 
         CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR") 
  REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE, 
         CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO") 
  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE 
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

Ошибка ORA-01436

Я попытался использовать ваше решение и получил следующее сообщение об ошибке.

 
ERROR at line 2: 
ORA-01436: CONNECT BY loop in user data

И еще, нельзя ли указать условие в иерархическом запросе, например, чтобы выбирать информацию только о таблицах, имена которых начинаются с 'HEDGE'

Ответ Тома Кайта

Нет ли в вашей схеме внешних ключей, ссылающихся на ту же таблицу? Если да, представленное решение не сработает (как я и упоминал в ответе)...

Да, а условия в запрос с конструкцией connect by добавить можно:

 
where tname like 'HEDGE%' 
 ... 
start with tname like 'HEDGE%' 
 ... 
connect by prior ..... and tname like 'HEDGE%' 
 ...

Но может оказаться проще сначала скопировать информацию по таблицам с именами типа HEDGE%. Но напомню, что если таблица ссылается сама на себя, получится цикл connect by, и представлденное решение НЕ сработает.

А зачем вставлять в таблицу дважды?

Сначала ты вставляешь в таблицу имена таблиц и ограничений типа 'P', а что ты вставляешь второй раз?

Ответ Тома Кайта

Сначала я вставляю первичные ключи (constraint_type = 'P')

Затем я вставляю информацию о первичных ключах и ссылающихся на них внешних ключах (constraint_type = 'R')

Как исключить ссылки на самих себя

Том, мне кажется, что исключить внешние ключи, ссылающиеся на первичный ключ той же таблицы, несложно:

 
    select a.table_name, 
           a.constraint_name pkey_constraint, 
           b.constraint_name fkey_constraint, 
           b.r_constraint_name 
      from user_constraints a, user_constraints b 
     where a.table_name = b.table_name 
       and a.constraint_type = 'P' 
       and b.constraint_type = 'R' 
       and a.constraint_name != b.r_constraint_name 
/

... если я ничего не упустил, конечно.

Твой запрос не выбирает внешние ключи, ссылающиеся на другие уникальные ключи таблицы, а не на первичный. Ты считаешь, что так задавать внешние ключи неправильно?

Ответ Тома Кайта

Ну, почему же "неправильно" - просто такое нечасто встречается. Можно легко учесть эту ситуацию, добавив функцию decode по полю a.constraint_type, которая будет делать значения P и U "одинаковыми".

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

В следующем выпуске

Результаты опроса подписчиков. Возможно, первый из серии авторских выпусков, посвященных индексам. Или перевод очередного блестящего ответа Тома Кайта... Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное