I recently learned about CTE's in SQL Server and am attempting to use it in PL/SQL. I do not need the recurive benefits of it, however, I would like to use it in lieu of creating a view and to improve query performance. Just looking for some direction on what code may be similar.
+5
A:
In Oracle this is known as subquery factoring, and it works the same as in SQL Server AFAIK:
with cte as (select * from emp)
select * from cte join dept on dept.deptno = cte.deptno;
See SELECT documentation and search for "factoring".
Tony Andrews
2010-07-26 13:48:59
+1: Supported since 9i, Subquery factoring didn't support the recursive portion till 11g (which is now ANSI) because Oracle already supported hierarhical queries using CONNECT BY syntax... I don't recall when PostgreSQL started supporting the syntax, likely in v8.x. But CTEs/Subquery Factoring doesn't improve query performance, just different syntax for derived tables/inline views.
OMG Ponies
2010-07-26 14:26:49
Many thanks. I am using Oracle 8i which would explain why I'm striking out. I appreciate the help!
Matthew Hoenstine
2010-07-26 14:50:40
The recursive function of the `with` clause was not supported until 11g R2. (Not availabe in 11g R1.) See: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/wnsql.htm#sthref6
Shannon Severance
2010-07-26 16:57:24