首先创建一个大表：
create table big_tableasselect rownum id, a.* from all_objects a where 1=0/alter table big_table nologging;
declare l_cnt number; l_rows number := &1;begin insert /*+ append */ into big_table select rownum, a.* from all_objects a;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows) loop insert /*+ APPEND */ into big_table select rownum+l_cnt, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY from big_table where rownum <= l_rows-l_cnt; l_cnt := l_cnt + sql%rowcount; commit; end loop;end;/
alter table big_table add constraintbig_table_pk primary key(id)/
begin dbms_stats.gather_table_stats ( ownname => user, tabname => 'BIG_TABLE', method_opt => 'for all indexed columns', cascade => TRUE );end;/
在本次测试中我创建了200万行记录。
然后创建一个Hash分区表：
create table big_table_hashed nologgingpartition by hash(object_id) partitions 10asselect * from big_table;
为两张表对owner字段创建索引：
create index big_idx1 on big_table(owner);create index big_hash_idx1 on big_table_hashed(owner) LOCAL;
analyze table big_table compute statistics for table;analyze table big_table compute statistics for all indexes; analyze table big_table compute statistics for all indexed columns;analyze table big_table_hashed compute statistics for table;analyze table big_table_hashed compute statistics for all indexes; analyze table big_table_hashed compute statistics for all indexed columns;
请注意：这个索引并非分区键。
使用如下测试脚本对比二者效率：
variable own varchar2(100);declare dumy big_table_hashed%rowtype;begin for i in 1..100 loop for x in ( select distinct owner from big_table_hashed) loop :own := x.owner; select * into dumy from big_table_hashed where owner = :own and rownum =1; end loop; end loop;end;/
variable own varchar2(100);declare dumy big_table%rowtype;begin for i in 1..100 loop for x in ( select distinct owner from big_table) loop :own := x.owner; select * into dumy from big_table where owner = :own and rownum =1; end loop; end loop;end;/使用tkprof工具查看效率对比结果：
hash分区表：
SELECT * FROM BIG_TABLE_HASHED WHERE OWNER = :B1 AND ROWNUM =1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 2100 0.32 0.40 0 0 0 0Fetch 2100 5.54 6.56 3720 1154600 0 2100------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4201 5.87 6.97 3720 1154600 0 2100
普通表：
SELECT * FROM BIG_TABLE WHERE OWNER = :B1 AND ROWNUM =1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 2100 0.39 0.41 0 0 0 0Fetch 2100 0.09 0.11 0 8402 0 2100------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4201 0.48 0.52 0 8402 0 2100
可以看出分区表的查询效率相当的差，原因是索引并非分区键，使用索引查询时必须
遍历所有分区，因此效率差。
删除原来的hash分区表的索引，
创建如下分区索引：
create index big_hash_idx1 on big_table_hashed(owner)global partition by range (owner)( partition values less than ( 'F' ), partition values less than ( 'M' ), partition values less than ( 'T' ), partition values less than ( MAXVALUE ));
再运行上述测试脚本，使用tkprof工具进行比较，结果如下：
对于hash分区表：
SELECT * FROM BIG_TABLE_HASHED WHERE OWNER = :B1 AND ROWNUM =1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 2100 0.46 1.58 0 0 0 0Fetch 2100 0.09 0.14 0 7600 0 2100------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4201 0.56 1.72 0 7600 0 2100
普通表：
SELECT * FROM BIG_TABLE WHERE OWNER = :B1 AND ROWNUM =1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 2100 0.46 0.38 0 0 0 0Fetch 2100 0.06 0.11 0 8402 0 2100------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4201 0.53 0.50 0 8402 0 2100
这一次，因为对全局索引进行了分区，hash分区表的查询效率要好的多，但即使如此，
还没有普通表的普通索引快。
测试结论：如果使用分区表，如果创建的索引不是分区键，建议使用全局索引分区，否则查询效率会很差。
参考文献：
《Oracle高效设计》
