Oracle Relationship Query Query

Question

Oracle Relationship Query Query

Navigation

#1 by (2 votes)

3

I have a program to look for records dependencies to proceed with deletion of data here in the company. We support some banks but in Oracle the query is processed very slowly (about 10 to 15 seconds for each table in remote production banks).

Query Queries

Parents Tables

SELECT DISTINCT c_pk.table_name table_name, CAST(a.column_name AS   
         VARCHAR(100)) as MainColumnName, a.constraint_name, a.position,     
         a.table_name as r_table_name, c_pk.constraint_name r_pk,            
         CAST((SELECT ax.column_name FROM all_cons_columns ax                
         WHERE ax.owner = c_pk.owner                                         
               AND ax.constraint_name = c_pk.constraint_name                 
               AND ax.position = a.position) as VARCHAR(100)) as column_name 
    FROM all_cons_columns a                                                  
    INNER JOIN all_constraints c ON a.owner = c.owner                        
                          AND a.constraint_name = c.constraint_name          
    INNER JOIN all_constraints c_pk ON c.r_owner = c_pk.owner                
                             AND c.r_constraint_name = c_pk.constraint_name  

   WHERE c.constraint_type = ''R''                                           
     AND a.table_name = ''%s''                                               
  ORDER BY                                                                   
        table_name, constraint_name, position

Daughter Tables

 SELECT a.table_name, CAST(a.column_name AS VARCHAR(100)) as
       column_name, a.constraint_name, a.position,                           
       c_pk.table_name r_table_name,  c_pk.constraint_name r_pk,             
       CAST((SELECT ax.column_name FROM all_cons_columns ax                  
       WHERE ax.owner = c_pk.owner                                           
             AND ax.constraint_name = c_pk.constraint_name                   
             AND ax.position = a.position) as VARCHAR(100)) as MainColumnName
  FROM all_cons_columns a                                                    
  JOIN all_constraints c ON a.owner = c.owner                                
                        AND a.constraint_name = c.constraint_name            
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner                        
                           AND c.r_constraint_name = c_pk.constraint_name    
 WHERE c.constraint_type = ''R''                                             
   AND c_pk.table_name = ''%s''                                              
ORDER BY                                                                     
      table_name, constraint_name, position

Is there a faster way to view this data?

desempenho oracle query

asked by anonymous 04.06.2014 / 14:18

1 answer

I need help creating a blocking pattern for Android using the patternLock plugin Android MediaPlayer class warning: Should have subtitle controller already set

score 2 · Accepted Answer

Try to use the PARALLEL hint and tell me if the query executes fast:

link

 SELECT
       /*+ PARALLEL(all_cons_columns 2) PARALLEL(all_constraints 2) */ 
       a.table_name, CAST(a.column_name AS VARCHAR(100)) as
       column_name, a.constraint_name, a.position,                           
       c_pk.table_name r_table_name,  c_pk.constraint_name r_pk,             
       CAST((SELECT ax.column_name FROM all_cons_columns ax                  
       WHERE ax.owner = c_pk.owner                                           
             AND ax.constraint_name = c_pk.constraint_name                   
             AND ax.position = a.position) as VARCHAR(100)) as MainColumnName
  FROM all_cons_columns a                                                    
  JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name            
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name    
 WHERE c.constraint_type = ''R''                                             
   AND c_pk.table_name = ''%s''                                              
ORDER BY                                                                     
      table_name, constraint_name, position