普通表索引，分区表局部索引和分区表的全局索引分区的效率对比测试

首先创建一个大表：

create table big_table
as
select 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 constraint
big_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 nologging
partition by hash(object_id) partitions 10
as
select * 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           0
Execute   2100      0.32       0.40          0          0          0           0
Fetch     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           0
Execute   2100      0.39       0.41          0          0          0           0
Fetch     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           0
Execute   2100      0.46       1.58          0          0          0           0
Fetch     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           0
Execute   2100      0.46       0.38          0          0          0           0
Fetch     2100      0.06       0.11          0       8402          0        2100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4201      0.53       0.50          0       8402          0        2100

 

这一次，因为对全局索引进行了分区，hash分区表的查询效率要好的多，但即使如此，

还没有普通表的普通索引快。

 

测试结论：如果使用分区表，如果创建的索引不是分区键，建议使用全局索引分区，否则查询效率会很差。

 

参考文献：

《Oracle高效设计》