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.

On more than one occasion I have been stumped with a message like this.

java.sql.BatchUpdateException: ORA-00001: unique constraint (SYSTEM.SYS_C0089989) violated

Apart from blaming oracle for being lazy and not putting the table/column name in the error message - there is nothing else that comes to mind when I see something like this.

Finding out the table and the constraint - you can connect to plsql and execute this query:

select constraint_name, constraint_type, table_name from user_constraints where constraint_name like 'SYS_C0089989';

The result is something like this:

"CONSTRAINT_NAME","CONSTRAINT_TYPE","TABLE_NAME"
"SYS_C0089989","P","JOBAUDIT"

This information gives you the table name and the constraint type on that table.

Here is what the constraint type columns mean:

C   Check on a table                Column
O   Read Only on a view           Object
P   Primary Key                       Object
R   Referential AKA Foreign Key  Column
U   Unique Key                        Column
V   Check Option on a view       Object

So looking back I know my code is trying to insert a duplicate id in the table.

I have been working on hibernate for a while now and recently wanted to get some data in really fast (basically using SQL batching). 

Here is the entity:

@Entity(name = "Person")
public class PersonEntity {
    @Id
    int id;
    String fName;
    String lName;
    int age;

    public String getFName() {
        return fName;
    }

    public void setFName(String fName) {
        this.fName = fName;
    }

    public String getLName() {
        return lName;
    }

    public void setLName(String lName) {
        this.lName = lName;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
}

1. Modify the *context.xml file to include the following hibernate jdbc parameter:

<prop key="hibernate.jdbc.batch_size">20</prop>

2. Write the code with session clearing every BATCH_SIZE. For convinience sake I am extending the HibernateDaoSupport class but you can use the sessionFactory directly. Here is what I have:

@Transactional
public class HibernateInsertTest extends HibernateDaoSupport implements HibernateInsertInterface {
    private final int BATCH_SIZE = 20;
    private final int NUMBER_OF_ENTITIES = 100000;
    @Transactional(propagation = Propagation.REQUIRED)
    public void init() {
        Session session = getSession();
        StopWatch stopWatch = new StopWatch("Insert test");

        stopWatch.start("Saving entities");
        for (int x = 0; x<NUMBER_OF_ENTITIES; x++) {

            PersonEntity ent = new PersonEntity();
            double rand = Math.random();
            ent.setId(new Long(System.currentTimeMillis() + x).hashCode());
            ent.setFName(""+ rand);
            ent.setLName("" + rand);
            ent.setAge((int) (rand * 100));
            session.persist(ent);
            if (x%BATCH_SIZE == 0) {
                session.flush();
                session.clear(); //<-- IMPORTANT TO CLEAR THE SESSION
            }
        }
        stopWatch.stop();
        System.out.println(stopWatch.prettyPrint());
    }

}

There is something subtle about this code that I want to talk about. Firstly notice that the inserts are in one transaction - although I can use the hibernate session to being and commit a transaction I am instead using the @Transaction spring annotation. The corresponding configuration (since I am using Jboss I can tie this in with the Jta transaction manager) is the following: 

    <!-- configure JTA transaction manager -->
    <tx:annotation-driven transaction-manager="transactionManager" />
    <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
    </bean>

Since spring works by generating a proxy for this class I am also implementing an interface (its good practice anyway). The other thing are the periodic calls to session.clear() and session.flush() - they are to prevent an OutOfMemroyException which can occurr if you are inserting a large amount of objects.  

Here is the output of the program:

StopWatch 'Insert test': running time (millis) = 4687

-----------------------------------------
ms     %     Task name
-----------------------------------------
04687  100%  Saving entities

The performance is no different from vanilla JDBC batching because underneath the hood its using the same mechanism. There is however a drawback you cannot use oracle natvie bindings which speeds up batch inserts by 1.5x.