Re: Determining Table Hierarchy

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
Received on Tue Dec 24 2002 - 09:28:35 CST