ORDER BY performance when executing a query in Oracle

Question

I have been working on a Spring application that connects to an Oracle database.

After three years, the amount of records in our tables are so much bigger that the response time of queries is so bad and our customer is dissatisfied.

So, I searched and got this URL for Oracle performance tuning.

The factor's 22 of this URL tells to NOT use ORDER BY in the query when the response time is important. So, if I omit ORDER BY from my query, the response time is more than half than with ORDER BY.

But I can not omit ORDER BY from my query because the customer needs sorting.

How do I fix my problem, so that I have ordering and a response time?

Answer 1

The factor's 22 of this URL tell that DO NOT use order by in the query when the response time is important, I omit order by from my query for this the response time is more half than the first

On the Internet, you should always question every advice you get.

In order for the ORDER BY clause to be fast, you need to use the right index. Make sure the sorting is done using a database index, therefore avoiding a full-table scan or an explicit sort operation. When in doubt, just search for SQL performance issues on Markus Winand's Use the Index Luke site or, even better, read his SQL Performance Explained book.

So, you should make sure that the Buffer Pool is properly configured and you have enough RAM to hold the data working set and indexes as well.

If you really have huge data (e.g. billions of records), then you should use partitioning. Otherwise, for tens or hundreds of millions of records, you could just scale vertically using more RAM.

Also, make sure you use compact data types. For example, don't store an Enum ordinal value into a 32-bit integer value since a single byte would probably be more than enough to store all Enum values you might use.

Answer 2

one of the best sulotion that Markus Winand metion in his blog that is using pipelined order by and it's detail in in the this link