-- 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 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
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.