-3

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.

Dariusz
  • 21,561
  • 9
  • 74
  • 114
  • 1
    Please provide a [MCVE] including the DDL statements for your table, DML statements for your sample data, your query and the explain plan. Otherwise you are preventing other people from being able to offer answers to this question as "Questions without a clear problem statement are not useful to other readers." – MT0 Nov 21 '17 at 10:26
  • I'm providing a possible solution to an otherwise ungooglable problem; I spent hours trying to solve it. The question's already highly indexed in google and it gives people a possible solution to a specific (title) problem. And a MCVE would take hours and I couldn't possibly expect it to actually show the problem, as Oracle's query analyzer's bound to do something differently there than on a large production database with gathered statistics. I don't care if you close the question or I lose rep or whatever, because I believe that if there's 1 person whom I help it's worth it. For developers! – Dariusz Nov 22 '17 at 07:24

1 Answers1

1

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.

Dariusz
  • 21,561
  • 9
  • 74
  • 114
  • 1
    If you are going to self-answer this question then please provide a [MCVE] to back up your assertions. – MT0 Nov 21 '17 at 10:23
  • 1
    This worked perfectly. I am interested to know why it is working? – omar Jul 01 '19 at 05:36