Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unhappy Unanswered: ORDER BY Performance

    We have a SQL like this -

    SELECT ...
    FROM files
    WHERE ....
    ORDER BY files.app_short_name

    We have a Unique index files_u2 like this -

    files.file_name 1
    files.subdir 2
    files.app_short_name 3

    This ORDER BY is taking around 7 seconds to complete and is not using the above mentioned index at all.
    Also, the files table has around half a million recordw which get filtered to
    around 1000 by the WHERE clauses.

    Is there a way to make the ORDER BY perform better, may be using the index ?

    Am using Oracle 8i/9i.

    Please help.

    thanks,

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Look at your where clause. The order by is performed on the result set, not on the base tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unhappy

    beilstwh,

    Without the ORDER BY clause, the query performs very well (0.5 sec),
    its the ORDER BY thats taking time to get executed.

    When i have an index on the cols in the ORDER BY clause, i expect this index to be used, right ?
    Am confused as to why its not picking up the index, the table and its indexes are analyzed and are healthy.

    Thanks for your interest.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The problem is that once you get a result set, how can it use the indexes on the base tables. It must perform a sort on the result set itself.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    15
    This is the query -

    SELECT /*+ first_rows */
    files.APP_SHORT_NAME PRODUCT,
    files.SUBDIR DIRECTORY,
    files.filename FILENAME,
    vers.version VERSION
    FROM
    ad_patch_run_bugs prb,
    ad_patch_run_bug_actions rba,
    ad_patch_common_actions cact,
    ad_files files,
    ad_file_versions vers
    WHERE
    prb.patch_run_id = 234
    and rba.PATCH_RUN_BUG_ID = prb.PATCH_RUN_BUG_ID
    and rba.executed_flag = 'Y'
    and vers.FILE_VERSION_ID (+) = rba.PATCH_FILE_VERSION_ID
    and files.FILE_ID = rba.FILE_ID
    and cact.COMMON_ACTION_ID = rba.COMMON_ACTION_ID
    and cact.ACTION_CODE in ('copy','forcecopy','RU_Update')
    ORDER BY files.app_short_name

    Commenting out the ORDER BY improves the performance tremendously, so i guess
    it doesn't take much time to bring up (atleast the first few as shown in the hint) records.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What is your sort_area_size?
    Are you sorting in memory or on disk?
    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
    Jul 2003
    Posts
    2,296
    you can't do FIRST ROWS hint with an order by can you???
    doesn't that defeat the purpose?

    ie: I have to order ALL ROWS and THEN display them

    please post an explain plan with statistics:
    set autotrace traceonly explain statistics
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by sjanarth
    This is the query -

    SELECT
    vers.version VERSION ...

    WHERE
    and vers.FILE_VERSION_ID (+) = rba.PATCH_FILE_VERSION_ID ...
    Also, you have an outer join above.
    Why even have this since you are listing
    those columns that match and do not match.

    I really don't like that outer-join thing.
    Also, there is no link between ad_files and ad_files_version?????
    looks like a cartesion product there.

    here is my rewrite
    PHP Code:
    SELECT   
         files
    .app_short_name product
         
    files.subdir DIRECTORY,
         
    files.filename filename
         
    vers.VERSION VERSION
    FROM 
         ad_files files
    ,
         
    ad_file_versions vers,
        (
    select 
             rba
    .patch_file_version_id,
             
    rba.file_id
         from 
             ad_patch_run_bugs prb
    ,
             
    ad_patch_run_bug_actions rba,
             
    ad_patch_common_actions cact
         where
             prb
    .patch_run_id 234
             
    AND rba.patch_run_bug_id prb.patch_run_bug_id
             
    AND rba.executed_flag 'Y'
             
    AND cact.common_action_id rba.common_action_id
             
    AND cact.action_code IN ('copy''forcecopy''RU_Update')) c
    WHERE
         vers
    .file_version_id(+) = c.patch_file_version_id
         
    AND files.file_id c.file_id
    order by 1

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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