5 Replies Latest reply: Dec 11, 2012 8:49 AM by Hoek RSS

    Unpivot or union

    Igor S.
      Hello folks,

      Cant find any artice on what is better to use unpivot or union all. I can achive same results using either but cant decide what would be better. For example a table has columns:

      Name 1, Phone1, Name 2, Phone 2. The result I want would be:

      Name 1, Phone 1
      Name 2, Phone 2.

      Best regards,
      Igor
        • 1. Re: Unpivot or union
          Hoek
          Define 'better'?
          Do you mean in terms of performance? => then simply run both queries and compare their execution plans or tkprof output.
          Do you mean 'better'as in: 'I'm looking for a Silver Bullit'? => They do not exist.
          Doe you mean something else? => Then provide more input, like the queries you have, the exact problem and database version you're running on, in short, everything listed here: {message:id=9360002}
          • 2. Re: Unpivot or union
            Igor S.
            I am looking for pros and cons of each aproach since I cant find any documentation that compares them. What I am looking for is better understading of these two aproaches so I can chose between them. (dont need silver bullet)

            Best regards,
            Igor
            • 3. Re: Unpivot or union
              John Spencer
              Using an unpivot type query would only access the table once while a union would access it twice in your example, so I would go for the unpivot myself.
              SQL> with pivoter as (
                2     select 1 occ from dual union all
                3     select 2 from dual)
                4  select case when occ = 1 then name1 else name2 end name,
                5         case when occ = 1 then phone1 else phone2 end phone
                6  from t
                7     cross join pivoter;
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1330849661
              
              -----------------------------------------------------------------------------
              | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT     |      |     4 |   124 |     8   (0)| 00:00:01 |
              |   1 |  MERGE JOIN CARTESIAN|      |     4 |   124 |     8   (0)| 00:00:01 |
              |   2 |   VIEW               |      |     2 |     6 |     4   (0)| 00:00:01 |
              |   3 |    UNION-ALL         |      |       |       |            |          |
              |   4 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
              |   5 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
              |   6 |   BUFFER SORT        |      |     2 |    56 |     8   (0)| 00:00:01 |
              |   7 |    TABLE ACCESS FULL | T    |     2 |    56 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------
              
              SQL> select name1, phone1 from t
                2  union all
                3  select name2, phone2 from t;
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 663398312
              
              ---------------------------------------------------------------------------
              | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
              ---------------------------------------------------------------------------
              |   0 | SELECT STATEMENT   |      |     4 |    56 |     6  (50)| 00:00:01 |
              |   1 |  UNION-ALL         |      |       |       |            |          |
              |   2 |   TABLE ACCESS FULL| T    |     2 |    28 |     3   (0)| 00:00:01 |
              |   3 |   TABLE ACCESS FULL| T    |     2 |    28 |     3   (0)| 00:00:01 |
              ---------------------------------------------------------------------------
              John

              Edited by: John Spencer on Dec 11, 2012 9:43 AM
              Added example
              • 4. Re: Unpivot or union
                Frank Kulash
                Hi, Igor,

                UNION will require one pass through the table for each branch of the UNION. If you can get the results you need using some other tecnique (such as UNPIVOT, or join) that only requires one pass through the table, then the other technique will probably be more efficient.
                If all branches of a UNION reference the same single table, then the query can be re-written to make only one pass through that table. (I think; I don't have a rigid proof for this. Can anyone think of a counter-example?)

                This is only a broad generalization. As Hoek said, there is no substitute for actally testing the alternatives on your own system, with your own tables.
                • 5. Re: Unpivot or union
                  Hoek
                  I am looking for pros and cons of each aproach since I cant find any documentation that compares them.
                  Because they are totally different approaches, they serve different purposes.
                  Comparing them is not really meaningful, albeit that they may give the same results.
                  UNPIVOT is what you should use given your example, assuming you're looking for pro's and con's from a performance perspective (you didn't really answer that from my previous reply). Also, UNPIVOT is much more meaningful/self-documenting when one of your collegues needs to maintain or review your code.