Oracle order by performance is considered more often when doing performance improvements. There are lots of techniques are help in tuning and sorting queries but it depends on several factors in the database. Here we introduce common and easy ways of improving the oracle order by performance.
But some problem needs deeper analysis if these options are not suited for your performance. You can add a comment below I would like to help with this.
First, I would like to add when Oracle doing sorting. That will help you when reading the remainder of this article. ORDER BY clause is the not only time to sort the rows in a table. Below are the automatically performing sort of the rows inside the process.
- SQL ORDER BY clause
- SQL GROUP BY clause
- CREATE INDEX in Oracle
- When use of UNION, INTERSECTION, or MINUS clauses
- SQL DISTINCT clause
Simply if the database wants to sequence the rows in the table oracle does the sorting. Oracle uses the sort_area_size for a small dataset. This is done very quickly in the memory. Sometimes sort_area_size is not enough for sorting then those situations are handled by creating a TEMP table in the database.
This TEMP table sorting takes more time compared to memory sorting. Therefore, memory size also improves the sorting time in queries.
I discuss common ways of improving Oracle order by performance. Let’s start with well-known creating indexes.
1. Indexing for Sorting:
- Creating an index definitely improves the significant performance but as you know there can be drawbacks when inserting and modifications. Therefore be smart to create an index in the best way and try to modify the available index according to queries in the database.
- B-tree indexes are the best option for improving performance compared to BITMAP, DOMAIN, or other types of indexes. Here I try to explain the best way to use an index for sorting.
If there is a relevant index available sorting part is not needed. It’s because Indexes are already in sorted order. Therefore if you could create an index to match with ORDER BY condition in the query Oracle is able to use an index to order the rows. Oracle has no need to separate sorting time for ordering rows. Let’s have a simple example.
Oracle order by index Examples
My example purchase_order_table contains orders with dates. I insert 1 million records into the table using the below query. Then I would like to write one query without any index first.
--
CREATE TABLE purchase_order_table(
order_id VARCHAR2(50) ,
order_date DATE ,
need_date DATE ,
quantity NUMBER,
PRIMARY KEY(order_id)
);
declare
-- insert 100000 records into database
i integer:=0;
begin
LOOP
i:= i+1;
INSERT INTO purchase_order_table (order_id, order_date,need_date,quantity)
VALUES ('Po-'||i,Sysdate - round(dbms_random.value(1,400),0),Sysdate + round(dbms_random.value(1,400),0),round(dbms_random.value(1,1000),0));
EXIT WHEN i > 999999;
END LOOP;
end;
--
My query has two ORDER BY columns and one condition for filtering (need_date)
-- SELECT order_id, quantity, order_date, need_date FROM purchase_order_table WHERE need_date < sysdate+5 ORDER BY need_date, order_date; --
If we don’t have an index for need_date then Oracle does the full table scan anyway. Below is the explain plan for the above query.
I will create an index for the table now. I want need_date as an index now. It is because of need_date in my WHERE condition. But I would like to have order_date included in my index because it’s there in my order by clause.
-- CREATE INDEX index_date ON purchase_order_table (need_date, order_date) --
According to explain plan now table access by index rowid. Now you can see the cost is reduced because of an index. Just Creating an index is not the only way of improving Oracle order by performance. When creating an index you have to be smart about the best way to create an index.
2. Limit the Result Set
If you do not need all the data in the query, and index already available then use the FETCH FIRST clause to limit the number of rows. This reduces the time and the load for sorting data. The below query filters the first 10 records from all sets. It is confirmed by looking at explain plan.
-- SELECT * FROM purchase_order_table WHERE need_date < sysdate+5 ORDER BY need_date, order_date FETCH FIRST 10 ROWS ONLY --
3. Use Parallel Processing
Oracle changes the performance according to the HINTS. There is a hint for able to use when sorting. The PARALLEL hint can be used to process parallel. The below query returns all the data but compared to the first explain plan parallel processing reduces the time for Oracle order by performance
-- SELECT /*+ PARALLEL(4) */ * FROM purchase_order_table WHERE need_date < sysdate+5 ORDER BY need_date, order_date; --
4. Partitioning
If your database uses PARTITION then you can take advantage of partitioning on sorting.
-- SELECT * FROM purchase_order_table PARTITION (p1) ORDER BY need_date, order_date; --
5. Memory Allocation
When starting the article, I mentioned sorting can be done in the memory or the TEMP table. Check weather enough memory is allocated for SORT_AREA_SIZE parameter for sorting. This helps to reduce the time by sorting without using the TEMP table. Below is how to change the memory size.
-- ALTER SESSION SET SORT_AREA_SIZE = 1048576; -- 1 MB --
6. Materialized Views
oracle order by performance can change using materialized views. If data is more static then materialized views are suit for it Or you have to refresh data in particular intervals to update with the latest.
Materialized views are performed because of data is precomputed and sorted then store final dataset like a table.
-- CREATE MATERIALIZED VIEW sorted_purchase_orders AS SELECT * FROM purchase_order_table ORDER BY need_date, order_date; --
7. Caching Results:
This is again for Static data. If data is static, then caching is another solution for sorting. If data is used more frequently then the result set can be stored in the cache. Then we can use them directly from the cache without searching for fresh data.
8. Review Execution Plans:
The execution plan is one of the great tools for analysis performance. If the query has poor performance in the database execution plan shows the problem locations. But this only works for that situation, but the same problem can change according to the data load.
Therefore, an execution plan must be used in each problem and problem available database. It’s because of same problem does not raise in a single location if the application and code base are similar.
9. Consider Function-Based Indexing:
If you have a PLSQL function on an order by clause then you are free to use the function base index when sorting dataset.
-- CREATE INDEX idx_function_based ON table_name(UPPER(column_name)); --
10. Optimize SQL Queries
I added point 10 as optimized the query because of above 9 tips may not help some time but you can be smarter on writing queries. As an example, if a query wants to sort data then you going to filter sorted data again later. Then you’re doing extra sorting for unwanted data sets.
Then first try to filter out all the data set and then try to sort filtered data in the final.
Then your sorting time reduces freely. Write smart queries and analyze queries for best performance. Use tools like PROFILING, AWR REPORTS, Or other optimizing tools to improve Oracle order by performance.
Conclusion
Query performance changes according to the data load, hardware configuration, and how you write the query. The above tips are helping to improve the Oracle order by performance. But you must attend to each problem separately.
The above tips improve the Oracle ORDER BY performance and reduce the execution time. All may not suit you but some of them are common for all the databases.
When creating an index attention to which type of data is available in the table then you can use different types of indexes to improve the performance. Hope you comment and questions to improve our sites.
https://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by
