Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Optimizing subqueries in Oracle 9i

    Hello,

    I have some performance problems with subqueries in Oracle 9. THe performance problems are regarding Subqueries with OR's (see below). I' m searching for hints to let this query better perform (f.e. translate into UNIONS?). I hope you can help me!

    SELECT DREL.RELNR , MIN(DREL.HHNR) FROM P_CHORDIANT.DREL DREL, P_DWH.DPERS DPERS WHERE

    ( ( DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS6.A1 FROM P_CHORDIANT.BS6 BS6)

    OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS2.A1 FROM P_CHORDIANT.BS2 BS2)

    OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS4.A1 FROM P_CHORDIANT.BS4 BS4)

    OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS5.A1 FROM P_CHORDIANT.BS5 BS5)

    OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS16.A1 FROM P_CHORDIANT.BS16 BS16) )

    AND DPERS.VERSRCI = 'J' ) AND DPERS.RELNR = DREL.RELNR

    GROUP BY DREL.RELNR

    Roberto

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    SELECT DREL.RELNR , MIN(DREL.HHNR)
    FROM P_CHORDIANT.DREL DREL,
    P_DWH.DPERS DPERS ,
    (
    SELECT A1 FROM P_CHORDIANT.BS6
    union
    SELECT A1 FROM P_CHORDIANT.BS2
    union
    SELECT A1 FROM P_CHORDIANT.BS4
    union
    SELECT A1 FROM P_CHORDIANT.BS5
    union
    SELECT A1 FROM P_CHORDIANT.BS16
    ) BS
    where DREL.RELNR = BS.A1
    AND DPERS.VERSRCI = 'J'
    AND DPERS.RELNR = DREL.RELNR
    GROUP BY DREL.RELNR


    but performance does depend a lot on data distribution, when you last analyzed the data, indexes etc..

    Alan

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Those subqueries are generated by a SQL generator for campaign management. So i cannot change the SQL. I can change hints for it. Oracle says there's a hint which is converting the SQL in a union like operator. I'm searching for a hint whoch make this query faster.

    Regards

    Roberto

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You might in that case want to look at plan stability.

    See http://www.dbazine.com/jlewis4.html

    Alan

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Is it possible to automatrically change and OR into a UNION using a hint or Oracle stored outlines. Which hint to use? CAn anybody explain more in depth the Oracle stored outlines tool?

    Regards

    Roberto

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by robertosp
    Is it possible to automatrically change and OR into a UNION using a hint or Oracle stored outlines. Which hint to use? CAn anybody explain more in depth the Oracle stored outlines tool?

    Regards

    Roberto
    That's what Oracle does according to the docs (always worth a read!):
    If a query contains a WHERE clause with multiple conditions combined with OR operators, then the optimizer transforms it into an equivalent compound query that uses the UNION ALL set operator, if this makes the query execute more efficiently:
    It also says:
    The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •