Re: difference between access times of SELECT and UPDATE

In article <a9jd07$3mno3$1_at_ID-70985.news.dfncis.de>, "Andrei says...
>
>Hallo all!
>
>can somebody explain me the difference between access times of SELECT and
>UPDATE,
> which use the same index? I get:

well, without a FULL example, it is hard to tell you exactly but here are many of the reasons:

o your select was answered via the index alone. It never even touched the table. The update -- well, of course that must use the index AND the table

o your update must maintain (split, move around, reorganize) your index structure, the select -- it just reads it

o I see recursive sql on the update -- perhaps there is a trigger out there that is doing alot of side effect work for the update. These statistics would be counted as part of your update.

Use SQL_TRACE + TKPROF to get more details. Running an example like this:

drop table t;
drop table t2;

create table t as select * from all_objects; create table t2 as select * from all_objects;

create index t_idx on t(object_id, owner );

set autotrace traceonly

select object_id, owner from t where object_id between 1 and 100; update t set owner = lower(owner) where object_id between 1 and 100; rollback;

create trigger t_trigger after update on t begin
 delete from t2;
end;
/

update t set owner = lower(owner) where object_id between 1 and 100; set autotrace off

you'll see what I mean

>
>by SELECT:
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'I11_BBEWV2' (NON-UNIQUE)
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 2 physical reads
> 0 redo size
>
>by UPDATE
>
>Execution Plan
>----------------------------------------------------------
> 0 UPDATE STATEMENT Optimizer=CHOOSE
> 1 0 UPDATE OF 'BBEWV2'
> 2 1 INDEX (RANGE SCAN) OF 'I11_BBEWV2' (NON-UNIQUE)
>
>Statistics
>----------------------------------------------------------
> 2 recursive calls
> 394 db block gets
> 11203 consistent gets
> 11203 physical reads
> 113672 redo size
>
>Thanks
>Andrei
>
>
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp