How to Change Case vs Decode in Oracle Performance

How to change case vs decode in Oracle performance? Performance change oracle decode vs case is simple and easy. DECODE was introduced in early Oracle release but CASE was introduced later in Oracle 8.1.6. here I hope to test performance validation between case and decode function and statement.

decode can easily visibly change the SQL results.  Simply case use for the below type of situation. The case function is more readable on the below options:

– More readable than DECODE
– Complex decision points

Example for case vs decode in Oracle performance

Here we introduced Purchase_order_table with millions of records. We just introduced two columns.  Order_state use a different type of states and that column going to be used in decode and case statements

--Create table
CREATE TABLE purchase_order_table(
    order_id VARCHAR2(50) ,
    order_state VARCHAR2(50) ,
    PRIMARY KEY(order_id)
);
--

-- Insert sample data into table
declare 
  -- insert 100000 records into database
  i integer:=0;
    type namesarray IS VARRAY(10) OF VARCHAR2(50); 
  names namesarray; 
begin
  -- States for order state
      names := namesarray('Pending', 'Confirmed', 'Closed', 'Cancel', 'partialComplete','Completed','New');


LOOP
   i:= i+1;
   INSERT INTO purchase_order_table (order_id, order_state) VALUES ('Po-'||i,names(MOD(i,7)+1));
   EXIT WHEN i > 999999;
END LOOP;
end;
--

Decode Query Performance

Below is the sample decode statements for checking whether the order is still not completed or still in progress. I want to take 1 if it is InProgress else 0. The below query returns 1 or 0 as a result.

-- 
select decode( order_state, 'Pending','1', 'Confirmed','1', 'Closed','0', 'Cancel','0', 'partialComplete','1','Completed','1','New','1' ) 
from purchase_order_table
--

The return column is only available at 1 or 0 and according to my database configuration, it took nearly 1 second.

I want to check explain plan for DECODE statements. Below is the cost showing 929.

Case Query Performance

Next hope to write a query using a CASE statement but expect the same output. Next, check how much time took for this query.

--
select case order_state 
when 'Pending' then '1' 
when 'Confirmed' then '1' 
when 'Closed' then '0' 
when 'Cancel' then '0' 
when 'partialComplete' then '1' 
when 'Completed' then '1'
when 'New' then '1' end  
from purchase_order_table;
--

According to below results set time took again nearly 1 seconds for millions of records.

I want to verify it using Explain Plan. Surprise!  Hmm yes, it is the same right? Yes, also Cost of the query is also the same as the CASE statement. Therefore, looks like it’s a similar time for query execution and it is confirmed with Explain Plan. Therefore time and the cost wise there is no considerable difference in performance.

Note: Index in a column or as a function base index may change the performance

So according to the investigation, there are no considerable differences between CASE vs DECODE in Oracle performance whether you use CASE or DECODE. You can use any of them according to your situation. Case is an improvement of decode and I would like to explain much more detail on this topic. If performance is similar why do we have two? below illustrate when to use case vs decode in oracle

When to Use CASE vs. DECODE:

• CASE is designed for complex situations that have a lot of conditions and complex functional queries in logic. Also, it also improves the readability and clean code  SELECT (e.g., WHERE, HAVING).
• DECODE is designed with one-to-one mapping conditions and simple conditions in select statements, but good to ignore complex conditions and multiple conditions. Readability is low with decode.

USE CASE

1. Complex Conditions: When you have complex conditions with different logical expressions then CASE with a good choice for you. Some conditions are hard to add with DECODE.

-- Example:
SELECT company, 
CASE WHEN sales_amount >= 1000 THEN 'High Sales' 
WHEN sales_amount >= 500 THEN 'Moderate Sales' 
ELSE 'Low Sales' END AS sales_type 
FROM sales_orders; 
--

2. Dynamic Sorting: Case statements can be used in sorting, If you want to sort by different parameters case help to do it with conditions.

--Example:
SELECT 
  employee_id,
  name,
  salary
FROM employees_table
ORDER BY
  CASE 
    WHEN :sort_by = 'name' THEN name
    WHEN :sort_by = 'salary' THEN salary
  END;
--

Use DECODE:

1. Simple Value Replacements: DECODE is good with one-to-one mapping according to the parameter value. Then it improves the readability of decode as well.

--Example:
SELECT product_name, DECODE(purchase_amount > 1000, 1, 'High', 'Low') AS category_type 
FROM purchase_order; 
--

2. Conciseness: Some cases in the database table column have low cardinal values in a column. Those are not readable to others But you need real value it to show to clients.

--Example:
SELECT order_id, DECODE(order_status, 'P', 'Pending', 'S', 'Shipped', 'C', 'Cancelled') AS orader_status 
FROM purchase_orders;
--

3. NULL Handling: NULL is everywhere in SQL therefore handling NULL in queries is important. In those kinds of situations, DECODE is a great option because it is straightforward work.

--Example
SELECT order_id, DECODE(order_status, NULL, 'Pending', order_status) AS order_status 
FROM purchase_orders;
--

Conclusion

I would be happy to say they are about the same… at least From a performance wise seems they are about the same, According to investigations we can say case vs decode in Oracle performance is nearly the same. There can be differences with location where we need to use them and what the intention of these keywords

Here I want to check the performance of Oracle decode vs case statement. if you would like we can add an index on the decode function as a function base index and that will improve the performance I would like to add a helpful article that is confirming with you.

https://stackoverflow.com/questions/3193692/case-vs-decode

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2998539000346762310