Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Do you really need all those DISTINCT? Isn't the (question, id) combination unique? –  ypercube Jul 24 at 11:34

2 Answers 2

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.