Re: Determining Table Hierarchy

Now, that is an asinine response,

Of course there is value in pure sql. Why would I want to write programmatic logic to recursively go up the tree, and keep track of my position and level in the tree if I could do it in a single statement. Furthermore, its going to perform a whole lot faster if Oracle does it than if I do it (regardless of whether I use pl/sql or some language living outside the db).

Why I'm answering you, is beyond me.

To the others responders so far, this kinda works. If I don't limit the owners I get a:

ORA-01436: CONNECT BY loop in user data

SELECT MAX(LEVEL), TABLE_NAME
FROM (
select
A.TABLE_NAME TABLE_NAME, B.TABLE_NAME PARENT_TABLE FROM
    all_constraints a,
    all_constraints b
WHERE
    a.r_constraint_name = b.constraint_name (+)     and a.r_owner = b.owner(+)
    and a.constraint_type in ('R','P')
-- and a.owner in ('ME')
)

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E09033C.954F5773_at_exesolutions.com...
> Jake wrote:
>
> > Spendius,
> >
> > Thanks for the answer, but I'm looking for a pure sql method. I think
it
> > should be doable with connect by.
> >
> > Jake
> > "Spendius" <spendius_at_muchomail.com> wrote in message
> > news:aba30b75.0212240545.52e501a1_at_posting.google.com...
> > > Run this script with a schema given as parameter (you can
> > > give a table name as 2nd parameter):
> > >
> > > D:\work\oracle\sql>type tree.sql
> > > set serveroutput on
> > > set trimsp on
> > > set ver off
> > >
> > > CREATE TABLE tables_dependencies
> > > (root_table VARCHAR2(30),
> > > node_table VARCHAR2(30),
> > > rank NUMBER,
> > > parent_rank NUMBER)
> > > TABLESPACE tools PCTUSED 40 INITRANS 1 MAXTRANS 255
> > > STORAGE (INITIAL 512K NEXT 512K PCTINCREASE 0
> > > MINEXTENTS 1 MAXEXTENTS 10)
> > > /
> > >
> > > -- following lines give only ROOT tables (those
> > > -- that don't have PK-pointing foreign keys)
> > > -- (uncomment them not: this is a reminder)
> > > -- + select table_name
> > > -- + from dba_constraints c1
> > > -- + where owner='&&1'
> > > -- + and constraint_type='P'
> > > -- + and not exists (select NULL
> > > -- + from dba_constraints c3
> > > -- + where -- c1.constraint_name =
> > > c3.r_constraint_name
> > > -- + c1.owner = c3.r_owner
> > > -- + and c1.table_name = c3.table_name
> > > -- + --
> > > -- + and c3.constraint_type = 'R')
> > >
> > > -- step 1
> > > prompt 2nd argument = TABLE_NAME (optional)
> > > INSERT INTO tables_dependencies
> > > SELECT c1.table_name,
> > > c2.table_name,
> > > 1, NULL
> > > FROM dba_constraints c1,
> > > dba_constraints c2
> > > WHERE c1.owner = UPPER('&&1') -- 'EXT528'
> > > and c1.table_name LIKE UPPER('%&&2%')
> > > AND c1.constraint_type = 'P'
> > > AND c1.constraint_name = c2.r_constraint_name
> > > AND c1.owner = c2.r_owner
> > > --
> > > -- if following lines removed, you get all schema's tables
> > > -- that have FK dependencies pointing towards them (and not
> > > -- only 'root' tables of the tree-structure)
> > > AND NOT EXISTS (SELECT NULL
> > > FROM dba_constraints c3
> > > WHERE c1.owner = c3.r_owner
> > > AND c1.table_name = c3.table_name
> > > AND c3.constraint_type = 'R');
> > > --
> > > -- following line gotta be removed against 7.* versions:
> > > -- ORDER BY 1, 2;
> > >
> > > -- step L_RANK's
> > > -- (remark: the RANK column is the equivalent of the LEVEL
> > > -- pseudo-column)
> > > DECLARE
> > > l_cnt NUMBER := 0;
> > > l_rank NUMBER := 1;
> > > BEGIN
> > >
> > > SELECT COUNT(DISTINCT table_name) INTO l_cnt
> > > FROM dba_constraints c,
> > > tables_dependencies td
> > > WHERE c.table_name = td.node_table
> > > AND c.constraint_type = 'P'
> > > and c.owner = UPPER('&&1')
> > > AND td.rank = l_rank;
> > >
> > > WHILE l_cnt > 0 LOOP
> > >
> > > l_rank := l_rank + 1;
> > > dbms_output.put_line('level = '||l_rank||' of depth, inserting
> > > '||l_cnt||' rows.');
> > >
> > > INSERT INTO tables_dependencies
> > > SELECT c1.table_name root_table,
> > > c2.table_name node_table,
> > > l_rank, l_rank - 1
> > > FROM dba_constraints c1,
> > > dba_constraints c2
> > > WHERE c1.owner = UPPER('&&1')
> > > AND c1.constraint_type = 'P'
> > > AND c1.constraint_name = c2.r_constraint_name
> > > AND c1.owner = c2.r_owner
> > > AND EXISTS (SELECT NULL
> > > FROM tables_dependencies td
> > > WHERE c1.table_name = td.node_table
> > > AND td.rank = l_rank-1)
> > > AND NOT EXISTS (SELECT NULL
> > > FROM tables_dependencies td
> > > WHERE c1.table_name = td.root_table);
> > > -- AND td.rank = l_rank-1);
> > >
> > > SELECT COUNT(DISTINCT table_name) INTO l_cnt
> > > FROM dba_constraints c,
> > > tables_dependencies td
> > > WHERE c.table_name = td.node_table
> > > AND c.constraint_type = 'P'
> > > AND c.owner = UPPER('&&1')
> > > AND rank = l_rank;
> > > END LOOP;
> > >
> > > END;
> > > /
> > >
> > > -- output step
> > > col root_table format a25
> > > col node_table format a25
> > > set pages 100
> > > set lines 90
> > >
> > > break on rank skip 1 on root_table
> > >
> > > spool tree2
> > > SELECT *
> > > FROM tables_dependencies
> > > ORDER BY rank, root_table, node_table
> > > /
> > > spool off
> > >
> > > spool tree2primKeysCnt
> > > select root_table, count(1)
> > > from tables_dependencies
> > > group by root_table
> > > order by 2
> > > /
> > > spool off
> > >
> > > spool tree2foreignKeysCnt
> > > select node_table, count(1)
> > > from tables_dependencies
> > > group by node_table
> > > order by 2
> > > /
> > > spool off
> > >
> > > -- echo "Show all occurences of: c"
> > > -- read tabName
> > > -- grep $tabName tree2.lst
> > > --
> > > -- echo "Show PRIM. KEY occurences of: $tabName"
> > > -- grep "^$tabName" tree2.lst
> > > --
> > > -- echo "Show FOREIGN KEY occurences of $tabName"
> > > -- grep "^......................... $tabName" tree2.lst
> > > --
> > > -- echo "$tabName appears N times as PARENT table:"
> > > -- grep "$tabName" tree2primKeysCnt.lst
> > > --
> > > -- echo "$tabName appears N times as CHILD table:"
> > > -- grep "$tabName" tree2foreignKeysCnt.lst
> > > --
> > >
> > > DROP TABLE TABLES_DEPENDENCIES
> > > /
> > >
> > >
> > > "Jake" <Im_at_nottelling.com> wrote in message
> > news:<au8n76$pbv$1_at_bob.news.rcn.net>...
> > > > Hi,
> > > >
> > > > I want to select a list of tables in order of RI. That is, I want
to
> > select
> > > > all the root tables first, then the children of those tables and so
> > on...
> > > > I'm sorry if this is a frequent post, but I made various
groups.google
> > > > searches and found nothing.
> > > >
> > > > I want to do this for various reasons, not the least is to know
which
> > tables
> > > > to add data to first so as not to conflict with RI
> > > >
> > > > I thought that:
> > > >
> > > > 1 select table_name, max(level)
> > > > 2 from user_constraints
> > > > 3 connect by prior CONSTRAINT_NAME = R_CONSTRAINT_NAME
> > > > 4* group by table_name
> > > > SQL> /
> > > >
> > > > TABLE_NAME MAX(LEVEL)
> > > > ------------------------------ ----------
> > > > CHILD 2
> > > > CHILD2 2
> > > > GRANDCHILD1 2
> > > > GRANDCHILD2 2
> > > > GREATGRAND 2
> > > > PARENT 1
> > > >
> > > > Would work but the level isn't right.
> > > >
> > > > It should return:
> > > >
> > > > TABLE_NAME MAX(LEVEL)
> > > > ------------------------------ ----------
> > > > GREATGRAND 4
> > > > GRANDCHILD1 3
> > > > GRANDCHILD2 3
> > > > CHILD 2
> > > > CHILD2 2
> > > > PARENT 1
> > > >
> > > >
> > > > Can someone point me in the right direction. I built a test set of
> > tables
> > > > like:
> > > >
> > > > create table PARENT
> > > > (
> > > > PARENTID Number not null,
> > > > constraint PK_PARENT primary key (PARENTID)
> > > > )
> > > > /
> > > >
> > > > create table CHILD
> > > > (
> > > > CHILDID number not null,
> > > > PARENTID Number null ,
> > > > constraint PK_CHILD primary key (CHILDID)
> > > > )
> > > > /
> > > >
> > > > create table CHILD2
> > > > (
> > > > CHILDID Number not null,
> > > > PARENTID Number null ,
> > > > constraint PK_CHILD2 primary key (CHILDID)
> > > > )
> > > > /
> > > >
> > > > create table GRANDCHILD1
> > > > (
> > > > GRANDCHILDID Number not null,
> > > > CHILDID number null ,
> > > > PARENTID Number null ,
> > > > constraint PK_GRANDCHILD1 primary key (GRANDCHILDID)
> > > > )
> > > > /
> > > >
> > > > create table GRANDCHILD2
> > > > (
> > > > GRANDCHILDID Number not null,
> > > > CHILDID Number null ,
> > > > constraint PK_GRANDCHILD2 primary key (GRANDCHILDID)
> > > > )
> > > > /
> > > >
> > > > create table GREATGRAND
> > > > (
> > > > GREATGRANDID Number not null,
> > > > GRANDCHILDID Number null ,
> > > > CHILDID Number null ,
> > > > GRA_GRANDCHILDID Number null ,
> > > > constraint PK_GREATGRAND primary key (GREATGRANDID)
> > > > )
> > > > /
> > > >
> > > > alter table CHILD
> > > > add constraint FK_CHILD_REF_7_PARENT foreign key (PARENTID)
> > > > references PARENT (PARENTID)
> > > > /
> > > >
> > > > alter table CHILD2
> > > > add constraint FK_CHILD2_REF_22_PARENT foreign key (PARENTID)
> > > > references PARENT (PARENTID)
> > > > /
> > > >
> > > > alter table GRANDCHILD1
> > > > add constraint FK_GRANDCHI_REF_13_CHILD foreign key (CHILDID)
> > > > references CHILD (CHILDID)
> > > > /
> > > >
> > > > alter table GRANDCHILD1
> > > > add constraint FK_GRANDCHI_REF_17_PARENT foreign key (PARENTID)
> > > > references PARENT (PARENTID)
> > > > /
> > > >
> > > > alter table GRANDCHILD2
> > > > add constraint FK_GRANDCHI_REF_27_CHILD2 foreign key (CHILDID)
> > > > references CHILD2 (CHILDID)
> > > > /
> > > >
> > > > alter table GREATGRAND
> > > > add constraint FK_GREATGRA_REF_33_GRANDCHI foreign key
> > (GRANDCHILDID)
> > > > references GRANDCHILD1 (GRANDCHILDID)
> > > > /
> > > >
> > > > alter table GREATGRAND
> > > > add constraint FK_GREATGRA_REF_37_CHILD2 foreign key (CHILDID)
> > > > references CHILD2 (CHILDID)
> > > > /
> > > >
> > > > alter table GREATGRAND
> > > > add constraint FK_GREATGRA_REF_41_GRANDCHI foreign key
> > > > (GRA_GRANDCHILDID)
> > > > references GRANDCHILD2 (GRANDCHILDID)
> > > > /
> > > >
> > > > Thanks
> > > > Jake
>
> There is no value in a pure SQL method unless the point of this is that
your
> work is an academic exercise assigned by an instructor ... in which case
> those of us in the forum should not be doing your homework for you.
>
> Please clarify the reason for wanting this if it is not a school
assignment.
>
> Daniel Morgan
>
Received on Tue Dec 24 2002 - 19:14:09 CST