WITH Clause with Oracle JDBC

Went to a 2 day tuning course with Dan Hotka and he introduced me to the WITH SQL clause.

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table

So I gave this a go and can see this helping out with LONG complicated queries , no need to add the same subquery over and over again, just use WITH AS. In short I tested this with Oracle JDBC , I knew it would work but just wanted to be sure.

SQL

  
SCOTT@linux11gr2> l
  1   with dept_a as (select /*+ materialize */ deptno from dept)
  2      select distinct e.deptno
  3      from dept_a d, emp e
  4      where e.deptno = d.deptno
  5      union
  6*     select deptno from dept_a d2

JDBC Code

  
  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
    String SQL_WITH = 
      " with dept_a as (select /*+ materialize */ deptno from dept) " + 
      "    select distinct e.deptno " + 
      "    from dept_a d, emp e " + 
      "    where e.deptno = d.deptno " + 
      "    union " + 
      "    select d2.deptno from dept_a d2";
    
    try
    {
      conn = getConnection();
      stmt = conn.createStatement();
      rset = stmt.executeQuery(SQL_WITH);
      while (rset.next())
      {
        System.out.println(rset.getString(1));
      }
    }
    finally
    {
      if (rset != null)
        rset.close(); 

      if (stmt != null)
        stmt.close(); 
      
      if (conn != null)
        conn.close();   
      
      if (ods != null)
        ods.close();
    }
  }