Optimize rows to columns conversion

Question

I have a big Oracle table (around 40 million rows) that looks like this:

ID Name Question Answer Reason 
3 Name1 1 Yes blah blah
3 Name1 2 No NA
3 Name1 3 No NA
3 Name1 4 Yes blah2
3 Name1 5 Yes null
3 Name1 6 Yes blah3
3 Name1 7 No null
6 Name2 1 Yes blah4444
6 Name2 2 No null
6 Name2 3 Yes blah3
6 Name2 4 NA blah5
6 Name2 5 Yes null
6 Name2 6 Yes blah6
6 Name2 7 NA null

I need one row per ID i.e. I will need to add columns for each question's answer (there are 7 questions per ID) and each question's reason. I need to make it look like this:

ID Name Q1 Q1-Reason Q2 Q2-Reason Q3 Q3-Reason etc.
3 Name1 Yes blah blah No null 
6 Name2 Yes blah4444 No null 

My query currently looks like this:

select
 A.ID,A.NAME,B1.Q1,B1.Q1-REASON,B2.Q2,B2.Q2-REASON
from
 TABLENAME A
inner join
 (
 select distinct C1.ID,C1.ANSWER as Q1,C1.REASON as Q1-REASON
 from TABLENAME C1
 where C1.QUESTION=1
 ) B1 on B1.ID=A.ID
inner join
 (
 select distinct C2.ID,C2.ANSWER as Q2,C2.REASON as Q2-REASON
 from TABLENAME C2
 where C2.QUESTION=2
 ) B2 on B2.ID=A.ID
...
...

However, as the table is huge, this is taking a VERY long time to retrieve the data. Could someone suggest ways to optimize this query? Any help would be appreciated!

I'm on Oracle 10g and SQLDeveloper 4.0.2.15

• ypercubeᵀᴹ: Do you really need all those DISTINCT? Isn't the (question, id) combination unique?

Answer 1

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table.

Since you have a limited number of questions (7), you can easily hard-code the query:

select
 id,
 name,
 max(case when question = 1 then Answer end) Q1_Answer,
 max(case when question = 1 then Reason end) Q1_Reason,
 max(case when question = 2 then Answer end) Q2_Answer,
 max(case when question = 2 then Reason end) Q2_Reason,
 max(case when question = 3 then Answer end) Q3_Answer,
 max(case when question = 3 then Reason end) Q3_Reason,
 max(case when question = 4 then Answer end) Q4_Answer,
 max(case when question = 4 then Reason end) Q4_Reason,
 max(case when question = 5 then Answer end) Q5_Answer,
 max(case when question = 5 then Reason end) Q5_Reason,
 max(case when question = 6 then Answer end) Q6_Answer,
 max(case when question = 6 then Reason end) Q6_Reason,
 max(case when question = 7 then Answer end) Q7_Answer,
 max(case when question = 7 then Reason end) Q7_Reason
from tablename
group by id, name;

See SQL Fiddle with Demo

Answer 2

This question is a hardy perennial on the Oracle lists. The PIVOT operator only came in in 11g, so that's no good to you. You might start by looking here (also gives non-PIVOT solutions - oracle-base is an excellent tips and snippets site).

The Oracle forums FAQ has a "How do I convert rows to columns" section here. An interesting application of windowing functions is covered here (Sys_connect_by_path). You can also use the MODEL clause - covered here. Here is also of interest - the Orafaq site is also an excellent site for all things Oracle - they have forums similar to this one, but focussed exclusively on Oracle - I've found them very good in the past.

As for performance, I suggest that you first asses which methods provide you with the correct answer and then go on to test them for performance. Just as an aside, this might also be of interest.