Solved

Parent child relationship oracle - Struggling to pass with the solutions  found

Posted on 2011-09-02
7
506 Views
Last Modified: 2012-08-28
Hi, I have been trying to figure out the order for the attached list of tables. I have got the list by querying dba constraints tables (PARENT_TABLE, TABLE, CHILD TABLE). But, I need to order them as "Table_name" ==> Table_LEVEL.
Example : deal-1
scenario -1
address-1
deal_address-2 etc.
Thanks much for looking into my problem.
 new-parent-child-tbls.xls
0
Comment
Question by:sarran_v
  • 2
  • 2
7 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 36477827
how did you get 3 levels of tables from dba_constraints?  the foreign keys only have 2 levels


also, you have circular references  such as


DEAL      SCENARIO      DEAL


are you sure your data is correct? and can you describe what the output is supposed to be using a smaller set?
maybe 5 rows of data and what you expect the output to be for those 5 rows?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36478181
I understand this:  while constraints only have 2 levels, each table potentially has a level above it and a level below it.  In other words, the three column list comes from a self join of the constraint table to itself.  

To calculate the level is a recursive operation and I don't think there is any way to do it in Oracle SQL.  You can however, write a recursive procedure that starts from any constraint and climbs up the hierarchy counting parents until no more are found.  However, do note that a table can have more than one constraint and consequently a different level for each one.   In cases where I need to know the level order (such as the correct order to process a series of deletes in a hierarchy (ilel child-up) without violating RI, using the max level of each table has always worked.  


I do agree that you sample query looks suspiciously wrong: the circular references being one example, stipulation_status as a child of stipulation being another.



0
 

Author Comment

by:sarran_v
ID: 36479135
When there are cyclic relationships (DEAL/SCENARIO or STIPULATION/STIPULATION_STATUS), One of the reference columns is null. In these cases, I can disable the constraints and do the operations and enable them. But, I'm not allowed to do the same for all tables.

This is what I used to populate the above xls.

DECLARE
   V_TBL_NM    VARCHAR2 (50);
   V_TBL_LVL   NUMBER;
BEGIN
   FOR I
      IN (SELECT dt.table_name
            FROM dba_tables dt
           WHERE dt.owner = 'DEAL'
                 AND table_name NOT IN
                        (SELECT table_name FROM grand_parent_tbls
                         UNION
                         SELECT table_name FROM grand_child_tbls)
                 AND TABLE_NAME NOT IN
                        ('PURGE_ASSOCIATION',
                         'QUEST_SL_TEMP_EXPLAIN1',
                         'PLAN_TABLE',
                         'TOAD_PLAN_TABLE',
                         'DEPLOYMENT_PROPERTIES',
                         'DEAL_PURGED',
                         'EMAIL_NOTIFICATION',
                         'RECEIVING_VALIDATION'))
   LOOP
      V_TBL_NM := I.TABLE_NAME;

      -- list the child tables which are not grand childs for the non grand parent/child tables
      FOR J IN (SELECT DISTINCT dc1.table_name, dc2.table_name child_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.r_constraint_name = dc1.constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_child_tbls
                                WHERE table_name = dc2.table_name))
      LOOP
         INSERT INTO new_parent_child_tbls (table_name, child_table)
              VALUES (v_tbl_nm, j.child_table);
      END LOOP;

      COMMIT;

      -- -- list the parent tables which are not grand grand parents for the non grand parent/child tables
      FOR k IN (SELECT dc1.table_name, dc2.table_name parent_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.constraint_name = dc1.r_constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_parent_tbls
                                WHERE table_name = dc2.table_name))
      LOOP
         UPDATE new_parent_child_tbls
            SET parent_table = k.parent_table
          WHERE table_name = v_tbl_nm;
      END LOOP;

      COMMIT;
   END LOOP;

   COMMIT;
END;


0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36485007
Hmmm...I'm not completely sure what you are trying to accomplish with that and your custom table that I do not recognize.  However, in a past life I did some work using Oracles constraint table to derive the relationship "levels". So, with advance apologies for capitalization, missing semi-colons, unmatched BEGIN-END's, and other syntax errors, here is some untested code that reconstructed from memory (as I don't have an Oracle instance available, right now).

I'm quite sure it won't work :>).  But, perhaps you can tweak it to a running state and see if it produces something like what you are looking for.  In any case, the WHILE loop that is deriving the level may be useful.
DECLARE
   V_TOP_NM    VARCHAR2 (50);
   V_TOP_OWN   VARCHAR2 (50);
   V_TBL_NM    VARCHAR2 (50);
   V_TBL_OWN   VARCHAR2 (50);
   V_CHI_NM    VARCHAR2 (50);
   V_CHI_OWN   VARCHAR2 (50);
   V_PAR_NM    VARCHAR2 (50);
   V_PAR OWN   VARCHAR2 (50);
   V_TBL_LVL   NUMBER;
BEGIN 
for T IN (
select 
 c.owner as chi_own
,c.table_name as chi_nm
,p.owner as par_own 
,p.TABLE_NAME as par_nm 
,t.owner as tab_own  
,t.TABLE_NAME as tab_nm 
into
from dba_constraints as t 
left join dba_constraints as p
  on t.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
 and t.R_OWNER = p.owner
left join dba constraints c
  on t.constraint_name = c.r_constraint_name
 and t.Owner = c.r_owner 
where
t.constraint_type in ('R','P','U') 
)
--count hierarchy depth
LOOP
   V_TBL_LVL := 0;
   V_TAB_OWN := T.tab_own;
   V_TAB_NM := t.Tab_nm;
   WHILE v_tab_nm IS NOT NULL
     begin
     select p.owner, p.TABLE_NAME 
       into V_Tab_OWN, V_Tab_NM
       from dba_constraints as c inner join dba_constraints as p
         on c.r_owner = p.owner and c.r_constraint_name = p.constraint_name
      where c.constraint_type = 'R';
        and c.owner = v_tbl_own
        and c.table_name = v_tbl_nm;
     V_TBL_LVL := V_TBL_LVL - 1;
     exception
     when NO_DATA_FOUND
          V_Tab_NM := NULL;
     end;
   END LOOP;

--these contain table name, immediate parent, immediate child, top level and depth from top   
   V_TBL_NM    := t.tab_nm;
   V_TBL_OWN   := t.tab_own;
   V_TOP_NM    := v_tab_nm;
   V_TOP_OWN   := v_tab_own;
   V_CHI_NM    := t.chi_nm;
   V_CHI_OWN   := t.chi_own;
   V_PAR_NM    := t.Par_Nm;
   V_PAR OWN   := t.par_own;
--do something here

 
END LOOP;
END;

Open in new window

0
 

Author Comment

by:sarran_v
ID: 36557875
After trying many ways, I have spent two working days to wrap the hierarchy of this complicated OLTP system manually by using the list of parent child tables i have found from the code i tried with hell of temporary tables. Thanks much for your support. It's great that you guyz jump in as soon as you can into the problem.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now