3 Replies Latest reply: Nov 10, 2010 1:25 PM by P.Forstmann RSS

    Delete rows where any value is null

    812452
      Hi,

      Is there a short way using sql to delete all rows from a table where any column value in that row is null? Thanks.

      Manish

      Edited by: user12394353 on Nov 10, 2010 7:59 AM
        • 1. Re: Delete rows where any value is null
          sb92075
          Which is the shortest way to delete all rows from a table where any column value in that row is null? Thanks.
          using single DELETE statement
          • 2. Re: Delete rows where any value is null
            DBA 24*7
            May be try this,

            DELETE FROM <Table_Name> WHERE <Col_Name> IS NULL ;
            • 3. Re: Delete rows where any value is null
              P.Forstmann
              If you need to delete a small number of rows with null values in a large table you could try to use a function based index to have a faster execution plan:
              SQL> select * from v$version;
              
              BANNER                                                                                                                              
              ----------------------------------------------------------------                                                                    
              Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product                                                                    
              PL/SQL Release 10.2.0.1.0 - Production                                                                                              
              CORE     10.2.0.1.0     Production                                                                                                          
              TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                                                                             
              NLSRTL Version 10.2.0.1.0 - Production                                                                                              
              
              SQL> alter session set nls_language=english;
              
              Session altered.
              
              SQL> drop table t purge;
              
              Table dropped.
              
              SQL> create table t as select * from all_objects;
              
              Table created.
              
              SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'T');
              
              PL/SQL procedure successfully completed.
              
              SQL> select count(*) from t;
              
                COUNT(*)                                                                                                                          
              ----------                                                                                                                          
                    6986                                                                                                                          
              
              SQL> alter table t modify (object_name null);
              
              Table altered.
              
              SQL> update t set object_name = null where object_id > 15000;
              
              1 row updated.
              
              SQL> select count(*) from t where object_name is null;
              
                COUNT(*)                                                                                                                          
              ----------                                                                                                                          
                       1                                                                                                                          
              
              SQL> create index fi on t(case when object_name is null then 1 else null end);
              
              Index created.
              
              SQL> delete t where (case when object_name is null then 1 else null end) = 1;
              
              1 row deleted.
              
              SQL> select * from table(dbms_xplan.display_cursor);
              
              PLAN_TABLE_OUTPUT                                                                                                                   
              ------------------------------------------------------------------------------------------------------------------------------------
              SQL_ID  22jwsjwuwxz1d, child number 0                                                                                               
              -------------------------------------                                                                                               
              delete t where (case when object_name is null then 1 else null end) = 1                                                             
                                                                                                                                                  
              Plan hash value: 2239455153                                                                                                         
                                                                                                                                                  
              -------------------------------------------------------------------------------------                                               
              | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                               
              -------------------------------------------------------------------------------------                                               
              |   0 | DELETE STATEMENT             |      |       |       |     1 (100)|          |                                               
              |   1 |  DELETE                      | T    |       |       |            |          |                                               
              |   2 |   TABLE ACCESS BY INDEX ROWID| T    |    70 |  1260 |     1   (0)| 00:00:01 |                                               
              |*  3 |    INDEX RANGE SCAN          | FI   |     1 |       |     1   (0)| 00:00:01 |                                               
              -------------------------------------------------------------------------------------                                               
                                                                                                                                                  
              Predicate Information (identified by operation id):                                                                                 
              ---------------------------------------------------                                                                                 
                                                                                                                                                  
                 3 - access("T"."SYS_NC00014$"=1)                                                                                                 
                                                                                                                                                  
              
              20 rows selected.
              
              SQL> 
              Edited by: P. Forstmann on 10 nov. 2010 20:25