3 Replies Latest reply: Dec 2, 2010 8:49 AM by 6363 RSS

    UNION in oracle SQL

    696393
      Hi ,

      I would like to know how union operator in sql functions. how is it works since multiple implicit cursors are involved in it , which cursor it picks up first and how it process the out put .

      select a,b,c from tab_1
      union
      select a,b,c from tab_11
      union
      select a,b,c from tab_111
      is there any alternate way to avoid unions ( need to improve performance of a sql ).



      thans in advance



      raj
        • 1. Re: UNION in oracle SQL
          6363
          infant_raj wrote:

          I would like to know how union operator in sql functions. how is it works since multiple implicit cursors are involved in it ,
          A single query opens a single cursor, there are not multiple implicit cursors involved.
          is there any alternate way to avoid unions ( need to improve performance of a sql ).
          This is not the correct way to tune, typically features are implemented to do something specific there are not fast and slow features especially not features that do exactly the same thing.

          These posts explain the correct way to tune.

          HOW TO: Post a SQL statement tuning request
          HOW TO: Post a SQL statement tuning request - template posting

          When your query takes too long
          When your query takes too long ...
          • 2. Re: UNION in oracle SQL
            Sven W.
            1 SQL (SELECT/I/U/D) statement => 1 cursor

            There are only very few exception to this rule, your select is not one of them.

            You could improve the performance by using UNION ALL instead of UNION. Union all does a sort operation, because it needs to eliminate all the dupliacte rows. This can be a performance eating task if the output is large. If possible always use UNION ALL. But the output might be different.
            • 3. Re: UNION in oracle SQL
              6363
              Sven W. wrote:

              You could improve the performance by using UNION ALL instead of UNION. Union all does a sort operation, because it needs to eliminate all the dupliacte rows. This can be a performance eating task if the output is large. If possible always use UNION ALL. But the output might be different.
              You cannot compare the performance of UNION ALL with UNION since, as you say, they return different results.

              UNION ALL can also be much slower.

              Re: DBA interview questions