WITH clause was introduced in Oracle 9i to provide powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.

WITH Clause Syntax

WITH
 alias_name         -- alias for the aggregate_query
AS
 (aggregate_query_here)
SELECT...            -- Beginning of the query main body

It should be noted that multiple aggregate queries can be defined in the WITH clause as below.

WITH
 alias_name1
AS
 (agg_query1) ,
 alias_name2
AS
 (agg_query2)
SELECT...

When using subquery factoring, think of the aggregate_query as an in-line view. Actually, it is a view but a temporary one. Instead of creating a permanent view accessible in the database, you are creating a temporary one exclusively used by your main query.

Usage

I primarily use this in creating adhoc queries, reports and extractions. Of course, you can just create a view to accomplish this task. However, there are times wherein you are not allowed to create additional objects in the database for one reason or another. In that case, this subquery optimization comes very handy.

Caveat

You can’t use this inside Oracle Forms. It also doesn’t work inside stored procedures or anything with PL/SQL in it. At least I’m talking about until 11g.

Comments are closed.

Post Navigation