空间设计英文/宁波seo外包推广公司
下午一个同事执行一条删除语句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的吧? 涉及到表都是分区,或者子分区.本地索引,未分析!