1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to update table with different values in single statement?

Discussion in 'SQL PL/SQL' started by jagadekara, Oct 18, 2013.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi all,

    I created table and inserted the data like below.

    CREATE TABLE XX_GENDER(GENDER CHAR);

    INSERT INTO XX_GENDER VALUES('M');

    INSERT INTO XX_GENDER VALUES('F');

    INSERT INTO XX_GENDER VALUES('F');

    COMMIT;

    SELECT * FROM XX_GENDER;

    GENDER
    --------
    M
    F
    F

    So my requirement is update this table where ever M is there we need to update it as f and where ever F is there we need to update it as m . but it should be in single statement.

    Final output should be like this.

    SELECT * FROM XX_GENDER;

    GENDER
    --------
    f
    m
    m
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    You can update your table by using a CASE statement in your UPDATE clause:
    Code (SQL):
    SQL>
    CREATE TABLE XX_GENDER(GENDER CHAR);

    INSERT INTO XX_GENDER VALUES('M');

    INSERT INTO XX_GENDER VALUES('F');

    INSERT INTO XX_GENDER VALUES('F');


    TABLE created.

    SQL> SQL>
    1 ROW created.

    SQL> SQL>
    1 ROW created.

    SQL> SQL>
    1 ROW created.

    SQL> SQL> COMMIT;

    Commit complete.

    SQL>
    SQL> SELECT * FROM xx_gender;

    G
    -
    M
    F
    F

    SQL>
    SQL> UPDATE xx_gender t1 SET gender = CASE WHEN t1.gender = 'F' THEN 'M' ELSE 'F' END;

    3 ROWS updated.

    SQL> SELECT * FROM xx_gender;

    G
    -
    F
    M
    M

    SQL>
     
    jagadekara likes this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    jagadekara likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank you Dariyoosh,

    But some of my friends suggest two more ways check below...

    1) update XX_GENDER set gender=translate(gender,'MF','fm');

    2) update XX_GENDER set gender=decode(gender,'M','f','m');

    and yours

    3) UPDATE xx_gender t1 SET gender = CASE WHEN t1.gender = 'F' THEN 'm' ELSE 'f' END;

    So now i have another question in mind, out of these 3 which one is more performance?

    Please suggest any one.....
     
    Ecor likes this.
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Why don't you compare their execution plans?

    Code (SQL):

    DROP TABLE xx_gender;
    CREATE TABLE XX_GENDER(GENDER CHAR);
    INSERT INTO XX_GENDER VALUES('M');
    INSERT INTO XX_GENDER VALUES('F');
    INSERT INTO XX_GENDER VALUES('F');
    COMMIT;
    --
    UPDATE  /*+ gather_plan_statistics */
    xx_gender t1
    SET t1.gender = CASE WHEN t1.gender = 'M' THEN 'F' ELSE 'M' END;
    --
    SELECT  *
    FROM    TABLE(sys.dbms_xplan.display_cursor
                (NULL, NULL, 'ALLSTATS +COST LAST'));
    --
    --
    DROP TABLE xx_gender;
    CREATE TABLE XX_GENDER(GENDER CHAR);
    INSERT INTO XX_GENDER VALUES('M');
    INSERT INTO XX_GENDER VALUES('F');
    INSERT INTO XX_GENDER VALUES('F');
    COMMIT;
    --
    UPDATE  /*+ gather_plan_statistics */
    xx_gender t1
    SET t1.gender = TRANSLATE(t1.gender, 'MF', 'FM');
    --
    SELECT  *
    FROM    TABLE(sys.dbms_xplan.display_cursor
                (NULL, NULL, 'ALLSTATS +COST LAST'));
    --
    --
    DROP TABLE xx_gender;
    CREATE TABLE XX_GENDER(GENDER CHAR);
    INSERT INTO XX_GENDER VALUES('M');
    INSERT INTO XX_GENDER VALUES('F');
    INSERT INTO XX_GENDER VALUES('F');
    COMMIT;
    --
    UPDATE  /*+ gather_plan_statistics */
    xx_gender t1
    SET t1.gender = DECODE(t1.gender, 'M', 'F', 'M');
    --
    SELECT  *
    FROM    TABLE(sys.dbms_xplan.display_cursor
                (NULL, NULL, 'ALLSTATS +COST LAST'));
    --
     
    Which respectively gives you the following three execution plans
    Code (Text):

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  58b8g66guk172, child number 0
    -------------------------------------
    UPDATE  /*+ gather_plan_statistics */ xx_gender t1 SET t1.gender = CASE
    WHEN t1.gender = 'M' THEN 'F' ELSE 'M' END

    Plan hash value: 2606769813

    -------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT   |           |      1 |        |     3 (100)|      0 |00:00:00.01 |      10 |
    |   1 |  UPDATE            | XX_GENDER |      1 |        |            |      0 |00:00:00.01 |      10 |
    |   2 |   TABLE ACCESS FULL| XX_GENDER |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------------

    Note
    -----
       - dynamic sampling used for this statement (level=2)


    19 rows selected.


    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  gdkpdhyxgb6sw, child number 0
    -------------------------------------
    UPDATE  /*+ gather_plan_statistics */ xx_gender t1 SET t1.gender =
    TRANSLATE(t1.gender, 'MF', 'FM')

    Plan hash value: 2606769813

    -------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT   |           |      1 |        |     3 (100)|      0 |00:00:00.01 |      10 |
    |   1 |  UPDATE            | XX_GENDER |      1 |        |            |      0 |00:00:00.01 |      10 |
    |   2 |   TABLE ACCESS FULL| XX_GENDER |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------------

    Note
    -----
       - dynamic sampling used for this statement (level=2)


    19 rows selected.


    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  bz3wfmz820qc7, child number 0
    -------------------------------------
    UPDATE  /*+ gather_plan_statistics */ xx_gender t1 SET t1.gender =
    DECODE(t1.gender, 'M', 'F', 'M')

    Plan hash value: 2606769813

    -------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT   |           |      1 |        |     3 (100)|      0 |00:00:00.01 |      10 |
    |   1 |  UPDATE            | XX_GENDER |      1 |        |            |      0 |00:00:00.01 |      10 |
    |   2 |   TABLE ACCESS FULL| XX_GENDER |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------------

    Note
    -----
       - dynamic sampling used for this statement (level=2)


    19 rows selected.

    SQL>
     
    As it seems to me all of them have the same cost for this table.
     
    jagadekara likes this.
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    For three rows in a table any of the provided update statements will exhibit stellar performance. It's when there is far more data in the table that performance becomes an issue. Let's modify the example and add much more data to the table, then add an index:

    Code (SQL):

    SQL> --
    SQL> -- Your original example
    SQL> --
    SQL> CREATE TABLE XX_GENDER(GENDER CHAR);
    TABLE created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('M');
    1 ROW created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('F');
    1 ROW created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('F');
    1 ROW created.
    SQL>
    SQL> COMMIT;
    Commit complete.
    SQL>
    SQL> SELECT * FROM XX_GENDER;
    G                                                                                                                                  
    -                                                                                                                                  
    M                                                                                                                                  
    F                                                                                                                                  
    F                                                                                                                                  
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- The three update statements and execution plans
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE XX_GENDER SET gender=translate(gender,'MF','fm');
    3 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
              3  db block gets                                                                                                          
              6  consistent gets                                                                                                        
              0  physical reads                                                                                                        
              0  redo SIZE                                                                                                              
            834  bytes sent via SQL*Net TO client                                                                                      
            807  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
              3  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE XX_GENDER SET gender=decode(gender,'M','f','m');
    3 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
              3  db block gets                                                                                                          
              6  consistent gets                                                                                                        
              0  physical reads                                                                                                        
              0  redo SIZE                                                                                                              
            836  bytes sent via SQL*Net TO client                                                                                      
            806  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
              3  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE xx_gender t1 SET gender = CASE WHEN t1.gender = 'F' THEN 'm' ELSE 'f' END;
    3 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |     3 |     6 |     3   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
              3  db block gets                                                                                                          
              6  consistent gets                                                                                                        
              0  physical reads                                                                                                        
              0  redo SIZE                                                                                                              
            836  bytes sent via SQL*Net TO client                                                                                      
            832  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
              3  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL> --
    SQL> -- For three rows performance is exceptional with any of the statements
    SQL> --
    SQL> -- Let's add data to the table and see which one 'wins'
    SQL> --
    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100000 loop
      3         IF MOD(i, 77)=0 THEN
      4          INSERT INTO xx_gender(gender)
      5          VALUES ('M');
      6         elsif MOD(i,343)=0 THEN
      7          INSERT INTO xx_gender(gender)
      8          VALUES('m');
      9         ELSE
     10          INSERT INTO xx_gender(gender)
     11          VALUES('F');
     12         END IF;
     13        END loop;
     14  
     15        commit;
     16  END;
     17  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- The three update statements and execution plans
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE XX_GENDER SET gender=translate(gender,'MF','fm');
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             99  recursive calls                                                                                                        
         105259  db block gets                                                                                                          
           5735  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       31834044  redo SIZE                                                                                                              
            838  bytes sent via SQL*Net TO client                                                                                      
            807  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE XX_GENDER SET gender=decode(gender,'M','f','m');
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             99  recursive calls                                                                                                        
         103558  db block gets                                                                                                          
            434  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       29817948  redo SIZE                                                                                                              
            840  bytes sent via SQL*Net TO client                                                                                      
            806  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE xx_gender t1 SET gender = CASE WHEN t1.gender = 'F' THEN 'm' ELSE 'f' END;
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             99  recursive calls                                                                                                        
         107903  db block gets                                                                                                          
            489  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       34902408  redo SIZE                                                                                                              
            841  bytes sent via SQL*Net TO client                                                                                      
            832  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL>
    SQL> --
    SQL> -- Let's add an index
    SQL> --
    SQL>
    SQL> CREATE INDEX xx_gender_idx ON xx_gender(gender);
    INDEX created.
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- The three update statements and execution plans
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE XX_GENDER SET gender=translate(gender,'MF','fm');
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
            131  recursive calls                                                                                                        
         511106  db block gets                                                                                                          
           1245  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       71076812  redo SIZE                                                                                                              
            843  bytes sent via SQL*Net TO client                                                                                      
            807  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE XX_GENDER SET gender=decode(gender,'M','f','m');
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             37  recursive calls                                                                                                        
         505359  db block gets                                                                                                          
            207  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       65288244  redo SIZE                                                                                                              
            844  bytes sent via SQL*Net TO client                                                                                      
            806  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> UPDATE xx_gender t1 SET gender = CASE WHEN t1.gender = 'F' THEN 'm' ELSE 'f' END;
    100003 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |   2 |   TABLE ACCESS FULL| XX_GENDER |   100K|   195K|    68   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             82  recursive calls                                                                                                        
         506483  db block gets                                                                                                          
            226  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       65399776  redo SIZE                                                                                                              
            844  bytes sent via SQL*Net TO client                                                                                      
            832  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
         100003  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL>
    SQL> SET echo off linesize 80
     
    Because there is no WHERE clause in any of those updates the index provides no benefit to the updates. Also, notice that the more data you have the higher the cost (and execution time) will be. Let's change the single-statement updates to two specific updates with WHERE clauses and see how performance fares:
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    SQL> --
    SQL> -- Your original example
    SQL> --
    SQL> CREATE TABLE XX_GENDER(GENDER CHAR);
    TABLE created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('M');
    1 ROW created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('F');
    1 ROW created.
    SQL>
    SQL> INSERT INTO XX_GENDER VALUES('F');
    1 ROW created.
    SQL>
    SQL> COMMIT;
    Commit complete.
    SQL>
    SQL> SELECT * FROM XX_GENDER;
    G                                                                                                                                  
    -                                                                                                                                  
    M                                                                                                                                  
    F                                                                                                                                  
    F                                                                                                                                  
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Process this with two separate update statements
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE xx_gender SET gender = 'f' WHERE gender = 'M';
    1 ROW updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |     2 |     4 |     3   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |*  2 |   TABLE ACCESS FULL| XX_GENDER |     2 |     4 |     3   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - FILTER("GENDER"='M')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
              3  db block gets                                                                                                          
              6  consistent gets                                                                                                        
              0  physical reads                                                                                                        
              0  redo SIZE                                                                                                              
            834  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
              1  ROWS processed                                                                                                        
    SQL>
    SQL> UPDATE xx_gender SET gender = 'm' WHERE gender = 'F';
    2 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |     2 |     4 |     3   (0)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |*  2 |   TABLE ACCESS FULL| XX_GENDER |     2 |     4 |     3   (0)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - FILTER("GENDER"='F')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
              1  db block gets                                                                                                          
              6  consistent gets                                                                                                        
              0  physical reads                                                                                                        
            376  redo SIZE                                                                                                              
            836  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
              2  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL> --
    SQL> -- For three rows performance is exceptional with any of the statements
    SQL> --
    SQL> -- Let's add data to the table and see which one 'wins'
    SQL> --
    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100000 loop
      3         IF MOD(i, 77)=0 THEN
      4          INSERT INTO xx_gender(gender)
      5          VALUES ('M');
      6         elsif MOD(i,343)=0 THEN
      7          INSERT INTO xx_gender(gender)
      8          VALUES('m');
      9         ELSE
     10          INSERT INTO xx_gender(gender)
     11          VALUES('F');
     12         END IF;
     13        END loop;
     14  
     15        commit;
     16  END;
     17  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Process this with two separate update statements
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE xx_gender SET gender = 'f' WHERE gender = 'M';
    1299 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           |   960 |  1920 |    69   (2)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |*  2 |   TABLE ACCESS FULL| XX_GENDER |   960 |  1920 |    69   (2)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - FILTER("GENDER"='M')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              0  recursive calls                                                                                                        
            169  db block gets                                                                                                          
            190  consistent gets                                                                                                        
              0  physical reads                                                                                                        
         144188  redo SIZE                                                                                                              
            839  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
           1299  ROWS processed                                                                                                        
    SQL>
    SQL> UPDATE xx_gender SET gender = 'm' WHERE gender = 'F';
    98439 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           | 98826 |   193K|    69   (2)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |*  2 |   TABLE ACCESS FULL| XX_GENDER | 98826 |   193K|    69   (2)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - FILTER("GENDER"='F')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             99  recursive calls                                                                                                        
         103748  db block gets                                                                                                          
            456  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       31453704  redo SIZE                                                                                                              
            839  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
          98439  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL>
    SQL> --
    SQL> -- Let's add an index
    SQL> --
    SQL>
    SQL> CREATE INDEX xx_gender_idx ON xx_gender(gender);
    INDEX created.
    SQL>
    SQL> --
    SQL> -- Let's generate statistics on that table
    SQL> --
    SQL> EXEC dbms_stats.gather_table_stats('GRIBNAUT','XX_GENDER', cascade=>TRUE);
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> --
    SQL> -- Process this with two separate update statements
    SQL> --
    SQL> SET autotrace ON
    SQL>
    SQL> UPDATE xx_gender SET gender = 'f' WHERE gender = 'M';
    1299 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 1218087034                                                                                                        
                                                                                                                                       
    -----------------------------------------------------------------------------------                                                
    | Id  | Operation         | Name          | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                
    -----------------------------------------------------------------------------------                                                
    |   0 | UPDATE STATEMENT  |               |  1200 |  2400 |     3   (0)| 00:00:01 |                                                
    |   1 |  UPDATE           | XX_GENDER     |       |       |            |          |                                                
    |*  2 |   INDEX RANGE SCAN| XX_GENDER_IDX |  1200 |  2400 |     3   (0)| 00:00:01 |                                                
    -----------------------------------------------------------------------------------                                                
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - access("GENDER"='M')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
              1  recursive calls                                                                                                        
           1490  db block gets                                                                                                          
             23  consistent gets                                                                                                        
              0  physical reads                                                                                                        
         438304  redo SIZE                                                                                                              
            840  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              2  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
           1299  ROWS processed                                                                                                        
    SQL>
    SQL> UPDATE xx_gender SET gender = 'm' WHERE gender = 'F';
    98439 ROWS updated.

    Execution Plan
    ----------------------------------------------------------                                                                          
    Plan hash VALUE: 2606769813                                                                                                        
                                                                                                                                       
    --------------------------------------------------------------------------------                                                    
    | Id  | Operation          | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |                                                    
    --------------------------------------------------------------------------------                                                    
    |   0 | UPDATE STATEMENT   |           | 98535 |   192K|    69   (2)| 00:00:01 |                                                    
    |   1 |  UPDATE            | XX_GENDER |       |       |            |          |                                                    
    |*  2 |   TABLE ACCESS FULL| XX_GENDER | 98535 |   192K|    69   (2)| 00:00:01 |                                                    
    --------------------------------------------------------------------------------                                                    
                                                                                                                                       
    Predicate Information (IDENTIFIED BY operation id):                                                                                
    ---------------------------------------------------                                                                                
                                                                                                                                       
       2 - FILTER("GENDER"='F')                                                                                                        
                                                                                                                                       
    Note                                                                                                                                
    -----                                                                                                                              
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold                                              

    Statistics
    ----------------------------------------------------------                                                                          
             59  recursive calls                                                                                                        
         504096  db block gets                                                                                                          
           1267  consistent gets                                                                                                        
              0  physical reads                                                                                                        
       70022316  redo SIZE                                                                                                              
            840  bytes sent via SQL*Net TO client                                                                                      
            804  bytes received via SQL*Net FROM client                                                                                
              3  SQL*Net roundtrips TO/FROM client                                                                                      
              1  sorts (memory)                                                                                                        
              0  sorts (disk)                                                                                                          
          98439  ROWS processed                                                                                                        
    SQL>
    SQL> ROLLBACK;
    ROLLBACK complete.
    SQL>
    SQL> SET autotrace off
    SQL>
    SQL> SET echo off linesize 80
     
    The index was useful on the 'M' updates since there is less than 2 percent of the data with a gender value of 'M'; if we skew the data the other way then the 'F' updates will use the index. And if we split the data down the middle and make half of the rows contain 'M' and the ogher half contain 'F' then, even with the index, the performance reverts back to that of not having an index at all.
     
    Ecor likes this.
  8. Ecor

    Ecor Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Good discussion....