Re: INDEX Question

Meinrad,

Can you eliminate the full table scans of message and display? This could help. Also, when was the table last analyzed? Have you tried 'All_rows' (default) as your hint instead of first_rows? Also, it looks like the sorts are still going to disk. If the initial/next extent for your temp tablespace is too low, that could slow things down with dynamic extension so you may wish to increase this. Is your temp tablespace of type temporary? If not, try

alter tablespace {TEMP_TABLESPACE_NAME} temporary;

Also, try the following parameters:

sort_write_buffers             4
sort_write_buffer_size         65536
sort_spacemap_size             512

Also, what are the table sizes?

meinrad.teufel_at_frqnet.de wrote:
>
> Hi,
> Thank you very much for your interest.
>
> The result can be of 250000 sets and it needs about 3 minutes (only
> selecting, not fetching). If I take the same statement without order by it
> takes about 3s.
>

3 rows processed
>
> I tried to rewrite the statement, but I didn't find a solution without UNION.
> So I enlarged the SORT_AREA_SIZE to a size with the best performance, but it
> is not good enough. Are there more Parameters which supports the sorting?
Received on Fri Feb 19 1999 - 18:29:13 CST