Order by performance issue

[fighter]

I have a table with 3 lac rows,
I need to select all rows from this table for my application.

Normal query w/o order by runs fine but
when used w/ an order by clause gives an error
Ora:01652 insufficient space in tablespace TS_NAME.

Someone Please advice.

[mike9]

http://www.google.com/search?hl=en&q=Ora+1652

[fighter]

thanks for the help mike9.

I created an index on the order by column,
and did not recieve any error.

Is this the right thing to do or any other solution you can suggest.

[mike9]

with the few information I got it's hard to say if it is the best solution.

Would be interessting to see the queries, their explain plan, the version of Oracle you are using and the kind of application. Also it would be interessting to know the size of your sort_area_size or pga_aggregate_target and the size of your temp tablespace.

[dbasan]

thanks for the help mike9.

I created an index on the order by column,
and did not recieve any error.

Is this the right thing to do or any other solution you can suggest.

when you give order by on such a table it needs temp tablespace space to sort the data according to your query to give you the required result set. When it does not have the required space it gave the ora error 1652.

do the kind of R&D and know do you really need the index? does it give you the time benefit?

[tamilselvan]

Also remember that adding new index may break or make other queries to run slow or fast.

You need to test the appln one more time.

Tamil