swangs

Descending from Oracle 9i release 2, the WITH clause has been implemented.
The WITH clause is like a temp table function that used widely and frequently in other database systems.
It's too bad this clause surfaces so late, but to look on the good side, I am glad we finally have such a good and convenient syntax to organize more efficient queries.

EXAMPLE:

WITH temp_name AS (
    select NVL(column_name,'') as CN
    from table_a
    where .....
)
select ....
from table_b
join temp_name on ...
where ...

Use WITH can improve the efficiency and usability ,and it's easy to read and modify.
It also provides a way to tune heavy-loading queries when they are needed to be tuned.

Ref:
http://www.dba-oracle.com/t_with_clause.htm
http://www.oracle.com/technology/products/oracle9i/daily/oct10.html