Re: Determining Table Hierarchy

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:00:45 CST