Oracle order by index is a good option to sort data by improving performance. Sorting is used when you want to order the data in a specific manner but if the data load is very high then sorting takes a big portion of time from full query execution time. there you need to improve the order by performance in the query.
In this article, we discuss improving the performance by using Oracle order by index. If you wrote a normal query without an index optimizer has to read the full table to sort out the dataset. Here we are looking to reduce time by adding an index to sorting columns. It ignores the full table scan and uses an index to show the results directly. Because indexes are already in an ordered way.
Here I would like to explain 5 simple examples of creating an index for ordering with corresponding SQL code and how to create an oracle order by index in a correct way. Because sometimes we have an index but not in an optimal way. Then we have improvement but there can be more.
Oracle ORDER BY Index Examples
Example 1: Basic Index on ORDER BY Column
This is the most basic example of an index. Suppose you have a table called Employee and column as Salary then you would like to sort the employees by salary. Then you can create an index on the salary column to improve the performance.
--Query to fetch data and ordered by salary SELECT * FROM sales ORDER BY order_date; -- Create an index on the order_date column CREATE INDEX idx_order_date ON sales(order_date); --
I created the B-TREE index because there are big variations can be there in the column. But you have to stick to a situation to create an index. Because there are index types like DOMAIN, BITMAP, and more. Those are working well according to the data set. Therefore creating an index in an oracle not only improves the performance but analyzes the dataset and creates an appropriate type of index.
Example 2: Covering Index
The covering index in Oracle includes all the columns required for a query. This is not valid if you use a lot of columns in the query. Because creating columns with including a lot of columns reduces the performance when modifying data. Therefore I think this is better for small queries.
-- Query to retrieve data SELECT order_id, order_date FROM orders WHERE customer_id = 123 ORDER BY order_date; -- Create index for order_date including Customer id and orader_id CREATE INDEX idx_covering_orders ON orders(order_id, order_date ,customer_id); --
Covering Index fulfills the query without accessing the database table. all the data in the index therefore no need to go and fetch data from a database table. This is reducing the I/O call for the database. Create in in most variation available column first. That also helps to improve the performance
Example 3: Reverse Index for Descending Order
This is a simple concept of index. You may know indexes are in order. But you prefer to load data in descending order and create an index also in descending order. Then query can use index order as it is. If you have an index on ascending order for other purposes then no need to create a new one for descending order because Oracle uses it but creating two indexes in both orders may not help much for performance.
-- Create a DESC index on the order_date CREATE INDEX idx_reverse_order_date ON sales(order_date DESC); -- Query fetch in descending order SELECT * FROM sales ORDER BY order_date DESC; --
Example 4: Composite Index
If you want to order with multiple columns create an Oracle order by index in the same order in the query.
-- Create a composite index CREATE INDEX idx_order ON purchase_order_table(order_date, customer_id); -- Query to fetch and ordered by order date and customer ID SELECT * FROM purchase_order_table ORDER BY order_date, customer_id --
Example 5: Functional Index
The function base index is one of the index types you can use when ordering data.
-- Create a functional index CREATE INDEX idx_order ON purchase_order_table(round(cost)); -- Query to fetch data SELECT * FROM purchase_order_table ORDER BY round (cost);
NOTE: IF you want to order by using JOIN columns then the index is not used by Oracle.
Order by Index Complete Analysis -IMPORTANT
I will do the complete analysis on creating Oracle order by index and do the performance improvement. If you don’t have any index on order by related columns, then Oracle does the full table scan for sorting. I insert 1 million data into the sample table and let’s improve the performance in a query by adding an index.
Sample data
--
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;
Sample Query
SELECT order_id, quantity, order_date, need_date FROM purchase_order_table WHERE need_date < sysdate+5 ORDER BY need_date, order_date;
In this demo query, you can see need_date and the order_date in order by clause and need_date in the where clause as well. Let’s try to Explain Plan for this first. According to the Explain Plan oracle does the full table scan for filtering and then does the sorting again.
I will create an index for need_date because of need_date in my where clause. Hope the query will improve the performance by using the index.
-- CREATE INDEX idx_need_date ON purchase_order_table (need_date); --
Index created and then let’s explain what looks like. It Is reduced rgt? COST was also reduced and clearly now Oracle does the INDEX RANGE SCAN and uses an index for it. BUT still, a problem here is SORT ORDER BY took considerable time. I hope this is still able to improve. So, I will modify the index by adding order_date into an index. Let’s look at any time reduction for it.
-- CREATE INDEX index_date ON purchase_order_table (need_date, order_date) --
Now explain plan and say I NO NEED SORT ORDER BY operation anymore. So, Oracle has done the sort by indexing itself. So, we got some gain from it, and time reduced in our query. This is how we can use the index for sorting and ordering data by multiple columns. Because of index are already ordered then oracle access the record by ROWID.
Hope you that is enough. YES if you are writing usual queries and those are not used frequently this is enough I guess. BUT according to the situation, you can decide where you need satisfied. Now I will create a CONVERING INDEX. This is a need to create when you have queries that are running frequently. Consider about index range and memory size for the index.
-- CREATE INDEX index_date ON purchase_order_table (need_date, order_date,order_id,quantity) --
You can see I include all the columns in the index. According to my earlier Explain Plan Out of COST, most of the time took for I/O COST. Therefore, I want to reduce that cost. It is because of remaining columns need to fetch from a table. That I/O time can be reduced by adding those columns to the index.
Now you can see COST reduced and other performance matters like Bytes, and CPU COST are reduced due to our new index. BUT we do not recommend this to be done every time because record INSERT and UPDATE may take extra time to update due to this type of composite index.
This is Wrong Create Order by Index.
As we discussed composite index can be created any way this is the wrong way of creating an index for our example because this does not get maximum performance by the index
-- create INDEX index_date ON purchase_order_table (order_id,quantity,order_date,need_date) --
It is still scanned through the index. you can see it in the Explain Plan by saying INDEX FULL SCAN but that is not what we expected. So mindful when you create an index with the correct column order.
Conclusion
oracle order by index is a great way of performance improvement. An index can be created in different ways like B-TREE, DOMAIN, BITMAP, Function base or there are a lot. But create an index by analyzing the query and the data load in the table. Also, prefer to improve the query according to the situation where you going to use this.
Sometimes index perform well on fetching but Modification has some problems. So balance of sort of operation in the table and do the performance. There are a lot of performance-related articles on this site. Read and comment on our improvement as well.
Resources
https://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by
https://docs.oracle.com/cd/E14004_01/books/PerformTun/PerformTunCustConfig14.html
