Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: Two SQL approaches - which is better?

    New to SQL. Below are two simplified versions of a production query currently in use. Both return the same results.

    What are the differences (if any) from a performance/maintenance/standards perspective?

    Code:
    SELECT COLUMNA,
           CASE 
           WHEN COLUMNB = 003 THEN Y
           ELSE N
           END,
           COLUMNC
    FROM TABLEA;
    
    
    SELECT COLUMNA,
           Y,
           COLUMNC
    FROM TABLEA
    WHERE COLUMNB = 003
    UNION ALL
    SELECT COLUMNA,
           N,
           COLUMNC
    FROM TABLEA
    WHERE COLUMNB <> 003;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN for both SQL statements
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Interesting question. The first statement beats the second statement in every way, every time. The reason is that the UNION ALL essentially creates two SQL statements to solve. This means that the db must perform at least twice the number of reads than that of the first statement.

    The following script demonstrates this.
    Code:
    dayneo@RMSD> create table tablea as
      2  select object_name, object_type
      3    from all_objects
      4  /
    
    Table created.
    
    dayneo@RMSD> 
    dayneo@RMSD> set autotrace traceonly explain statistics
    dayneo@RMSD> 
    dayneo@RMSD> select object_name columna,
      2  	    case
      3  		 when object_type = 'TABLE' then 'Y'
      4  		 else 'N'
      5  	    end columnb
      6    from tablea
      7  /
    
    118037 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2728879802
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |   123K|  3366K|   141   (3)| 00:00:02 |
    |   1 |  TABLE ACCESS FULL| TABLEA |   123K|  3366K|   141   (3)| 00:00:02 |
    ----------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    Statistics
    ----------------------------------------------------------
             68  recursive calls
              0  db block gets
           8512  consistent gets
            603  physical reads
              0  redo size
        3878578  bytes sent via SQL*Net to client
          86958  bytes received via SQL*Net from client
           7871  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         118037  rows processed
    
    dayneo@RMSD> 
    dayneo@RMSD> select object_name columna,
      2  	    'Y' columnb
      3    from tablea
      4   where object_type = 'TABLE'
      5  union all
      6  select object_name columna,
      7  	    'Y' columnb
      8    from tablea
      9   where object_type <> 'TABLE'
     10  /
    
    118037 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 415192009
    
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |   123K|  3366K|   283  (52)| 00:00:04 |
    |   1 |  UNION-ALL         |        |       |       |            |          |
    |*  2 |   TABLE ACCESS FULL| TABLEA |  6436 |   175K|   141   (3)| 00:00:02 |
    |*  3 |   TABLE ACCESS FULL| TABLEA |   116K|  3190K|   141   (3)| 00:00:02 |
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OBJECT_TYPE"='TABLE')
       3 - filter("OBJECT_TYPE"<>'TABLE')
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    Statistics
    ----------------------------------------------------------
              9  recursive calls
              0  db block gets
           9176  consistent gets
             95  physical reads
              0  redo size
        3858156  bytes sent via SQL*Net to client
          86958  bytes received via SQL*Net from client
           7871  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         118037  rows processed
    
    dayneo@RMSD> 
    dayneo@RMSD> select object_name columna,
      2  	    decode(object_type, 'TABLE', 'Y', 'N') columnb
      3    from tablea
      4  /
    
    118037 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2728879802
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |   123K|  3366K|   141   (3)| 00:00:02 |
    |   1 |  TABLE ACCESS FULL| TABLEA |   123K|  3366K|   141   (3)| 00:00:02 |
    ----------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    Statistics
    ----------------------------------------------------------
              4  recursive calls
              0  db block gets
           8503  consistent gets
              0  physical reads
              0  redo size
        3878578  bytes sent via SQL*Net to client
          86958  bytes received via SQL*Net from client
           7871  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         118037  rows processed
    
    dayneo@RMSD> 
    dayneo@RMSD> set autotrace off
    dayneo@RMSD> 
    dayneo@RMSD> drop table tablea
      2  /
    
    Table dropped.
    
    dayneo@RMSD>
    In this script, I have created a simple two column table similar to the case that you described. I then ran the two SQL statements that you provided and instructed SQL*PLUS to return the explain plan and statistics for each.

    #1) Performance
    From this you will see that the first SQL statement performs a single FULL TABLE SCAN as apposed to the second statement that performs two FULL TABLE SCANs to accomplish the same. This also means that the first SQL statement will return in 2 seconds (on my system) as apposed to the second statement that will return in 4 seconds.

    Also notable is the difference in CPU between the two statements. The first statement used 3% CPU (on my system) as apposed to the second statement that used a whopping 54% CPU. The extra CPU on the second statement seems to be a result of the use of the UNION ALL.

    #2) Maintenance
    From a maintenance perspective, the case statement wins hands down over the UNION ALL. Imagine having to add an extra column to the statement. For the first statement you can just add the column, for the second statement you would have to add the column in both SELECTs used in the UNION ALL.

    Another option: I introduced a third statement that makes use of the DECODE expression instead of the CASE expression that you used. It just looks neater and Oracle developers are generally more familiar with DECODE than with CASE.

    #3) Standards
    From a standards perspective, the CASE expression is 100% SQL92 compliant, as is the UNION ALL expression. The DECODE expression that I presented is Oracle specific and not SQL92 compliant.

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by dayneo View Post
    Interesting question. The first statement beats the second statement in every way, every time.
    Never say never, never say always.
    Usually "it depends".
    Look at this example, and pay attention on elapsed times:
    Code:
    SQL> create table tablea as select * from all_objects;
    
    Table created.
    
    SQL> insert into tablea select a.* from tablea a        
      2  cross join ( select 1 from dual connect by level <= 10 );
    
    556950 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create index xyz on tablea( object_type, object_name );
    
    Index created.
    
    SQL> set timing on 
    SQL> set autotrace traceonly
    
    Elapsed: 00:00:00.01
    SQL> call dbms_stats.gather_table_stats( user, 'tablea' );
    
    Call completed.
    
    Elapsed: 00:00:05.50
    SQL> select object_name columna, 
      2         case when object_type = 'TABLE' then 'Y'
      3              else 'N'
      4         end columnb
      5  from tablea;
    
    612645 rows selected.
    
    Elapsed: 00:00:02.92
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3178855805
    
    -----------------------------------------------------------------------------
    | Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |	    |	612K|	 21M|  1069   (1)| 00:00:13 |
    |   1 |  INDEX FAST FULL SCAN| XYZ  |	612K|	 21M|  1069   (1)| 00:00:13 |
    -----------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
    	  1  recursive calls
    	  0  db block gets
          44552  consistent gets
    	  3  physical reads
    	  0  redo size
       11972191  bytes sent via SQL*Net to client
         449785  bytes received via SQL*Net from client
          40844  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         612645  rows processed
    
    SQL> select object_name columna,
      2         'Y' columnb
      3  from tablea
      4  where object_type = 'TABLE'
      5  union all
      6  select object_name columna,
      7         'N' columnb
      8  from tablea
      9  where object_type <> 'TABLE';
    
    612645 rows selected.
    
    Elapsed: 00:00:02.88
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3006337102
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	 612K|	  21M|	1236  (87)| 00:00:15 |
    |   1 |  UNION-ALL	      |      |	     |	     |		  |	     |
    |*  2 |   INDEX RANGE SCAN    | XYZ  | 25527 |	 897K|	 166   (0)| 00:00:02 |
    |*  3 |   INDEX FAST FULL SCAN| XYZ  |	 587K|	  20M|	1070   (1)| 00:00:13 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_TYPE"='TABLE')
       3 - filter("OBJECT_TYPE"<>'TABLE')
    
    
    Statistics
    ----------------------------------------------------------
    	  1  recursive calls
    	  0  db block gets
          44559  consistent gets
    	  0  physical reads
    	  0  redo size
       11972176  bytes sent via SQL*Net to client
         449785  bytes received via SQL*Net from client
          40844  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         612645  rows processed
    
    SQL> select object_name columna,
      2         decode( object_type, 'TABLE', 'Y', 'N' ) columnb
      3  from tablea;
    
    612645 rows selected.
    
    Elapsed: 00:00:02.89
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3178855805
    
    -----------------------------------------------------------------------------
    | Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |	    |	612K|	 21M|  1069   (1)| 00:00:13 |
    |   1 |  INDEX FAST FULL SCAN| XYZ  |	612K|	 21M|  1069   (1)| 00:00:13 |
    -----------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
    	  1  recursive calls
    	  0  db block gets
          44552  consistent gets
    	  0  physical reads
    	  0  redo size
       11972191  bytes sent via SQL*Net to client
         449785  bytes received via SQL*Net from client
          40844  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         612645  rows processed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •