VIVEK SHAH

In this section we will see about how to find the child tables for a given parent table.

In many projects where we deal with databases, we would like to find the list of child tables for a parent table. A parent table will have a primary key and one or more child tables will be referencing the parent table using a foreign key. But how will you find the list of child tables referencing the parent table.

If you use oracle client like TOAD tool, we can easily find out the child tables. But is there any other way to find the same? yes we can find the list using oracle meta data tables. The following query will give the list of parent table name, parent column name, child table name and child column name.

SELECT a.table_name, c.column_name, b.table_name AS CHILD_TABLE, d.column_name, b.R_CONSTRAINT_NAME 
FROMuser_constraints a, user_constraints b, user_ind_columns c, user_cons_columns d 
WHERE a.constraint_type = 'P' AND 
a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME AND  
b.CONSTRAINT_TYPE = 'R' AND  
a.table_name = c.table_name AND
a.constraint_name = c.index_name AND  
b.CONSTRAINT_NAME = d.constraint_name AND  
a.table_name = ''TABLE_NAME"

In the above query substitute the required table name at the end of where clause to find the list of child tables for the given parent table.

substitute "TABLE_NAME" with your parent_table_name

Often you may be faced with a requirement to optimize a sql query(increase query running time) to be executed in oracle database. Query Optmization in general is a huge topic to discuss. Let me provide some basic steps to be followed while optimizing a sql query.

1. If the query has only one table and certain where conditions, then try to index the columns given in the where condition. By default primary key columns are indexed. Then try to reorder the set of where conditions based on number of rows for the column values in descending order.

2. If the query contains more than one table (joins), first try to order the tables in descending order (number of rows) in the 'from' clause. Then try to reorder the set of where conditions based on number of rows for the column values in descending order. Try executing the sql query in TOAD to get the explain plan and then find the tables which are taking more time to retrieve rows in explain plan and index the required columns in those tables.

3. Try using sql hints to make the query execution faster. Let see some of the common hints available in oracle

ORDERED - This hint will execute the query in the same order of tables.

INDEX(tablename indexname) - this hint will force the system to use the particular index while executing the query

USE_NL(table1, table2) - this hint will use nested loop joins to join table1 and table2.

We can also use more than one hint in a single query as follows.

Oracle DBMS will optimize every query it receives for execution. But if you are not satisfied with the default optimization done and query execution time you can follow the above steps to optimize your query.