Querying the dictionary Tables

Hello

I am working on a process to move recently updated records from an old  partiton to new partriton before removing the old partition. I have to query the dict tables to get the Parent tables and child tables along with the partiton name and column on which primary or referential integrity is defined. The two queries I am using are :

To get the detail tables of Contacts table:

SELECT distinct(cons.TABLE_NAME) TABLE_NAME,

        conscol.column_name REF_COL_NAME,
        part.partition_name PART_NAME
FROM    ALL_CONSTRAINTS cons,
        ALL_CONSTRAINTS consr,
        ALL_CONS_COLUMNS conscol,
        ALL_TAB_PARTITIONS part
WHERE   part.table_name=cons.table_name
AND     conscol.constraint_name = cons.r_constraint_name
AND     consr.constraint_name = cons.r_constraint_name
AND     part.partition_name like '%2002Q3%'
AND     cons.owner=user
AND     cons.CONSTRAINT_TYPE = 'R'
AND     cons.table_name = 'CONTACTS';

To get the master table for Addresses table

SELECT 	   distinct(cons.table_name) PARENT_TAB,
           conscol.column_name PARENT_COL,
	   part.partition_name PART_NAME
FROM       all_constraints cons,
           all_constraints consr,
	   all_cons_columns conscol,
           all_tab_partitions part
WHERE	   part.table_name=conscol.table_name
AND       conscol.constraint_name=cons.constraint_name
AND       cons.constraint_name=consr.r_constraint_name
AND	   part.partition_name like '%2002Q3%'
AND       cons.owner=USER
AND       consr.table_name='ADDRESSES';   

Are these queries optimized. Is there any other way to write them to get the same result? Received on Fri Sep 06 2002 - 17:03:43 CDT