| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Determining Table Hierarchy
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)
/
tables_dependencies td
WHERE c.table_name = td.node_table
AND c.constraint_type = 'P'
and c.owner = UPPER('&&1')
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;
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
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 > JakeReceived on Tue Dec 24 2002 - 07:45:58 CST
 |
 |