Search code examples
sqloracle-databaseunpivot

Oracle 11g unpivot very slow - low performance

My query's unpivot query performance is very low, it runs very slowly and takes around 11s to execute, while the base query that it's based on takes around 60ms to execute.

The base query is complex, with several joins, but it returns only a few rows.

Unpivot operates on ~7 group-by columns and unpivots around 40 columns.

Explain query plan shows no details, only that the cost of unpivot is very high.

Solution

  • One thing to try is to use WITH clause for the base query.

    Instead of

    SELECT * from (SELECT my_data, ...) UNPIVOT (...)

    Try

    WITH base as (select my_data, ...) select * from base UNPIVOT (...)

    In my case it reduced query time by a factor of 10.