2010/05/31

DML through indexes

Recently I have been rereading Effective Oracle by Design. And I realized something new for myself.
Inter alia I reread "Modification DML from Start to Finish" section:

In general, these statement have a query component...
In general, a modification statement will find data it needs to update using the consistent-read mechanism described in the previous section. Then it will get that information again in current (as of right now) mode in order to modify it.


What does "find data" mean?...

And short time after that I ran into an interesting issue with deleting all rows from a table. I saw hint FULL within delete statement in code that was written by my collegue. I tried to remove that hint and CBO started to use INDEX ONLY access path. It was very unexpectedly for me, because I was used to think about delete statement in the same way as about select statement. I mean when I want select all rows from some table FULL TABLE ACCESS path is more efficient comparing with access path through any index. But as it turned out things must be rethink as for delete statement and update statement too.
So Thomas Kyte wrote, statement will find data it need during execution query component of DML. What does it exactly mean? What kind of data Oracle must find?
I think Oracle finds rowids of rows it will update or delete. And which way is most efficient to do this? If I delete all rows from a table any index with not null fields have got all necessary rowids! And Oracle will use it if I will not interfere with hints! And it is not all about using indexes in DML. I think the same positive effect can be observed when we need to update or delete a part (may be a huge part) of rows.
SQL> create table t1 tablespace users
2  as select * from all_objects;

Table created.

SQL> describe t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NAMESPACE                                 NOT NULL NUMBER
EDITION_NAME                                       VARCHAR2(30)

SQL> create index t1_idx1 on t1(object_id) tablespace users;

Index created.

SQL> exec dbms_stats.gather_table_stats(null,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace on explain statistics
SQL> set timing on
SQL> delete from t1;

71312 rows deleted.

Elapsed: 00:00:04.12

Execution Plan
----------------------------------------------------------                      
Plan hash value: 3237753727                                                     

----------------------------------------------------------------------------    
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------    
|   0 | DELETE STATEMENT |         | 71312 |   348K|   160   (1)| 00:00:02 |    
|   1 |  DELETE          | T1      |       |       |            |          |    
|   2 |   INDEX FULL SCAN| T1_IDX1 | 71312 |   348K|   160   (1)| 00:00:02 |    
----------------------------------------------------------------------------    


Statistics
----------------------------------------------------------                      
     144  recursive calls                                                    
   80831  db block gets                                                      
     223  consistent gets                                                    
      35  physical reads                                                     
27601692  redo size                                                          
     673  bytes sent via SQL*Net to client                                   
     546  bytes received via SQL*Net from client                             
       3  SQL*Net roundtrips to/from client                                  
       2  sorts (memory)                                                     
       0  sorts (disk)                                                       
   71312  rows processed                                                     

SQL> rollback;

Rollback complete.

Elapsed: 00:00:06.21
SQL> delete /*+FULL(t1)*/ from t1;

71312 rows deleted.

Elapsed: 00:00:15.67

Execution Plan
----------------------------------------------------------                      
Plan hash value: 775918519                                                      

---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | DELETE STATEMENT   |      | 71312 |   348K|   285   (1)| 00:00:04 |     
|   1 |  DELETE            | T1   |       |       |            |          |     
|   2 |   TABLE ACCESS FULL| T1   | 71312 |   348K|   285   (1)| 00:00:04 |     
---------------------------------------------------------------------------     


Statistics
----------------------------------------------------------                      
     198  recursive calls                                                    
  224029  db block gets                                                      
    1108  consistent gets                                                    
       2  physical reads                                                     
40684972  redo size                                                          
     673  bytes sent via SQL*Net to client                                   
     560  bytes received via SQL*Net from client                             
       3  SQL*Net roundtrips to/from client                                  
       1  sorts (memory)                                                     
       0  sorts (disk)                                                       
   71312  rows processed                                                     

SQL> rollback;

Rollback complete.

Elapsed: 00:00:12.89
As you can see LIO (db block gets + consistent gets) is very different between first and second case. And the statement with index access is clear winner in this competition.

Note:
SQL> select table_name,blocks,num_rows from user_tables where table_name='T1';

TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
T1                                   1041      71312

SQL> select index_name,leaf_blocks,num_rows from user_indexes where table_name='T1';

INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS
------------------------------ ----------- ----------
T1_IDX1                                158      71312