Re: index

"dead" <OVOIZBACI-vlasic_at_vodatel.net> wrote in message news:<ceb1n6$2i3$1_at_sunce.iskon.hr>...
> question :
> I have Primary key(index) on field ABC1 in table TABLE1
> if I put sql command : Select * from TABLE1
> I have result in 1 sec but in 'natural' order, not order by ABC1
> If I put sql command with order : Select * from TABLE1 order by ABC1
> I need 150sec to have result
> It's sam if I put /*+FIRST_ROWS*/
> Does I need to change some parameters on database, index or in sql command
> or it is normal on table of 800.000 col.?

I would suspect sorts going to disk rather than in memory. Querying V$SYSSTAT and V$STATNAME can help you determine this:

select n.name, s.value
from v$statname n, v$sysstat s
where n.statistic# = s.statistic#
and n.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');

I would imagine your 'sorts (disk)' value to be rather high, indicating your sort_area_size is far too small for the tasks it is being asked to perform. Change this setting in your init.ora file (pre-9i) or use the following command on 9i and 10g (presuming you are using an spfile):

alter system set sort_area_size=<some new value here> scope=spfile;

Not knowing the value to which this parameter is currently set makes it impossible to make suggestions for adjustment.

David Fitzjarrell Received on Thu Jul 29 2004 - 17:08:17 CDT