range,list和Hash 分区的性能

这里我主要比较查询性能差异

首先创建如题目1中的三张表并增加记录至100w多创建的分区内的记录尽量保持平衡也就是每个分区的记录数相近

不加索引下执行下面的查询

SELECT * FROM t_range WHERE object_id=48928

SELECT * FROM t_hash WHERE object_id=48928

SELECT * FROM t_list WHERE object_type='WINDOW GROUP'

SELECT * FROM t_range WHERE object_type='WINDOW GROUP'

SELECT * FROM t_hash WHERE object_type='WINDOW GROUP'

SELECT * FROM t_list WHERE object_name='ST_POINT'

	针对分区键的查询	针对非分区键的字段查询
Range	0.047	0.062
List	0.047	0.062
Hash	0.015	0.078

可见在无索引的情况下针对分区键的查询正常情况下hash 是速度最快的但是在非分区键的查询中range和list分区优势比较明显

Local索引下性能对比

索引分区字段

createindex local_index_rangeont_range(object_id,object_name) local;

createindex local_index_hash ont_hash(object_id,object_name) local;

createindex local_index_list on t_list(object_type,object_name) local;

从创建的速度看range分区是最快hash的分区次之 list分区最慢

SELECT * FROM t_range WHERE object_id=48928AND

object_name='oracle/spatial/util/RTree$Span'

SELECT * FROM t_hash WHERE object_id=48928AND

object_name='oracle/spatial/util/RTree$Span'

SELECT * FROM t_list WHERE object_name='ST_POINT'

AND object_name='oracle/spatial/util/RTree$Span'

	针对索引字段的查询
Range	0.031
List	0.015
Hash	0.016

可见range和list分区在加索引后速度明显提高而hash分区的性能提升不大，也可以看出list和hash分区较range的速度还是比较快的

全局不分区索引：

createindexorders_global_t_range on t_range(object_name) GLOBAL

createindex orders_global_t_list ont_list(object_name) GLOBAL

createindex orders_global_t_hash on t_hash(object_name) GLOBAL

从创建的过程看range>list>hash

SELECT * FROM t_range WHERE object_name='oracle/spatial/util/RTree$Span'

SELECT * FROM t_hash WHERE object_name='oracle/spatial/util/RTree$Span'

SELECT * FROM t_list WHERE

object_name='oracle/spatial/util/RTree$Span'

	针对索引字段的查询
Range	0.078
List	0.063
Hash	0.031

可见hash的查询优势还是比较明显，但是这个全局不分区的索引的性能明显不如local索引。

全局分区索引：

createindex orders_global_t_range

on t_range(object_id)

globalpartitionbyrange (object_id)

(partition GLOBAL1 valueslessthan (30000),

partition GLOBAL2 valueslessthan (MAXVALUE)

)

;

createindex orders_global_t_hash

on t_hash(object_id)

globalpartitionbyHASH (object_id)

;

createindex orders_global_t_list

on t_list(object_id)

globalpartitionbyrange (object_id)

(partition GLOBAL1 valueslessthan (30000),

partition GLOBAL2 valueslessthan (MAXVALUE)

)

;

SELECT * FROM t_range WHERE object_id=48928

SELECT * FROM t_hash WHERE object_id=48928

SELECT * FROM t_list WHERE object_id=48928

	针对索引字段的查询
Range	0.031
List	0.031
Hash	0.032

可见全局分区索引对range和list的性能提示还是很明显的，三者在这个方面的性能差别并不大