Order by Slowing down

[fossil]

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,

[chrisrlong]

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