测试分区和信息统计对性能的影响

测试环境：WIN2003+ORA10g 10.2.0.1.0

发现自己做测试的水平还比较烂，而且这个测试不在同一天完成的，必定会造成很大的误差。留给自己看看吧，高手见笑了

Partition	Analyze	TIME	Explain
no	no analyze	11s	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1291 SORT GROUP BY 1 58 TABLE ACCESS BY INDEX ROWID ORPHEN.TEST_BASEPASS 340 K 18 M 1291 INDEX RANGE SCAN ORPHEN.BASE_PASS_NEW 340 K 1239
	analyze table	6s	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1304 SORT GROUP BY 1 22 INDEX RANGE SCAN ORPHEN.INDEX_BASE_PASS_NEW 4 K 86 K 1304
	analyze index	13s	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1356 SORT GROUP BY 1 58 VIEW ORPHEN.index$_join$_001 340 K 18 M 1356 HASH JOIN INDEX RANGE SCAN ORPHEN.BASE_PASS_NEW 340 K 18 M 4252 INDEX FAST FULL SCAN ORPHEN.BASE_PASS_UNITID_NEW 340 K 18 M 33
	analyze table and index	6s	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1304 SORT GROUP BY 1 22 INDEX RANGE SCAN ORPHEN.INDEX_BASE_PASS_NEW 4 K 86 K 1304
	可以看到在没有分区情况下，对表进行信息统计后，查询效率有了明显的提高。但是如果只是单纯的分析索引，而没有对表进行分析，效果是非常不理想的。 Oracle针对表的分析更有用，而索引本身已经具备了相当的优化规则在里面，在下面的测试用也可以看到。

把表分区	no analyze	8	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 2796 SORT GROUP BY 1 28 PARTITION RANGE ITERATOR 219 K 5 M 2796 2 3 TABLE ACCESS FULL ORPHEN.BASE_PASS 219 K 5 M 2796 2 3
	analyze table	9	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1588 SORT GROUP BY 1 22 TABLE ACCESS BY GLOBAL INDEX ROWID ORPHEN.BASE_PASS 4 K 106 K 1588 ROWID ROW L INDEX RANGE SCAN ORPHEN.BASE_PASS_NEW 4 K 22
	analyze index	8	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 2796 SORT GROUP BY 1 28 PARTITION RANGE ITERATOR 219 K 5 M 2796 2 3 TABLE ACCESS FULL ORPHEN.BASE_PASS 219 K 5 M 2796 2 3
	analyze table and index	9	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1621 SORT GROUP BY 1 22 TABLE ACCESS BY GLOBAL INDEX ROWID ORPHEN.BASE_PASS 4 K 106 K 1621 ROWID ROW L INDEX RANGE SCAN ORPHEN.BASE_PASS_NEW 4 K 22
	存在表分区的情况下，查询速度相应的提高。但是分析过表后，并没有表现出期望中的效率。

表和索引分区	no analyze	3	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 2700 SORT GROUP BY 1 28 PARTITION RANGE ITERATOR 290 K 7 M 2700 2 3 INDEX RANGE SCAN ORPHEN.INDEX_BASE_PASS_NEW 290 K 7 M 2700 2 3
	analyze table	3	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1607 SORT GROUP BY 1 22 PARTITION RANGE ITERATOR 4 K 106 K 1607 2 3 INDEX RANGE SCAN ORPHEN.INDEX_BASE_PASS_NEW 4 K 106 K 1607 2 3
	analyze table & index	3	Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1607 SORT GROUP BY 1 22 PARTITION RANGE ITERATOR 4 K 106 K 1607 2 3 INDEX RANGE SCAN ORPHEN.INDEX_BASE_PASS_NEW 4 K 106 K 1607 2 3
	将表分区，同时建立了本地分区索引，效果的提升非常明显。Oracle自动使用合适的方法区优化查询效率，很奇怪的是分析表与否并为产生比较大的影响，估计是测试方法有问题。或者是意外oracle10g的优化方式产生的结果

在上面的基础上更改了查询语句，增加了使用制定Partition的参数：
SELECT COUNT (DISTINCT unitid)
FROM base_pass partition (P200706)t
WHERE t.testdate BETWEEN TO_DATE('2007-06-1', 'YYYY-MM-DD')
AND TO_DATE('2007-07-1', 'YYYY-MM-DD')
AND t.productfamily = 'V3M'
发现效果提升明显，只需要2s就可以完成查询

一些用到的脚步：
10g客户端无法到出9i数据
--到出数据
exp yyy/yyyyy@yyyy FILE="e:\exp\base_pass.DMP" LOG="d:\exp\exp_base_pass.log" TABLES=BASE_PASS buffer=4096000 feedback=10000

表空间名不同，重命名表空间
alter tablespace fcs rename to fcs_tables

--导入数据
imp xxxx/xxxx@xxx FILE="d:\exp\base_pass.DMP" LOG="d:\exp\exp_base_pass.log" TABLES=BASE_PASS fromuser=fcs touser=orphen buffer=2048000 commit=y ignore=y feedback=10000

----统计信息
--计算
begin
dbms_stats.gather_table_stats(ownname=> 'ORPHEN', tabname=> 'BASE_PASS', partname=> NULL);
end;

--估算统计信息
begin
dbms_stats.gather_table_stats(ownname=> 'ORPHEN', tabname=> 'BASE_PASS', partname=> NULL , estimate_percent=> 33 );
end;

--删除统计信息
begin
dbms_stats.delete_table_stats(ownname=> 'ORPHEN', tabname=> 'BASE_PASS');
end;

This work is licensed under a CC A-S 4.0 International License.