With Clause
WITH Clause ====================================== What? With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs) This features is introduced in Oracle 9i.Using this feature: We assign query result set to a temporary tables. We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries. When? Usage of (2,3 level-Nested Queries) can be minimized or avoided when using With Clause. If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data. How to implement CTEs ====================== SELECT deptno, Sum(sal) total_sal FROM emp GROUP BY deptno; --fetch sum of sal for each dept. SELECT Avg(total_sal) FROM (SELECT deptno, Sum(sal) total_sal ...