ORACLE 用ROWID 删除数据竟然的走索引-不得其解

下午一个同事执行一条删除语句1个小时才执行了11万条

该语句是DELETE FROM TABLE_NAME WHERE ROWID=’XXXXAAAA’

执行计划确实走索引一个以CREATE_TIME为主的索引.

后来很多同事不得其解本以为写了ROWID,那该直接去表里把数据给删了,为啥还要去访问索引呢? 我们的DBA解释到因为该表没做分析索引也没做分析.

 

不过回家实验了一把瞧瞧看

SQL> create table t_test (id number,name varchar2(35),create_time date);

Table created

SQL> create index idx_test_createtime on t_test(create_time);

Index created

SQL> insert into t_test  select object_id,object_name,sysdate from dba_objects;

50333 rows inserted

SQL> commit;

Commit complete

不做分析:

      

一些默认的参数   

SQL> show parameter optimizer_index_cost_adj

optimizer_index_cost_adj             integer     100

optimizer_index_caching              integer     0

db_block_buffers                     integer     0

db_block_checking                   string      FALSE

db_block_checksum                  string      TRUE

db_block_size                       integer     8192

db_file_multiblock_read_count         integer     32

 

执行删除语句看执行计划

delete t_test where id >500

delete t_test where id=:1

delete t_test where rowed=’ AAAM3OAAEAAAAOEAAA’

结果执行计划没走索引

DELETE STATEMENT, GOAL = ALL_ROWS   Depth=0 Operation=DELETE STATEMENT      DELETE  Depth=1    Operation=DELETE    Object name=T_TEST                        TABLE ACCESS BY USER ROWID Depth=2    Operation=TABLE ACCESS     

分析下

SQL> analyze table t_test compute statistics;

Table analyzed

发现还是不走索引

 

试下分区表

create table t_test_2 as select * from t_test

SQL> update t_test_2 set create_time=sysdate-1 where id<=10000;

9581 rows updated

SQL> update t_test_2 set create_time=sysdate-2 where id<=20000 and id>10000;

9974 rows updated

SQL> update t_test_2 set create_time=sysdate-3 where id<=30000 and id>20000;

10000 rows updated

SQL> update t_test_2 set create_time=sysdate-4 where id<=40000 and id>30000;

9997 rows updated

SQL> update t_test_2 set create_time=sysdate-5 where id<=50000 and id>40000;

9089 rows updated

SQL> commit;

Commit complete

SQL> select to_char(create_time,'yyyymmdd') as statedate,count(*) t from t_test_2 group by to_char(create_time,'yyyymmdd');

STATEDATE          T

--------- ----------

20120528        9974

20120527       10000

20120530        1692

20120529        9581

20120526        9997

20120525        9089

6 rows selected

 

创建时间范围分区表:

-- Create table

create table t_test_3

(  id          number,

  name        varchar2(35),

  create_time date

)partition by range (create_time)

(  partition test_20120525 values less than (to_date('2012-05-26','yyyy-mm-dd')),

  partition test_20120526 values less than (to_date('2012-05-27','yyyy-mm-dd')),

  partition test_20120527 values less than (to_date('2012-05-28','yyyy-mm-dd')),

  partition test_20120528 values less than (to_date('2012-05-29','yyyy-mm-dd')),

  partition test_20120529 values less than (to_date('2012-05-30','yyyy-mm-dd')),

  partition test_20120530 values less than (to_date('2012-05-31','yyyy-mm-dd'))

);

创建本地索引并插入数据

SQL> create index idx_local_createtime on t_test_3 (create_time) local;

Index created

SQL> insert into t_test_3  select id,name,create_time from t_test_2;

50333 rows inserted

SQL> commit;

Commit complete

 

delete t_test where id=:1

delete t_test where rowed=’ AAAM3OAAEAAAAOEAAA’

执行计划依旧是TABLE ACCESS BY USER ROWID

 

分别设置参数

SQL> alter system set optimizer_index_caching=100;

System altered

SQL> alter system set optimizer_index_cost_adj=1;

System altered

结果执行计划依旧是TABLE ACCESS BY USER ROWID

 

执行分析

SQL> EXEC dbms_stats.gather_table_stats

  (ownname=>'SHARK', tabname=>'T_TEST_3',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed

执行计划依旧是TABLE ACCESS BY USER ROWID

 

创建子分区

SQL> update t_test_2 set id=mod(id,10);

50333 rows updated

SQL>CREATE TABLE SHARK.t_test_4
(
  id           NUMBER,
  name         VARCHAR2(35),
  create_time  DATE
)
PARTITION BY RANGE (create_time)
SUBPARTITION BY LIST (id)
SUBPARTITION TEMPLATE
  (SUBPARTITION "A" VALUES (0),
   SUBPARTITION "B" VALUES (1),
   SUBPARTITION "C" VALUES (2),
   SUBPARTITION "D" VALUES (3),
   SUBPARTITION "E" VALUES (4),
   SUBPARTITION "F" VALUES (5),
   SUBPARTITION "G" VALUES (6),
   SUBPARTITION "H" VALUES (7),
   SUBPARTITION "J" VALUES (8),
   SUBPARTITION "K" VALUES (9)
  )
( 
  PARTITION TEST_20120525 VALUES LESS THAN (to_date('2012-05-26', 'yyyy-mm-dd')), 
  PARTITION TEST_20120526 VALUES LESS THAN  (to_date('2012-05-27', 'yyyy-mm-dd')) , 
  PARTITION TEST_20120527 VALUES LESS THAN (to_date('2012-05-28', 'yyyy-mm-dd'))    , 
  PARTITION TEST_20120528 VALUES LESS THAN  (to_date('2012-05-29', 'yyyy-mm-dd'))    , 
  PARTITION TEST_20120529 VALUES LESS THAN (to_date('2012-05-30', 'yyyy-mm-dd'))    , 
  PARTITION TEST_20120530 VALUES LESS THAN  (to_date('2012-05-31', 'yyyy-mm-dd'))   
);

 

SQL> insert into t_test_4 select * from t_test_2;

50333 rows inserted

SQL> commit;

Commit complete

SQL> create index idx_local_createtime_test4 on t_test_4 (create_time) local;

Index created

 

SQL> EXEC dbms_stats.gather_table_stats 

(ownname=>'SHARK', tabname=>'T_TEST_4',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed

SQL> create index idx_local_id_test4 on t_test_4 (id) local;

Index created

 

很不幸运的是执行计划依旧是TABLE ACCESS BY USER ROWID

只有下面的语句才用到了索引

delete t_test_4 where id =4

delete

  partition range all

   partition list single

     index range scan

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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

 公司的数据库服务器应该是10.2.0.4的吧? 涉及到表都是分区,或者子分区.本地索引,未分析!