Subquery Factoring with WITH Clause from Oracle 9i onwards.

Subquery Factoring

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Select each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Rename the existing Column in Oracle Table from 9iR2

In Oracle9ir2, Oracle provides "alter table" syntax to rename data columns in-place in this form:

alter table
   table_name
rename column
   old_column_name  
TO
   new_column_name;

Here are some examples of Oracle "alter table" syntax to rename data columns.

alter table
   cust_table
rename column
   cust_sex 
TO
   cust_gender;