Прикрутить Connect by к поиску дерева ссылок (FK)

Кууу

Дата: 26.11.2009 18:44:43

Есть таблица MYSCHEME.MYTABLE
Задача достать список всех таблиц, на которые она ссылается внешними ключами делаю типа так:

SELECT distinct fk.table_name "Исходная таблица", 
       /*fk.constraint_name,                        
          fk.r_constraint_name,
          fk.r_owner,*/
       pkt.table_name "Таблица на которую ссылаемся"
FROM all_constraints fk,
     all_constraints pk,
     all_cons_columns pkt
WHERE fk.constraint_type = 'R' 
  and fk.owner = 'MYSCHEME'
  and fk.table_name IN ('MYTABLE')
  and fk.r_constraint_name=pk.constraint_name
  and fk.r_owner=pk.owner
  and pk.constraint_name=pkt.constraint_name(+)
  and pk.owner=pkt.owner
  and position=1

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

Elic

Дата: 26.11.2009 18:52:08

Кууу

но в поиске что-то не нашел.

STFF

Кууу

Дата: 26.11.2009 19:28:20

Посмотрел - в одних вариантах достается далеко не все дерево:

   select level,
          t.owner, 
          t.table_name, 
          t.constraint_name, 
          t.r_constraint_name
   from all_constraints t
     where t.constraint_type = 'R'
   connect by prior t.constraint_name = t.r_constraint_name
          start with t.table_name = 'MYTABLE'
   order by t.table_name

В других, тоже похоже, что не все или вообще вываливается типа

ORA-01436: цикл CONNECT BY в данных пользователя

  select *
from(
  select cr.constraint_name ref_constraint, col.table_name ref_table, col.column_name ref_col,
    c.constraint_name, c.table_name, colr.column_name col
  from all_constraints c, all_constraints cr,  all_cons_columns col, all_cons_columns colr
  where c.constraint_type = 'R'
     and( c.r_constraint_name = cr.constraint_name and c.r_owner = cr.owner
     and c.r_constraint_name = col.constraint_name and c.r_owner = col.owner
     and c.constraint_name = colr.constraint_name and c.owner = colr.owner)
   )
start with ref_table = 'MYTABLE'
CONNECT BY PRIOR table_name = ref_table

_Alex_SMIRNOV_

Дата: 26.11.2009 19:50:00

   SELECT DISTINCT owner, table_name
     FROM dba_constraints t
  START WITH  owner = 'OWNER' AND table_name = 'TABLE' AND constraint_type IN ('P', 'U')
 CONNECT BY NOCYCLE PRIOR owner = r_owner AND PRIOR constraint_name = r_constraint_name AND constraint_type = 'R'

Кууу

Дата: 26.11.2009 20:31:14

_Alex_SMIRNOV_, что-то мне подсказывает, что так мы получим далеко не всю иерархию и думаю в этом со мной многие согласятся.
Этот запрос

  select *
from(
  select cr.constraint_name ref_constraint, col.table_name ref_table, col.column_name ref_col,
    c.constraint_name, c.table_name, colr.column_name col
  from all_constraints c, all_constraints cr,  all_cons_columns col, all_cons_columns colr
  where c.constraint_type = 'R'
     and( c.r_constraint_name = cr.constraint_name and c.r_owner = cr.owner
     and c.r_constraint_name = col.constraint_name and c.r_owner = col.owner
     and c.constraint_name = colr.constraint_name and c.owner = colr.owner)
   )
start with ref_table = 'MYTABLE'
CONNECT BY PRIOR table_name = ref_table

все-таки ближе всего к истине, но он будет вылетать в случае перекрестных когда две таблицы ссылаются друг на друга и в случае когда таблица ссылается сама на себя, возможно, я неправ и в этом случае он не должен вылетать, но почему-то вылетает я пока не пойму почему, думаю, что из-за этого,
Как отсечь ссылки таблиц самой на себя это понятно:
and c.table_name<>col.table_name
,
а как отсечь эти перекрестные ссылки пока не пойму.

Кууу

Дата: 27.11.2009 10:16:59

А вообще это будет неполная картина тогда - хотелось бы все-таки чтобы дерево вывелось вместе с перекрестными ссылками и ссылками таблиц на саму себя. Только как написать Connect By, чтобы он не зациливался тогда на этом

тик

Дата: 27.11.2009 10:32:22

Кууу

Посмотрел - в одних вариантах достается далеко не все дерево:

   select level,
          t.owner, 
          t.table_name, 
          t.constraint_name, 
          t.r_constraint_name
   from all_constraints t
     where t.constraint_type = 'R'
   connect by prior t.constraint_name = t.r_constraint_name
          start with t.table_name = 'MYTABLE'
   order by t.table_name

В других, тоже похоже, что не все или вообще вываливается типа

ORA-01436: цикл CONNECT BY в данных пользователя

  select *
from(
  select cr.constraint_name ref_constraint, col.table_name ref_table, col.column_name ref_col,
    c.constraint_name, c.table_name, colr.column_name col
  from all_constraints c, all_constraints cr,  all_cons_columns col, all_cons_columns colr
  where c.constraint_type = 'R'
     and( c.r_constraint_name = cr.constraint_name and c.r_owner = cr.owner
     and c.r_constraint_name = col.constraint_name and c.r_owner = col.owner
     and c.constraint_name = colr.constraint_name and c.owner = colr.owner)
   )
start with ref_table = 'MYTABLE'
CONNECT BY PRIOR table_name = ref_table

Добавте к своему запросу NOCYCLE и у вас не будет ошибки "ORA-01436: цикл CONNECT BY в данных пользователя"

Кууу

Дата: 27.11.2009 11:18:48

тик, замечательно

(select distinct 
       ref_constraint, ref_table, ref_col,
       constraint_name, table_name, col
from(
  select cr.constraint_name ref_constraint, col.table_name ref_table, col.column_name ref_col,
    c.constraint_name, c.table_name, colr.column_name col
  from all_constraints c, 
       all_constraints cr,  
       all_cons_columns col, 
       all_cons_columns colr
  where c.constraint_type = 'R'
     and( c.r_constraint_name = cr.constraint_name and c.r_owner = cr.owner
     and c.r_constraint_name = col.constraint_name and c.r_owner = col.owner
     and c.constraint_name = colr.constraint_name and c.owner = colr.owner)
   )
start with table_name = 'MYTABLE'
CONNECT BY NOCYCLE PRIOR table_name=ref_table)

Уже совсем близко к истине, но еще хотелось бы вывести информацию именно в виде дерева,
то есть каким-то образом заюзать LEVEL, но так как есть циклы
NOCYCLE насколько я понял спасает от самой ошибки зацикливания, но он сильно дублирует данные, то есть допустим таблица ссылается сама на себя с NOCYCLE она выведет эту ссылку один раз, но за ней потянет еще раз все ссылки на которые та ссылается в итоге цикл все равно есть, но он прерывается после первой итерации однако это добавляет море лишних записей - отсек их по DISTINCT-у, но вот как построить именно в виде дерева - LEVEL по дистинкту то не прокатит :( ?
Типа MyTable
- MyTable7
- MyTable3
- MyTable5
- MyTable
......

Кууу

Дата: 30.11.2009 16:28:01

И еще интересный момент - данный запрос смотрит и "вверх" и "вниз" по ссылкам, а мне нужно только вниз, начиная от исходной таблицы беру все ее FK получю список дочерних таблиц потом беру их FK и т.д.

Кууу

Дата: 30.11.2009 18:00:01

Вроде поправил по лигике так вот должно быть:

(select distinct 
       ref_constraint, ref_table, ref_col,
       constraint_name, table_name, col
from(
  select cr.constraint_name ref_constraint, col.table_name ref_table, col.column_name ref_col,
    c.constraint_name, c.table_name, colr.column_name col
  from all_constraints c, 
       all_constraints cr,  
       all_cons_columns col, 
       all_cons_columns colr
  where c.constraint_type = 'R'
     and( c.r_constraint_name = cr.constraint_name and c.r_owner = cr.owner
     and c.r_constraint_name = col.constraint_name and c.r_owner = col.owner
     and c.constraint_name = colr.constraint_name and c.owner = colr.owner)
   )
start with table_name = 'MYTABLE'
CONNECT BY NOCYCLE PRIOR ref_table=table_name)

Но что-то пока не дождусь конца выполнения запроса