Order by Slowing down - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Order by Slowing down

  1. #11
    Join Date
    Dec 2002
    Posts
    36
    Thanks for reply.

    I have

    "workarea_size_policy=auto"
    "pga_aggregate_target=800M"
    "parallel_max_servers=0"

    -- All the sorts are happening in the memory.

    -- No parallel servers/Parallel Query as the tablespaces
    are created on some big Hard Drives.

    -- Size of the table 21G

    -- 30 Million rows in the table

    -- WITH ORDER BY on the SELECT, the result comes back
    after 1 hour.

    -- WITHOUT ORDER BY the result starts immediately.

    -- I don't have NOT NULL constraint on custnum, I am sorry for that
    ,however, I checked the plan of the table in other database
    where the CUSTNUM has NOT NULL constraint has B*TREE index
    on it and NSL_SORT='BINARY' but still doing the sort.



    explain plan set statement_id='8' into plan_table for
    select custnum from customer_source_mv
    where g_type_cat='K' and dnm='Y' and g_Type_caller='S' and ind_list='0110' order by custnum;


    SELECT STATEMENT Cost = 394
    SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID CUSTOMER_SOURCE_MV
    BITMAP CONVERSION TO ROWIDS
    BITMAP AND
    BITMAP INDEX SINGLE VALUE CUST_SRC_G_CALLER
    BITMAP INDEX SINGLE VALUE CUST_SRC_IND_LIST
    BITMAP INDEX SINGLE VALUE CUSTSRC_G_CAT
    BITMAP INDEX SINGLE VALUE CUSTSRC_DNM


    Please put some light on this situtation.



    Thanks,
    fossil

  2. #12
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Now make sure to compare apples and apples. The ORDER BY must first SELECT *all* the rows before it can do the sort. So how long does it take to SELECT *all* the records without the ORDER BY - not just the first record, but all of them?

    Also, exactly how many records are being returned that it take a freaking hour to select and sort them!??! Of what possible use would this many records be? I'm thinking you have more of a requirements problem than a technical one.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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