执行计划SQL写法差异改变之三_rowid的影响
环境准备:
create table t as select * from dba_objects;
语句1:
update t set object_id=rownum;
create index idx_object_id on t(object_id);
select rowid from t where object_id=8;
ROWID
AAAlntAFNAAAADbAAH
语句1:
SQL> update t set object_name='abc' where object_id=8;
语句2:
SQL> update t set object_name='abc' where object_id=8 and t.rowid='AAAlntAFNAAAADbAAH';
语句1和语句2在某些业务场景下,显然是等价的。但是语句2走得是TABLE ACCESS BY USER ROWID,而语句1走得是INDEX RANGE SCAN。请注意这个TABLE ACCESS BY USER ROWID扫描方式,其直接根据rowid来访问,是最快的访问方式!
执行计划SQL写法差异改变之四_分区条件有无
环境准备:
create table list_part_tab(id number,deal_date date,area_code number,nbr number,building varchar2(4000))
partition by list(area_code)
(
partition p_591 values (591),
partition p_592 values (592),
partition p_593 values (593),
partition p_594 values (594),
partition p_595 values (595),
partition p_596 values (596),
partition p_597 values (597),
partition p_598 values (598),
partition p_599 values (599),
partition p_other values (default)
);
insert into list_part_tab(id,deal_date,area_code,nbr,building)
select rownum,to_date(to_char(sysdate-365,'J')+trunc(dbms_random.value(0,365)),'J'),
ceil(dbms_random.value(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('',400,'')
from dual
connect by rownum<=100000;
update list_part_tab set building='福州市政府' where area_code=591 and rownum=1;
语句1:
语句2:
说明:两个语句的执行计划似乎没有差别,但是经过仔细查看我们发现,语句1的执行计划中pstart为1而pstop为10,说明从第1个分区遍历到第10个分区。而第2个语句的执行计划中pstart和pstop对应的是key,说明它们落在了指定的分区中。执行计划的不同性能也有明显差距。从cost消耗就能看出来。因此查询分区表能使用分区条件的,一定带上分区条件,能大大提升查询性能。