Re: UNION of two SELECT extremely slow

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Jul 2006 04:36:15 -0700
Message-ID: <1153136175.613850.35810@s13g2000cwa.googlegroups.com>

gmilosavljevic wrote:
> Hi All,
>
> I'm fighting with the weird problem. I have a public ASP application
> and suddenly one user reported the problem - very slow rendering of a
> page. I've localized the problem and it is about UNION operation. There
> are two SELECT statements, each of them work very very normaly
> (1-2secs) when being ran separately, but when I try to do UNION of
> them, the whole query executes more than 3minutes (!!!). It's really
> weird.
> Anyone has any thoughts about it?
>
> Thanks a lot in advance!!!
> <SNIP SQL Code>
> Regards,
> Goran M.

Without seeing the wait events, data size, plan, Oracle version, and init.ora parameters everything is likely to be a guess. One of the things that I see is that you use UNION rather than UNION ALL. My guess is that the query is hitting the temporary tablespace for sorting, distinct, and even join operations.

Another way to help the SQL statement is to remove the subquery, if at all possible. This can typically be done by converting it into an inline view. The inline view only needs to be resolved once, rather than once per row. For example, the second half of your SQL statement will look like this (note the NOT IN syntax was replaced by an outer join and then the right side of the join is specified as being null):

UNION ALL
SELECT
  VW.ID AS BWEID,
  B.PE_SUBMITTED,

  VW.BUDGET_ID,
  VW.CATEGORY_ID,
  VW.CATEGORY_NAME AS CAPTION,
  VW.USER_DEFINED_CATEGORY AS ADDLINE,

  1 AS HASEDIT,

  VW.SUPERCATEGORY_ID AS PARENTID,
  VW.SUPER_CATEGORY_NAME AS PARENTCAPTION,
  VW.PARENT_UDC,
  VW.CAPTION_UNIT_COST,
  VW.CAPTION_MULTIPLIER_1,
  VW.CAPTION_MULTIPLIER_2,
  VW.EXPENSE_ACCOUNT_CODE AS EXPCODE,
  VW.UNIT_COST,
  VW.MULTIPLIER_1,
  VW.MULTIPLIER_2,
  VW.COMMENTS,
  VW.APPLICABLE,
  VW.DISPLAY_SEQUENCE

FROM
  VW_BWS_ENTRY VW,
  BUDGET B,
  BUDGET_ITEM BI,
  (SELECT DISTINCT
    BIC.ID
  FROM
    BUDGET_ITEM_CATEGORY BIC,
    BUDGET_ITEM BI,
    INVOICE_LINE_ITEM I,
    BUDGET B
  WHERE
    BIC.ID=BI.BUDGET_ITEM_CATEGORY_ID
    AND BI.ID=I.BUDGET_ITEM_ID
    AND B.ID=BI.BUDGET_ID
    AND B.MEETING_ID='889445') BNI
WHERE
  VW.BUDGET_ITEM_CATEGORY_ID=BNI.ID(+)
  AND BNI.ID IS NULL
  AND VW.BUDGET_ID=B.ID
  AND B.MEETING_ID='889445'
  AND APPLICABLE=1
  AND VW.BUDGET_ITEM_CATEGORY_ID=BI.BUDGET_ITEM_CATEGORY_ID   AND BI.BUDGET_ID=B.ID
ORDER BY
  DISPLAY_SEQUENCE,
  ADDLINE; Depending on the version of Oracle, the hidden init.ora parameters, and the size of the data set, the above may execute many times faster than the original SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 17 2006 - 06:36:15 CDT