Optimize Oracle order by query
You're sorting five million rows including about one hundred thousand clobs. It is unlikely you'll be able to do that in memory; if you run the query in SQL*Plus with stats on you should see the query writing out to disk a lot.
One way of improving the performance will be to add more GB to your buffer cache, but that may be an expensive option and not a quick one either.
Building a composite index on erp_in(indate desc, corr_id, interface) could help as well, because the results from the driving table will be pre-sorted and so the ordering should take less work. Partitioning by INDATE might have a similar effect, but partitioning is a chargeable extra to the Enterprise Edition and hence is not a cheap fix (gobs more memory will probably be a lot cheaper).
Your reference to archiving old data suggests that you don't actually want to retrieve all the records. If this is the case then reducing the size of the result set with a WHERE clause would help a lot. The easiest way to tune something is not to do work in the first place.
Adding a primary key and ordering by it won't reduce the amount of effort required to to the actual sort.
"so should I partition by date? will it help without adding WHERE clause on INDATE field"
It depends. Partitioning introduces some physical organization of the table, so the rows will (at least) require less sorting. How much less depends on the granularity of the partition: partition by a range on one day and the table is pretty much already in INDATE order, partition by a range of one year and it's much less so.
However, bear in mind that partitioning is not primarily a performance option. It is an option for managing data, especially loading, and availability. In fact, it may downgrade the performance of some queries, queries which don't suit the ordering applied by the partition key.
So, should you partition by date? Not a question we can answer. Answering it requires the sort of in-depth knowledge of your system which you have and we lack. But if you have the licenses it is certainly an option you should investigate and benchmark.
Are you really fetching so many rows to web server? If yes, review your code to narrow down to what's required.
- Try archiving old data to another table based on datetime. Re-write logic to fetch older data only when required.
- As others have mentioned, indexes / keys should help a mile in most cases
If you can't do any of the above, another ugly solution (not sure if it will not go worse) is to create in-memory table, filter and fetch what's required and then fetch CLOB data.
Have you experimented with additional indexes? I have done something similar and I have used a clustered index on the indate DateTime field. This is on a premise that most queries will be bounded to some time period and that rebalancing of B-trees will not be an issue as most inserts will with increasing key values. Get the execution plan for your query and look if you could not optimize it a bit.