How to Add an Oracle Function Based Index: Unexpected Tips

Oracle function based index enhances the performance with queries that retrieve data using a custom index according to usage. This will improve the performance when consists of function or expression in where condition. You will learn how to create Oracle function-based index with examples and how to speed up the query performance with examples.

Introduction to Oracle function-based index

In Oracle or any other database, the most crucial role in optimization is the Index. Indexes perform well if you are used in the correct place. Sometimes indexes can give poor performance if you use them unnecessarily. But Indexes provide a faster way of retrieving data from a database.

The usual and standard way of creating an index is to use a table column or multiple columns to index. But in some complex cases, you may need a customized course of function base indexes to provide the best performance on your queries. That enables the database engine to locate rows quickly more effectively and efficiently.

What Are Function-Based Indexes?

The simple description of function-based index in Oracle is to create an index based on a function or expression to one or more columns in a table that you need to filter data by computed values. This computed value can be Mathematical calculation, String manipulation, or complex column operation.

Standard indexes use single or few columns in the index but if a complex application doing complex calculations with millions of data in a single table. In those situations, real-time calculation and filtering at the same time might increase the cost of queries. Therefore Function-Based Indexes are useful in situations like complex queries that deal with huge tables.

How to decide to need for a Function-Based Index

If you are experiencing poor performance in a complex query and the problem drill down to filtering row in a table due to complex Oracle or inbuilt function then this will help your performance:

1. Analyze Problem:  As I mentioned above first analyze the problem drill down the cause and identify the problem in the built-in Oracle function or a DETERMINISTIC user-defined PL/SQL function. A function may calculate or transform the value in the table.
2. Create the Function-Based Index: Create an index according to the result of the function as it is. Index associates with the result of complex function. The result will be indexed after you introduce it in your table.
3. Verify Optimization: Once the index is created, Oracle can use it to optimize queries by using your index to filter out the required rows. Once you insert or modify a row in the table index value will update according to your function values. That is one of the bad things in function index but Let’s discuss the disadvantages later in this article

Use Cases for Function-Based Indexes

This is not the usual way of creating an index but there are a lot of cases listed below with simple examples:

1. Substring Search: If you want to search or filter rows after completing the substring. Then a function-based index can be created to index substrings, this creates an index for the substring value for the column and it improves query performance.

-- Syntax 
CREATE INDEX idx_substr_1 ON customer_table(SUBSTR(last_name,3,4));
--

2. Case-Insensitive Searches: This is the usual case because of if column values may not be case-sensitive. But when you filter rows by the equal operator it is case sensitive. If those types of cases you have to use UPPER or LOWER keywords to change the case in a table column. Then create a function-based index if you want to perform case-insensitive searches

-- Syntax 
CREATE INDEX idx_case_insensitive ON product_table(UPPER(part_name));
--

3. Mathematical Calculations: There might be situations in which we are not filtering the column as it is. But we are doing some calculations for that column and according to value we filter the rows. Function-based indexes can store the result of the measure, once you filter the row by this value database engine reducing the need for on-the-fly computations.

-- Syntax 
CREATE INDEX idx_compute_1 ON products(unit_price * 0.9/100);
--

4. Date Manipulations: Dates and time is the next heavily used column type. When working with date/time columns, we have to filter by year, month, days or any other scenario then create function-based indexes to store derived date values

-- Syntax
CREATE INDEX idx_month_1 ON Orders(trunc(order_date, 'MM'));
--

5. Multi-Column Indexes: You are able to create function-based indexes on multiple-column concatenations.

-- Syntax 
CREATE INDEX idx_name_1 ON employees(CONCAT(first_name, ' ', last_name));
--

6. Deterministic Custom function index

If you want a function base index on a custom-created function that definitely helps to increase performance on a query. The custom function may have a huge complex. Think about millions of records in a table and need to filter by custom function. Then each and every row needs real-time execution for filtration.

--Syntax
create index custom_idx1 on my_table (treat(my_api.get_myfunction(para1,para2)  as value));
--

function based index oracle example on Modulus

Let’s explore a full example. In this example, I will create a function-based index for MOD to return the remainder (modulus) and filter by the result.

Think about having a table Purchase_Orders_table with an order_status column, and you want to count uncompleted orders by using the order_status. Below are my table columns and data insert query before moving into query execution. I hope to insert 1000000 records into to table for comparative results.

Columns
   key             OrderId      Varchar (20)            
   attribute       Order_Date   DATE                 
   attribute       Customer_Id  Varchar(100)            
   attribute       Order_State  Varchar (100)            
   attribute       Need_Date    DATE                 
   attribute       Created_Date DATE    

-- Hope Order States are like below
1 Planned, 2 Started, 3 in-progress,4 Shipped, 5 completed,

-- Use below query for insert sample records into table
-- i inserting 1000000 records into table for my example
declare 
  -- Local variables here
  i integer:= 1;
begin
  -- Test statements here
  LOOP
   i:= i+1;
   INSERT INTO Purchase_Orders_table (ORDER_ID, Order_date,CUSTOMER_ID,order_state,Created_Date,Need_Date,rowversion) 
   VALUES ('Po-'||i, TO_DATE(current_date - round(dbms_random.value(1,50),0)) ,'Cus_Id-'||round(dbms_random.value(1,9999),0),
   round(dbms_random.value(1,5),0),null,TO_DATE(current_date + round(dbms_random.value(1,250),0)) ,sysdate);

   EXIT WHEN i > 1000000;
END LOOP;
end;

          

Query

This is our query for the performance test

--
SELECT *
FROM Purchase_Orders_table
WHERE MOD(ORDER_STATE,5) != 0;
--

First, try to execute the query without an index and try to take a explain plan for a query.

Now I create the index to expect better performance for the query

-- Create a function-based index
CREATE INDEX idx_mod_search ON Purchase_Orders_table (MOD(order_state,5));
--

Now I have an index for modulus, Now I will execute the same query, and let’s see any improvement thereafter adding a function-based index. see the below images for the latest execution.

Earlier query optimizer selects a full table scan for the modulus function but now reduces the execution time because the optimizer chooses a full index range scan for modulus. If you closely look at the cost it reduces from a big amount to a small amount because of the function base index.

Function-based Index in Oracle Performance

• Execution Time Reduced
• Explain plan Cost Reduced
• CPU Cost Reduced
• Bytes Reduced

This is how to improve the performance in query by using the function base index, this results in fast queries compared to earlier. I have to say MOD is a small function. But you can have complex functions doing comprehensive mathematics and take the result for filtering. Think about those kinds of scenarios and use them accordingly.

If there is a function in which condition most probably performance can reduce. That can be an inbuilt PLSQL oracle function or DETERMINISTIC Custom user-defined function. Anyhow if you use a function base index then there is an improvement.

Function Based Index in Oracle Custom function

-- DETERMINISTIC function
FUNCTION Get_Order_period (
   order_date IN DATE , need_date IN DATE) RETURN NUMBER DETERMINISTIC
IS
   Date_Diff NUMBER;
BEGIN
SELECT TO_DATE(order_date, 'DD-MM-YYYY') -  
       TO_DATE(need_date, 'DD-MM-YYYY') INTO Date_Diff
FROM   dual;
   RETURN Date_Diff;

END Get_Order_period;

-- create index
CREATE INDEX idx_date_search ON Purchase_Orders_table  ( Purchase_Orders_api.Get_Order_period(need_date,Order_Date));

-- Query for execution
SELECT * 
FROM   Purchase_Orders_table
WHERE  Purchase_Orders_api.Get_Order_period(need_date,Order_Date)<100;

Benefits of Function-Based Indexes

Function-based indexes give a lot of value to performance by improving speed:

1. Improved Query Performance: By reducing online or Real-time calculations improves the performance of queries.
2. Reduced I/O and CPU Overhead: Function-based indexes reduce CPU overhead for computing complex calculations and sometimes queries need more I/O calls for those calculations. If you already stored computed value in an index then time reduces accordingly.
3. Flexibility: Speed is top performing and highlighted thin in today’s applications. But you have to do complex things and you need to perform well in those kinds of scenarios You can create custom indexes to your specific query DETERMINISTIC user-defined function according to your requirements, enabling you to optimize complex queries effectively.
4. Perform an index range scan: The query performs well because of normally scans the table by full table scan but speeds up the query by filtering the rows using an index range scan. This is surely reducing the time for scans.
5. Precompute values. If you have a query doing real-time computation for your query or the same query executes frequently then the function base index helps to reduce that kind of calculation.

Sometimes those kinds of calculations can be run on off-peak times and use precalculated values for queries.

Disadvantages of Function-Based Indexes

function-based indexes: There are considerable issues but compared to benefits decide what you most valuable for you.

1. The critical problem is every single insert or modification index value needs to be updated latest computation. This is one of the performance penalties for every write.
2. The performance of the query depends on the data load. The load can change over the period. Therefore, index performance can vary with time.

What are the Best Practices

While function-based indexes offer powerful optimization capabilities, they come with some considerations and best practices:

1. Index Selectivity: Make sure you select the most valuable and effective index for optimization.
2. Query Optimization: Oracle’s query optimizer is doing a great job of optimizing queries by using explain plan and other statistics. Make sure your index is used for filtering data.
3. Maintenance Overhead: Function-based indexes have problems in data modification therefore alert on those modifications having any considerable problems.
4. Data Consistency: Data gradually change over the period. You must ensure consistency of data over the period.

Conclusion

• Function-based indexes are a powerful mechanism to improve the performance of queries. There are drawbacks to modification data but still, you have a big advantage in selecting data from a table.
• You can have a function-based index on string manipulation, math calculation, and inbuild custom functions.
• This is creating a custom index accruing to the computed values on your function and that index is used for filtering data with complex functionalities.

Resources

https://www.akadia.com