Improving Query Performance with the SQL WITH Clause

The SELECT statement's WITH clause provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.

The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.

Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query.

Without SQL WITH clause example

SELECT SUBSTR ('SEIZE THE DAY',1,INSTR('SEIZE THE DAY',' ',1)-1) STR1,
   SUBSTR ('SEIZE THE DAY',INSTR('SEIZE THE DAY',' ',1)+1,
   INSTR('SEIZE THE DAY',' ',1,2)- INSTR('SEIZE THE DAY',' ',1)-1) STR2,
   SUBSTR ('SEIZE THE DAY',INSTR('SEIZE THE DAY',' ',1,2)+1) STR3
FROM DUAL

SQL WITH clause example

WITH REC1 AS
   ( SELECT 'SEIZE THE DAY' STR FROM DUAL )
SELECT SUBSTR (STR,1,INSTR(STR,' ',1)-1) STR1,
   SUBSTR (STR,INSTR(STR,' ',1)+1,INSTR(STR,' ',1,2)- INSTR(STR,' ',1)-1) STR2,
   SUBSTR (STR,INSTR(STR,' ',1,2)+1) STR3
FROM REC1