How I Reduced My Oracle SQL Execution Time from 110s to 2s

The Problem

Recently, I encountered a performance bottleneck while writing a SQL query: the execution time was over 110 seconds. Here’s a simplified version of the query:

SELECT
  'A' AS type,
  COUNT(adl.is_attend) AS count,
  ROUND(SUM(CASE WHEN adl.is_attend = 1 THEN 1 ELSE 0 END) / COUNT(adl.is_attend), 2) * 100 AS rate
FROM
  attendance adl
  INNER JOIN (
    SELECT deptId
    FROM department
    START WITH deptId = '...'
    CONNECT BY up_daptId = PRIOR deptId
  ) nbd ON adl.deptId = nbd.deptId
WHERE 
  TO_DATE(adl.date, 'yyyy-MM-dd') >= TO_DATE(#{begin_date}, 'yyyy-MM-dd')
  AND TO_DATE(adl.date, 'yyyy-MM-dd') <= TO_DATE(#{end_date}, 'yyyy-MM-dd')

UNION ALL

-- other queries with different deptId and type...

The rest of the query consists of several almost identical blocks (differing only in deptId and type) combined with UNION ALL.

The backend dynamically passes #{begin_date} and #{end_date} as query parameters.

Performance Issue

• Each individual SQL block took ~18 seconds to run.
• With 6 blocks in total, the final query time reached over 110 seconds.
• The attendance table contains 13+ million records, and even simple queries take around 20s.

Optimization Process

Let’s break down the root causes and how I tackled them.

Root Cause 1: Data Volume

• The attendance table is massive. That’s unavoidable.
• Filtering by date helps, but it’s not enough.

Root Cause 2: Recursive Join

• The department table is joined using a recursive hierarchical query (CONNECT BY) to fetch all sub-departments.
• Recursive joins are inherently expensive, especially without indexes.

Solution 1: Index Optimization on deptId

The most effective optimization was to add an index on deptId:

CREATE INDEX idx_deptId ON department(deptId);

Result: Execution time dropped from 18s to 2s for each block.
This alone brought the total query time down dramatically.

Solution 2 (Optional): Avoiding TO_DATE() in WHERE Clause

Initially, we used TO_DATE() for date comparison:

TO_DATE(adl.date, 'yyyy-MM-dd') >= TO_DATE(#{begin_date}, 'yyyy-MM-dd')

However, calling functions on table columns prevents Oracle from using indexes. One workaround is to compare strings directly, as Oracle can implicitly convert strings to dates based on session settings:

adl.date >= #{begin_date} AND adl.date <= #{end_date}

This further reduced execution time from 2s to ~1s per block.

But beware: Implicit conversions depend on the NLS_DATE_FORMAT session setting. This can lead to:

• Loss of precision (e.g., minutes and seconds)
• Wrong century (due to two-digit years)
• Inconsistent behavior across environments

My Decision: Keep TO_DATE()

To ensure correctness and avoid unexpected results, I chose not to rely on implicit conversion and retained TO_DATE() in production code, even if it’s slightly slower.

Key Takeaways

• Always index your join keys, especially in recursive joins.
• Avoid applying functions directly on indexed columns in WHERE clauses.
• Be cautious with implicit type conversions — they can be a double-edged sword.
• Performance tuning is often about small, thoughtful changes.