Odd query optimization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Odd query optimization

  1. #1

    Question Odd query optimization

    I have a query that's joining 4 tables. Very simple joins primary key to foreign key. Let's call them tables1 thru 4. The query is:

    Select distinct
    table1.column, table2.column, table3.column
    from table1, table2, table3, table4
    where
    table1.pkey = table2.pkey and
    table2.pkey = table3.pkey and
    table3.pkey = table4.pkey and
    table4.column like 'somevalue'

    The query takes 3 minutes to run, but changing the query to:

    Select distinct
    table1.column, table2.column, table3.column
    from table1, table2, table3, table4
    where
    table1.pkey = table2.pkey and
    table2.pkey = table3.pkey and
    table3.pkey = table4.pkey and
    table4.pkey = table4.pkey and
    table4.column like 'somevalue'

    causes the query to run in 3 seconds, can someone please explain why?

    Thanks in advance
    Last edited by Bruce Trimpop; 08-10-2004 at 04:25 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's interesting ... can you show us the explain plans for those queries?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    why are you not joining ALL like columns??
    1 to 2 but not 1 to 4??

    PHP Code:
    SELECT DISTINCT 
           table1
    .COLUMNtable2.COLUMNtable3.COLUMN
      FROM table1
    table2table3table4
     WHERE table1
    .pkey table2.pkey
       
    AND table1.pkey table3.pkey
       
    AND table1.pkey table4.pkey
       
    AND table2.pkey table3.pkey
       
    AND table2.pkey table4.pkey
       
    AND table3.pkey table4.pkey
       
    AND table4.COLUMN LIKE 'somevalue' 
    - Cookies

  4. #4

    Query w/ explain plan attached

    Note the self join is commented out, but uncommented the query performs significantly faster.



    SELECT DISTINCT
    pcsv.provinv_id AS ProvIn_ID,
    ppm.PGMNME AS ProgNme,
    ppm.PROVPGM_ID AS Prog_ID,
    pcsv.svcdte AS SvcMnth
    FROM
    provinv pi,
    provpgm ppm,
    provconssvc pcsv,
    optList
    WHERE
    pcsv.provinv_id = pi.provinv_ID
    AND pi.PROVPGM_ID = ppm.PROVPGM_ID
    AND pi.InvMethod_OL = OptList.OptList_ID
    -- AND OptList.OptList_ID = OptList.OptList_ID
    AND UPPER(OptList.DSCR) LIKE 'ATTENDANCE FILE'
    Attached Images Attached Images

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    Can you take out distinct clause from query on both cases and let us know the results ??
    Raghu

  6. #6
    Join Date
    Oct 2002
    Posts
    182
    the UPPER clause in the where statement causes the full
    table scans. Somehow adding the commented line allows the CBO to
    do something different evidentally.

    BTW - why do you need the LIKE clause?
    Essentially that is an equals statement.
    - Cookies

  7. #7
    Join Date
    Oct 2002
    Posts
    182
    does this do anything different?
    PHP Code:
    select distinct 
      pcsv
    .provinv_id AS provin_id
      
    ppm.pgmnme AS prognme,
      
    ppm.provpgm_id AS prog_id
      
    pcsv.svcdte AS svcmnth
    from
      provpgm ppm

      
    provconssvc pcsv,    
      (
    select pi.provinv_idpi.provpgm_id
       from provinv pi
    optlist
        where pi
    .invmethod_ol optlist.optlist_id
         
    and upper(optlist.dscr) = 'ATTENDANCE FILE'pi
    where
      pcsv
    .provinv_id pi.provinv_id and 
      
    ppm.provpgm_id  pi.provpgm_id
    - Cookies

  8. #8
    No idea why they are using LIKE. I agree that unless multiple values are being passed in based on user selections the LIKE should be an =. I'm not really involved in the application and how the queries are being created. I was asked about this sort of in a casual water fountain conversion.

    BTW, cookie your rewrite still performs much like the original and adding the oplistid=oplistid to the derived table where clause made the query significantly faster?!

    Very curious

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Writing the same column (indexed) on both sides in the WHERE clause was a trick to tell the optimizer to use index that I used 14 years ago when RBO was the only choice in Oracle 6/7.

    How ever, with CBO, it should atleaset find out an optimal plan with less cost based on the available statistics.

    What is the Oracle release you are using?

    I guess the problem seems to be in PROVINV table. In both self-join and w/o self join, the plans show full table scan.

    Did you analyze the table OPTLIST in between 2 explain plans.
    Because I see the difference in values in cardinality for the OPTLIST table. One shows 3495 and other 2.

    Does the index IDX_OPTLIST_UPPER_DSCR include OptList_ID column also?

    Do one thing.

    Create a concatenated index on provinv
    on (invmethod_ol, provpgm_id, provinv_ID).

    And add a HINT ORDERED
    Code:
    SELECT /*+ ORDERED */ 
          DISTINCT
          pcsv.provinv_id AS ProvIn_ID,
          ppm.PGMNME AS ProgNme,
          ppm.PROVPGM_ID AS Prog_ID,
          pcsv.svcdte AS SvcMnth
    FROM
       optList,
       provinv pi,
       provpgm ppm,
       provconssvc pcsv
    WHERE
           pcsv.provinv_id = pi.provinv_ID
       AND pi.PROVPGM_ID = ppm.PROVPGM_ID
       AND pi.InvMethod_OL = OptList.OptList_ID
    -- AND OptList.OptList_ID = OptList.OptList_ID
       AND UPPER(OptList.DSCR) LIKE 'ATTENDANCE FILE';
    Tamil
    Last edited by tamilselvan; 08-11-2004 at 03:52 PM.

  10. #10
    Hi Tamil,

    We're using 9i Enterprise, I'll let you know what you're suggestion does.

    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width