ahlawat.net

At work last week - I was doing some iterative development which required me to start from a clean database every time. The solution provided by the DBA’s in my team was either a complete reinitialization of the schema OR a script that disables every constraint, truncates all the tables and re-enables all the constraints. Both of these solutions were dreadfully slow.

This was a bit of a hassle because we have an extremely complex schema with a lot of inter-dependencies between tables. Therefore like any self respecting developer I decided to script my way out - so the problem was to:

1. Figure out all the referential constraints from the database

2. Order the deletes in a manner that none of the constraints are violated

A little bit of reading and research introduced me to the all_constraints table ..

SQL> desc all_constraints;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

SQL>

The columns of interest here is the R_CONSTRAINT_NAME  which is the constraint on which the constraint in question relies on. A join with the user_constraint table can then give the actual table on which the current table in question relies on -

select a.constraint_name as acn, a.table_name as atn, b.table_name as btn,
  b.constraint_name  as bcn from all_constraints a, user_constraints b where
  a.r_constraint_name = b.constraint_name

After this its a cake walk - here is the groovy script that deletes the table in the correct order.

import groovy.sql.Sql

def sql = Sql.newInstance("jdbc:oracle:thin:username/password@//localhost:1521/SID",
        "oracle.jdbc.driver.OracleDriver")
long start = System.currentTimeMillis()
def dependencies = [:]
def deleted = new HashSet();

sql.eachRow("select table_name from user_tables where temporary = 'N' and table_name not like 'MVC_%' and table_name not like 'MV_%'") {
  dependencies[it.table_name] = new HashSet();
}

sql.eachRow("""select a.constraint_name as acn, a.table_name as atn, b.table_name as btn,
  b.constraint_name  as bcn from all_constraints a, user_constraints b where
  a.r_constraint_name = b.constraint_name""") {
  dependencies[it.btn] << it.atn
}

println "Getting all tables and resolving referential constraints finished ${System.currentTimeMillis() - start} ms"

start = System.currentTimeMillis();

int size = dependencies.keySet().size()
while (deleted.size() < size) {
  int initSize = deleted.size();
  dependencies.keySet().each {table->
    def reducedSet = dependencies[table].grep {!deleted.contains(it)}
    if (reducedSet.size() ==0 || (reducedSet.size() == 1 && reducedSet.iterator().next() == table)) {
      deleted.add(table)
      println "deleting $table"
      String query = "delete from $table"
      sql.execute(query)
    }
  }

  if (initSize == deleted.size()) {
    println "PROBLEM .. "
    dependencies.keySet().each() {table->
      if (deleted.contains(table)) {
        return;
      }
      println "$table -> ${dependencies[table].grep {!deleted.contains(it)}}"
    }
    throw new IllegalStateException("Cannot delete tables because of cyclic dependencies")
  }
}

println "Deleting all tables completed in ${System.currentTimeMillis() - start}"

I hope this saves you as much time as it has already saved me.