Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: Please help to optimize a SELECT

    Code:
    SELECT DISTINCT t0.ID AS t0_ID 
       FROM ACCOUNTJDO t0, 
            ACCOU_PRODUCTS t1,
            PRODUCTCHARJDO t6,
            PRODUCTJDO t2,
            PRODUCTSPECCHARJDO t8,
            PRODUCTSPECCHARVALUEJDO t7,
            PSCPRODUCTSPECJDO t4,
            PSCPR_CHARS t5, 
            PSCPR_PRODUCTSPECS t3 
        WHERE ((t0.LEGACYACCOUNTTYPE <> 'CC' OR t0.LEGACYACCOUNTTYPE IS NULL)
                AND LOWER(t7.VALUE0) LIKE 'whatever%' 
                AND t8.SPECCHARNAME = 'ALTSERIALNUMBER') 
                AND t0.JDOCLASS = 'com.exigen.ef.sgb.infrastructure.SGBAccountJDO' 
                AND t0.ID = t1.ID 
                AND t1.ID_PRODUCTS = t2.ID 
                AND t2.ID = t3.ID 
                AND t3.ID_PRODUCTSPECS = t4.ID 
                AND t4.ID = t5.ID 
                AND t5.ID_CHARS = t6.ID 
                AND t6.ID_OFFICIALVALUE = t7.ID 
                AND t6.ID_SPECCHARACTERISTIC = t8.ID
    Code:
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=704869 Card=388 Bytes=51216)
    
       1    0   SORT (UNIQUE) (Cost=704869 Card=388 Bytes=51216)
       2    1     HASH JOIN (Cost=704846 Card=125393 Bytes=16551876)
       3    2       TABLE ACCESS (FULL) OF 'ACCOUNTJDO' (TABLE) (Cost=759 Card=89657 Bytes=4841478)
       4    2       HASH JOIN (Cost=703261 Card=125425 Bytes=9783150)
       5    4         HASH JOIN (Cost=699211 Card=126061 Bytes=8698209)
       6    5           HASH JOIN (Cost=695683 Card=126187 Bytes=8075968)
       7    6             HASH JOIN (Cost=693724 Card=125926 Bytes=6925930)
       8    7               TABLE ACCESS (BY INDEX ROWID) OF 'PSCPR_CHARS' (TABLE) (Cost=3 Card=1 Bytes=10)
       9    8                 NESTED LOOPS (Cost=692067 Card=126096 Bytes=6430896)
      10    9                   HASH JOIN (Cost=320213 Card=124917 Bytes=5 121597)
      11   10                     TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTSPECCHARVALUEJDO' (TABLE) (Cost=174039 Card=414794 Bytes=3318352)
      12   11                       INDEX (RANGE SCAN) OF 'I_PRDCLJD_LWR_VALUE0' (INDEX) (Cost=839 Card=414794)
      13   10                     TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTCHARJDO' (TABLE) (Cost=1747 Card=42519 Bytes=680304)
      14   13                       NESTED LOOPS (Cost=48855 Card=44492306 Bytes=1468246098)
      15   14                         TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTSPECCHARJDO' (TABLE) (Cost=478 Card=1046 Bytes=17782)
      16   15                           INDEX (RANGE SCAN) OF 'SPECCHARNAME' (INDEX) (Cost=6 Card=1097)
      17   14                         INDEX (RANGE SCAN) OF 'I_PRDCRJD_ID_SPECCHARACTERISTI' (INDEX) (Cost=6 Card=42523)
      18    9                   INDEX (RANGE SCAN) OF 'I_PSCPHRS_ID_CHARS' (INDEX) (Cost=2 Card=1)
      19    7               INDEX (FAST FULL SCAN) OF 'SYS_C0054926' (INDEX (UNIQUE)) (Cost=465 Card=1006413 Bytes=4025652)
      20    6             TABLE ACCESS (FULL) OF 'PSCPR_PRODUCTSPECS' (TABLE) (Cost=503 Card=1008610 Bytes=9077490)
      21    5           INDEX (FAST FULL SCAN) OF 'SYS_C0054860' (INDEX (UNIQUE)) (Cost=1100 Card=2310156 Bytes=11550780)
      22    4         TABLE ACCESS (FULL) OF 'ACCOU_PRODUCTS' (TABLE) (Cost=1166 Card=2300763 Bytes=20706867)

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    First place I'd look is those full table scans. Is that what you really want?

  3. #3
    Join Date
    Sep 2005
    Posts
    9

    Please help to optimize a SELECT

    Quote Originally Posted by marist89
    First place I'd look is those full table scans. Is that what you really want?
    No.

    Btw, Table PSCPR_PRODUCTSPECS has indexes on ID and ID_PRODUCTSPECS columns. Why they are not used?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    IMO,
    SELECT DISTINCT t0.ID AS t0_ID
    FROM ACCOUNTJDO t0
    above is OK & the remaining table joins should exist in the WHERE clause; since NO data is being extracted from them
    ACCOU_PRODUCTS t1,
    PRODUCTCHARJDO t6,
    PRODUCTJDO t2,
    PRODUCTSPECCHARJDO t8,
    PRODUCTSPECCHARVALUEJDO t7,
    PSCPRODUCTSPECJDO t4,
    PSCPR_CHARS t5,
    PSCPR_PRODUCTSPECS t3
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2005
    Posts
    9
    Quote Originally Posted by anacedent
    IMO,
    SELECT DISTINCT t0.ID AS t0_ID
    FROM ACCOUNTJDO t0
    above is OK & the remaining table joins should exist in the WHERE clause; since NO data is being extracted from them
    ACCOU_PRODUCTS t1,
    PRODUCTCHARJDO t6,
    PRODUCTJDO t2,
    PRODUCTSPECCHARJDO t8,
    PRODUCTSPECCHARVALUEJDO t7,
    PSCPRODUCTSPECJDO t4,
    PSCPR_CHARS t5,
    PSCPR_PRODUCTSPECS t3
    What do you mean? I do not get the idea. Could you give a sample?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    AND t0.ID EXISTS IN SELECT t1.ID FROM ACCOU_PRODUCTS t1 WHERE t1.ID_PRODUCTS EXISTS IN SELECT t2.ID FROM PRODUCTJDO t2 etc...

    Upon further review, this "design" gives me a headache.
    The nested relationships is way complex & the original query is a brute force join
    I suspect that with a LOT of careful thought a single SQL with nested subqueries can be devloped that would run VERY quickly with proper indexes.
    My bottom line is that only tables that contribute data to the SELECT clause
    should appear in the FROM clause.
    All other dependencies reside in the WHERE clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What version of Oracle are you using?

    Have you run dbms_stats recently and what parameters did you use as these can have a big effect on your execution plan. I usually double check the user_tab_columns table to check the number of distinct values is accurate.

    As for only including tables that contribute to the select in the FROM clause, that is a complete myth. Sometimes it can help but sometimes it can be much worse. The optimizer can merge subqueries (not all cases though) into the main query or convert tables in the from clause to a subquery type execution plan. This isnt to say Anacedents method wont work in your case just that it is not a hard and fast rule.

    The best thing to do is if reanalyzing doesnt fix the problem try different ways of writing the sql as this sometimes helps/forces the optimizer to do the right thing. If this still doesnt work you need to look to see if you have indexes in the right place (with columns in the right order) and also trying to force it to execute the most restrictive criteria first. As a last resort you can use hints but this may cause grief in the future if your data distribution changes.

    Alan

Posting Permissions

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