WITH CLAUSE for sub-query
Hi folks,
Recently I've been performance tuning some calculation views written by another developer and I've consistently found examples where they are using WITH CLAUSE similar to this;
VAR_OUT = WITH <alias_name_A> AS (sql_subquery_statement),
>]
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT <column_list>
FROM <alias_name_A>, <alias_name_B>, [tablenames]
[WHERE <join_condition
In several cases I've re-written to be something like this;
LT_ALIAS_A = select * from ABC;
LT_ALIAS_B = select * from XYZ;
VAR_OUT = select * from :LT_ALIAS_A JOIN :LT_ALIAS_B ON whatever
Now both ways work and give results but I'm finding performance seems to be worse with the WITH CLAUSE. Does anybody know for certain that this is consistently the case or perhaps I've just gotten lucky thus far as my examples are somewhat over simplified and in reality these are fairly complex queries however it SEEMS i'm consistently seeing degradation with the WITH clause. I'm not finding much on this clause in SQL guide so I'm wondering if anybody has any thoughts on this or actually does their local tables this way.
Thanks,
-Patrick