DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Slow view

  1. #1
    Join Date
    Nov 2003
    Posts
    89

    Slow query

    Hi,
    I have table of 8M records. The below query takes 2 min for 2 M record output . What should I look in below query to tune.

    Code:
     SELECT  DISTINCT
            UPPER(POL_PFX) POL_PFX,
            DECODE(SUBSTR(pol_num, 1, 3),
                   '00X', SUBSTR(pol_num, 3, 7),
                   '00T', SUBSTR(pol_num, 3, 7),
                   SUBSTR(pol_num, 5)) POL_NUM,
            UPPER(REINS_CODE) REINS_CODE,
            MP_INCEPT_DT,
            MP_EXPIRE_DT,
            MP_INS_NAME
    FROM    MF_MIS_A
    Thank You
    Last edited by srt; 01-18-2006 at 12:57 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you sure that you need the DISTINCT?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2003
    Posts
    89
    Yes dave I need the distinct. Without distinct it returns rows right away, but the problem is comming with distinct. What would be the correct solution when using distinct.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    See if you are getting disk-based sorts, using "set autotrace traceonly". If so we could look at modifying the memory management to sort in memory.

    Also, you might like to look at this: http://oraclesponge.blogspot.com/200...le-faster.html
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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