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
1 comment:
Here are some interesting details about this issue
http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/
Post a Comment