1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL WITH Clause ( Subquery Factoring )

Discussion in 'SQL PL/SQL' started by rajavu, Oct 17, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    'WITH queryname' clause is introduced in the Oracle 9i release 2 . This allows you to assign a name to a subquery block and this name can be referenced in multiple places in the main query. Oracle treat this query name as inline view or as a table . You can even give alias to this query name. Actually WITH is introduced as replacement to normal subquery and this technique is called subquery factoring.

    Syntax

    Single Alias

    Code (Text):
    WITH <alias_name> AS (subquery_sql_statement) SELECT column_name_list> FROM <aliasname>[,tablename]
    Multiple Alias

    Code (Text):


    [u]Multiple Alias[/u]
    WITH   <alias_one> AS
                (subquery_sql_statement)
              <alias_two> AS
                 (sql_statement_from_alias_one or subquery_sql_statement )
    SELECT <column_name_list>
    FROM <alias_one>, <alias_two>,[Tablenames]
    [WHERE <join_condition>]
     
    Examples

    Example for Single alias

    Code (Text):

    WITH REC1 AS
    ( SELECT 'LIFE IS BEAUTIFUL' 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
     
    its equivalent SQL will be

    Code (Text):

    SELECT  SUBSTR ('LIFE IS BEAUTIFUL',1,INSTR('LIFE IS BEAUTIFUL',' ',1)-1) STR1,
            SUBSTR ('LIFE IS BEAUTIFUL',INSTR('LIFE IS BEAUTIFUL',' ',1)+1,
                   INSTR('LIFE IS BEAUTIFUL',' ',1,2)- INSTR('LIFE IS BEAUTIFUL',' ',1)-1) STR2,
            SUBSTR ('LIFE IS BEAUTIFUL',INSTR('LIFE IS BEAUTIFUL',' ',1,2)+1) STR3
    FROM DUAL
     
    Example for Multiple alias

    Selects only those employess who are earning the above the average salary of their employees (without using AVG function)

    Code (Text):

    WITH SUM_SAL  AS (SELECT DEPTNO , SUM(SAL)SAL_SUM  FROM  EMP GROUP BY DEPTNO ),
         CNT_EMP  AS (SELECT DEPTNO , COUNT(SAL)CNT_SAL FROM  EMP GROUP BY DEPTNO )
    SELECT EMP.DEPTNO, EMP.ENAME ,EMPNO, SAL
    FROM  EMP , SUM_SAL REC1 , CNT_EMP REC2
    WHERE EMP.DEPTNO = REC1.DEPTNO
    AND   REC1.DEPTNO = REC2.DEPTNO
    AND   SAL > (SAL_SUM/CNT_SAL  )
     
    Conclusion

    Subquery Factoring technique really helps to minimise the complexity of huge Subquery commands by assgning them a name and referring the at multiple points in the main query whenever is needed .
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Continuing with rajavu's topic on Sub-Query factoring...

    Materialize Hint

    Materialize Hint is a technique to optimise queries and it can provide good performance gains with large datasets. When Oracle materialises a subquery, in effect it means that a "sort of dynamic" temporary table is created for use in the execution of that SQL statement.

    By Default the Cost Based Optmiser (CBO) may choose not to materialise a subquery. In such a case we can use the MATERIALIZE hint.

    Please note that my examples and post is based on Oracle 9i

    In the following example, we will include a subquery that is unlikely to be materialised by Oracle and then attempt to force the temporary table execution using the MATERIALIZE hint. We will begin with a version of the query without hints.

    Code (Text):
    SQL> WITH sub_query AS (
      2     SELECT abc
      3     ,      SUM(sum_amt) AS total_amt
      4     FROM   money
      5     GROUP  BY
      6            abc
      7     )
      8  SELECT *
      9  FROM   sub_query;
    Obviously here Oracle will not materialise this subquery. Since the subquery is only used once so it would be a waste to createand use a temporary table.

    So we can demonstrate how to force Oracle by using the MATERIALIZE hint

    Code (Text):
    SQL> WITH sub_query AS (
      2     SELECT /*+ materialize */
      3            abc
      4     ,      SUM(sum_amt) AS total_amt
      5     FROM   money
      6     GROUP  BY
      7            abc
      8     )
      9  SELECT *
     10  FROM   sub_query;
    Cheers! :D
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Materiaze or it will be MATERIALIZE ?
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Continuing on the topic of Subquery Factoring, there are some restrictions while using it.

    Subqueries not Referenced

    Oracle will raise an exception if we define subqueries not referenced at least once.
    Code (Text):

    SQL> WITH unused_subquery AS (
      2     SELECT 'A' AS column_alias
      3     FROM   dual
      4     )
      5  SELECT SYSDATE
      6  FROM   dual;
       FROM   dual
              *
    ERROR at line 3:
    ORA-32035: unreferenced query name defined in WITH clause
     
    Subqueries Nesting

    Subqueries cannot be nested inside each other even though subqueries can reference preceding subqueries. Look at the example below.

    Code (Text):
    SQL> WITH outer_subquery AS (
      2          WITH nested_subquery AS (
      3                  SELECT SYSDATE AS date_column
      4                  FROM   dual
      5                  )
      6          )
      7  SELECT date_column
      8  FROM   outer_subquery;
            WITH nested_subquery AS (
            *
    ERROR at line 2:
    ORA-32034: unsupported use of WITH clause