WITH Clause

[IanWaterman]

I have recently learnt about the WITH clause and how it can help speed up Queries with a lot of subqueries. However, I can not work out how to use it within my query or even if its appropriate.

  SELECT DISTINCT -- List of fields and various case statements
    .....

  FROM ACCOUNTTRANSACTION
  INNER JOIN ACCOUNTHEADER
  ON ACN_ACCOUNTHEADER_CDE = ACCOUNTHEADER_KEY
  INNER JOIN ACCOUNTTRANSACTIONSET
  ON ACCOUNTTRANSACTIONSET_CDE = ACCOUNTTRANSACTIONSET_KEY
  LEFT OUTER JOIN RISKHISTORY
  ON ACCOUNTTRANSACTIONSET_KEY  = RKH_ACCOUNTTRANSACTIONSET_CDE
  AND ATA_REFERENCE_TRANSACTION = RKH_POLICY_CDE
  AND TRUNC (ATA_POSTING_DATE) >= TO_DATE ( '21/04/2011', 'DD/MM/YYYY' )
  INNER JOIN PARTY
  ON ACH_PARTY_CDE = PARTY_KEY
  LEFT OUTER JOIN PAYMETHREF
  ON ATA_PAYMETHREF_CDE = PAYMETHREF_KEY
  INNER JOIN DISBURSEMENTTYPE
  ON ACN_DISBURSEMENTTYPE_CDE = DISBURSEMENTTYPE_KEY
  INNER JOIN TRANSACTIONTYPE
  ON ATA_TRANSACTIONTYPE_CDE = TRANSACTIONTYPE_KEY
  LEFT OUTER JOIN PAYMENTCARDACCTRANS PC
  ON ACN_ACCOUNTHEADER_CDE = PAT_ACCHEADCDE
  AND ACCOUNTTRANSACTIONSET_CDE = PAT_ACCTRANSETCDE
  LEFT OUTER JOIN GENPOLICY MAIN_POL
  ON ATA_REFERENCE_TRANSACTION = MAIN_POL.POLICY_KEY
  LEFT OUTER JOIN ----Sub query 1
    ( SELECT DISTINCT AON_MAIN_POLICY,
      AON_LINK_POLICY
      FROM ADDONS
      INNER JOIN GENPOLICY
      ON AON_MAIN_POLICY = POLICY_KEY
    ) ADDONS 
  ON ATA_REFERENCE_TRANSACTION = ADDONS.AON_LINK_POLICY
  LEFT OUTER JOIN GENPOLICY AON_POL
  ON ADDONS.AON_MAIN_POLICY = AON_POL.POLICY_KEY
  INNER JOIN PMIUSER
  ON ATA_UPD_USER = PMIUSER_KEY
  LEFT OUTER JOIN REVENUETYPE
  ON ATA_REVENUE_CDE = REVENUETYPE_KEY
  LEFT OUTER JOIN
-- Subquery 2
  (
	SELECT MAX(POLICY_ITEMS_KEY) POLICY_ITEMS_KEY,
    POLICY_ITEMS.ITM_POLICY_CDE,
    ITM_VERSION,
    MAX (ITM_ITEM_SEQ) ITM_ITEM_SEQ,
    MAX(ITM_END_DATE) ITM_END_DATE
    ,SCHEMECODE
    FROM POLICY_ITEMS
    LEFT JOIN 
   -- Embedded Subquery A
    (
      SELECT ITM_POLICY_CDE,
      DECODE(QSD_EDI_SCHEME_CODE,'FC','PMC','PMY','PMY') SCHEMECODE
      FROM POLICY_ITEMS
      INNER JOIN RISKHISTORY
      ON (RKH_POLICY_CDE=ITM_POLICY_CDE
      AND RKH_VERSION   = ITM_VERSION)
      INNER JOIN QUOTE_RESULTS_HEADER
      ON QRH_POLICY_ITEMS_CDE = POLICY_ITEMS_key
      INNER JOIN QUOTE_OUTPUT_HEADER
      ON QOH_QUOTE_RESULT_CDE=QUOTE_RESULTS_HEADER_KEY
      INNER JOIN QUOTE_SOFTWARE_DETAILS
      ON QUOTE_OUTPUT_HEADER_KEY = QSD_QUOTE_OUTPUT_CDE
      WHERE RKH_LIVE             = 'Y'
      AND RKH_DESCRIPTION       IN ('Inception','Renewed')
    ) DRVTBL
    ON DRVTBL.ITM_POLICY_CDE = POLICY_ITEMS.ITM_POLICY_CDE
    WHERE ITM_ITEM_TYPE = 0
    GROUP BY POLICY_ITEMS.ITM_POLICY_CDE, ITM_VERSION, SCHEMECODE
  ) P
  ON RKH_POLICY_CDE = p.ITM_POLICY_CDE
  AND RKH_VERSION   = p.ITM_VERSION
  LEFT OUTER JOIN GENERAL_MOTOR GM
  ON P.POLICY_ITEMS_KEY = ITM_POLICY_ITEMS_CDE
  LEFT OUTER JOIN EXT_QTE_PREM E
  ON P.ITM_POLICY_CDE = E.EQP_POLICY_CDE
  AND p.ITM_VERSION   = E.EQP_VERSION
  AND P.ITM_ITEM_SEQ  = E.EQP_ITEM_SEQ
  WHERE (
    CASE
      WHEN ATA_TRANSACTION_AMOUNT = 0
      THEN 1
      ELSE ACN_TOTAL_AMOUNT
    END)                       != 0
  AND TRUNC (ATA_POSTING_DATE) <= SYSDATE - 1
  AND TRUNC (RKH_UPD_DATE) >= SYSDATE - 31

Due to size of dataset query is now quite slow, will using WITH help me? If so how do I incorporate into above.

Thank you

Ian

 

[sxschech]

Something like this?

WITH ADDON AS
( 
 SELECT DISTINCT AON_MAIN_POLICY,
        AON_LINK_POLICY
 FROM ADDONS 
 INNER JOIN GENPOLICY
      ON AON_MAIN_POLICY = POLICY_KEY
)
,
DRVTBL AS
(
      SELECT ITM_POLICY_CDE,
      DECODE(QSD_EDI_SCHEME_CODE,'FC','PMC','PMY','PMY') SCHEMECODE
      FROM POLICY_ITEMS
      INNER JOIN RISKHISTORY
      ON (RKH_POLICY_CDE=ITM_POLICY_CDE
      AND RKH_VERSION   = ITM_VERSION)
      INNER JOIN QUOTE_RESULTS_HEADER
      ON QRH_POLICY_ITEMS_CDE = POLICY_ITEMS_key
      INNER JOIN QUOTE_OUTPUT_HEADER
      ON QOH_QUOTE_RESULT_CDE=QUOTE_RESULTS_HEADER_KEY
      INNER JOIN QUOTE_SOFTWARE_DETAILS
      ON QUOTE_OUTPUT_HEADER_KEY = QSD_QUOTE_OUTPUT_CDE
      WHERE RKH_LIVE             = 'Y'
      AND RKH_DESCRIPTION       IN ('Inception','Renewed')
)
,
P AS 
(
    SELECT MAX(POLICY_ITEMS_KEY) POLICY_ITEMS_KEY,
    POLICY_ITEMS.ITM_POLICY_CDE,
    ITM_VERSION,
    MAX (ITM_ITEM_SEQ) ITM_ITEM_SEQ,
    MAX(ITM_END_DATE) ITM_END_DATE
    ,SCHEMECODE
    FROM POLICY_ITEMS
    LEFT JOIN DRVTBL
    ON DRVTBL.ITM_POLICY_CDE = POLICY_ITEMS.ITM_POLICY_CDE
    WHERE ITM_ITEM_TYPE = 0
    GROUP BY POLICY_ITEMS.ITM_POLICY_CDE, ITM_VERSION, SCHEMECODE
  ) 

SELECT DISTINCT -- List of fields and various case statements
    .....

  FROM ACCOUNTTRANSACTION
  INNER JOIN ACCOUNTHEADER
  ON ACN_ACCOUNTHEADER_CDE = ACCOUNTHEADER_KEY
  INNER JOIN ACCOUNTTRANSACTIONSET
  ON ACCOUNTTRANSACTIONSET_CDE = ACCOUNTTRANSACTIONSET_KEY
  LEFT OUTER JOIN RISKHISTORY
  ON ACCOUNTTRANSACTIONSET_KEY  = RKH_ACCOUNTTRANSACTIONSET_CDE
  AND ATA_REFERENCE_TRANSACTION = RKH_POLICY_CDE
  AND TRUNC (ATA_POSTING_DATE) >= TO_DATE ( '21/04/2011', 'DD/MM/YYYY' )
  INNER JOIN PARTY
  ON ACH_PARTY_CDE = PARTY_KEY
  LEFT OUTER JOIN PAYMETHREF
  ON ATA_PAYMETHREF_CDE = PAYMETHREF_KEY
  INNER JOIN DISBURSEMENTTYPE
  ON ACN_DISBURSEMENTTYPE_CDE = DISBURSEMENTTYPE_KEY
  INNER JOIN TRANSACTIONTYPE
  ON ATA_TRANSACTIONTYPE_CDE = TRANSACTIONTYPE_KEY
  LEFT OUTER JOIN PAYMENTCARDACCTRANS PC
  ON ACN_ACCOUNTHEADER_CDE = PAT_ACCHEADCDE
  AND ACCOUNTTRANSACTIONSET_CDE = PAT_ACCTRANSETCDE
  LEFT OUTER JOIN GENPOLICY MAIN_POL
  ON ATA_REFERENCE_TRANSACTION = MAIN_POL.POLICY_KEY
  LEFT OUTER JOIN ADDON
  ON ATA_REFERENCE_TRANSACTION = ADDONS.AON_LINK_POLICY
  LEFT OUTER JOIN GENPOLICY AON_POL
  ON ADDONS.AON_MAIN_POLICY = AON_POL.POLICY_KEY
  INNER JOIN PMIUSER
  ON ATA_UPD_USER = PMIUSER_KEY
  LEFT OUTER JOIN REVENUETYPE
  ON ATA_REVENUE_CDE = REVENUETYPE_KEY
  LEFT OUTER JOIN P
  ON RKH_POLICY_CDE = p.ITM_POLICY_CDE
  AND RKH_VERSION   = p.ITM_VERSION
  LEFT OUTER JOIN GENERAL_MOTOR GM
  ON P.POLICY_ITEMS_KEY = ITM_POLICY_ITEMS_CDE
  LEFT OUTER JOIN EXT_QTE_PREM E
  ON P.ITM_POLICY_CDE = E.EQP_POLICY_CDE
  AND p.ITM_VERSION   = E.EQP_VERSION
  AND P.ITM_ITEM_SEQ  = E.EQP_ITEM_SEQ
  WHERE (
    CASE
      WHEN ATA_TRANSACTION_AMOUNT = 0
      THEN 1
      ELSE ACN_TOTAL_AMOUNT
    END)                       != 0
  AND TRUNC (ATA_POSTING_DATE) <= SYSDATE - 1
  AND TRUNC (RKH_UPD_DATE) >= SYSDATE - 31

 

[IanWaterman]

Thanks for this.

This is as far as I got but I can not see how the queries in the WITH clause are joined to the data. The sub queries bring back a data set. I am now wondering if WITH is only appropriate if a single data record is brought back.

Ian

 

[taupirho]

Ian, my understanding is that the WITH clause is is useful for

a) Simplifying unwieldy queries , note I said simplifying not making them any faster. Basically it just makes them more readable in my view

b) As a place-holder for repeated sub-queries. this can improve performance as it works a bit like cacheing the data rerturned by the sub-query so that subsequent access is faster

I think your situation stands firmly in camp (a) I'm afraid


In order to understand recursion, you must first understand recursion.

 

[IanWaterman]

On looking at the way they are used I would agree with you.

It just that I was at an interview recently and was asked about the WITH clause. Unfortunately, I replied with a blank look. Needless to say I did not get the job, but I did ask what they were and the interviewer assured me that query speed increased significantly in his case from seveal minutes to a few seconds.

On looking at write ups on subject it implies that Oracle can automatically Materialise the queries which would make execution faster.

Or do I need to explicitly materialise them I have seen this syntax

WITH dept_count AS (
SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...

Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.

Ian

 

[taupirho]

In my opinion I would let Oracle do any optimising for you rather that explicitly materialsing the query. You can always revert to doing that if required. On the subject of having a blank look at your interview when the WITH clause was mentioned I would say you don't know what you don't know. However, judging by the query you posted, you obviously are an experienced Oracle bod and I would have expected someone of your experience to have heard of sub-query factoring (the WITH clause's fancy name don't you know). In this regard the "New features" manual that comes with every Oracle database is invaluable. Its always worth a skim through to see if anything catches the eye which can be delved into depper at a more convenient time. That and the frequenting of forums such as this one and others like it and I guarantee you'll soon get up to speed with 95% of new Oracle features pretty quickly. And don't worry, as the venerable Tom Kite always says, he tries to learn something new about Oracle every day so even he doesn't know it all!


In order to understand recursion, you must first understand recursion.

 

[IanWaterman]

Thanks for your input.

I am mainly a Reports Developer with Crystal and Reporting Services so SQL is just a side line to build views and debug reports. So pick up stuff as I come across it rather than actively seeking.

Ian

 

[IanWaterman]

Just to let all know. when using WITH do not give the Subquery same name as the table it is querying. Oracle 10 does not like it

WITH ADDON AS
(
SELECT DISTINCT AON_MAIN_POLICY,
AON_LINK_POLICY
FROM ADDONS
INNER JOIN GENPOLICY
ON AON_MAIN_POLICY = POLICY_KEY
)

Had to change this to

WITH ADD_DET AS
(
SELECT DISTINCT AON_MAIN_POLICY,
AON_LINK_POLICY
FROM ADDONS
INNER JOIN GENPOLICY
ON AON_MAIN_POLICY = POLICY_KEY
)

Query runs 3 times faster with the With declaration instead of embedded subqueries.

Ian