分区表和分区索引（global local）的性能对比测试

最新推荐文章于 2023-11-08 14:37:12 发布

cov98559

最新推荐文章于 2023-11-08 14:37:12 发布

阅读量385

点赞数

文章标签：数据库

分区表以及分区索引测试的场景
建立如下四个独立的tablespace；然后建立四个相应的table和index。测试那种配置下性能最优。
由于环境限制，table或index分区都没有独立的tablespace，但是每种情形测试条件都是相同的，以保证测试结果的可比性

table index tablespace

不分区 ,不分区 , tp_tno_ino
不分区 ,global hash分区 ,tp_tno_igh
hash分区 ,local hash分区 ,tp_th_ilh
hash分区 ,global hash分区 ,tp_th_igh

create tablespace tp_tno_ino datafile '/export/home/oracle/oradata/tp_tno_ino.dbf' size 3g;
create tablespace tp_tno_igh datafile '/export/home/oracle/oradata/tp_tno_igh.dbf' size 3g;
create tablespace tp_th_ilh datafile '/export/home/oracle/oradata/tp_th_ilh.dbf' size 3g;
create tablespace tp_th_igh datafile '/export/home/oracle/oradata/tp_th_igh.dbf' size 3g;
--不分区不分区
create table atest_tno_ino tablespace tp_tno_ino as select * from dtcqt_dt_record where 1=2 ;
alter table atest_tno_ino nologging;
insert /*+ APPEND */ into atest_tno_ino select * from dtcqt_dt_record;
commit;
create index ind_atest_tno_ino on atest_tno_ino (file_id) tablespace tp_tno_ino ;
analyze table atest_tno_ino compute statistics;

--不分区 global hash分区
create table atest_tno_igh tablespace tp_tno_igh as select * from dtcqt_dt_record where 1=2;
alter table atest_tno_igh nologging;
insert /*+ APPEND */ into atest_tno_igh select * from atest_tno_ino;
commit;
create index ind_atest_tno_igh on atest_tno_igh (file_id) tablespace tp_tno_igh global partition by hash(file_id) partitions 4;
analyze table atest_tno_igh compute statistics;

--hash分区 local hash分区 tp_th_ilh
create table atest_th_ilh partition by hash(file_id) partitions 4 tablespace tp_th_ilh
as select * from dtcqt_dt_record where 1=2;
alter table atest_th_ilh nologging;
insert /*+ APPEND */ into atest_th_ilh select * from atest_tno_ino;
commit;
create index ind_atest_th_ilh on atest_th_ilh(file_id) local store in (tp_th_ilh);
analyze table atest_th_ilh compute statistics;

--hash分区 global hash分区 tp_th_igh
create table atest_th_igh partition by hash(file_id) partitions 4 tablespace tp_th_igh
as select * from dtcqt_dt_record where 1=2;
alter table atest_th_igh nologging;
insert /*+ APPEND */ into atest_th_igh select * from atest_tno_ino;
commit;
create index ind_atest_th_igh on atest_th_igh(file_id) global partition by hash(file_id) partitions 4
store in (tp_th_igh);
analyze table atest_th_igh compute statistics;

相同的sql查看每个表的执行计划
SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.46
SQL>
SQL>
SQL> select * from atest_th_igh where file_id =283;

已选择21883行。

已用时间: 00: 00: 33.79

执行计划
----------------------------------------------------------
Plan hash value: 1322205582

--------------------------------------------------------------------------------

----------------------------------------

Cost (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------------------

| 0 | SELECT STATEMENT | | 19514 | 18M|

11617 (1)| 00:02:20 | | |

| 1 | PARTITION HASH SINGLE | | 19514 | 18M|

11617 (1)| 00:02:20 | 2 | 2 |

| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| ATEST_TH_IGH | 19514 | 18M|

11617 (1)| 00:02:20 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | IND_ATEST_TH_IGH | 18331 | |

197 (1)| 00:00:03 | 2 | 2 |

--------------------------------------------------------------------------------

----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("FILE_ID"=283)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6652 consistent gets
3737 physical reads
116 redo size
24904177 bytes sent via SQL*Net to client
16415 bytes received via SQL*Net from client
1460 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21883 rows processed

SQL> select * from atest_th_ilh where file_id =283;

已选择21883行。

已用时间: 00: 00: 31.46

执行计划
----------------------------------------------------------
Plan hash value: 498468522

--------------------------------------------------------------------------------

---------------------------------------

--------------------------------------------------------------------------------

---------------------------------------

| 0 | SELECT STATEMENT | | 19514 | 18M|
2889 (1)| 00:00:35 | | |

| 1 | PARTITION HASH SINGLE | | 19514 | 18M|
2889 (1)| 00:00:35 | 2 | 2 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| ATEST_TH_ILH | 19514 | 18M|
2889 (1)| 00:00:35 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | IND_ATEST_TH_ILH | 19514 | |
43 (0)| 00:00:01 | 2 | 2 |

--------------------------------------------------------------------------------

---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("FILE_ID"=283)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6641 consistent gets
3725 physical reads
72 redo size
24904177 bytes sent via SQL*Net to client
16415 bytes received via SQL*Net from client
1460 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21883 rows processed

SQL> select * from atest_tno_igh where file_id =283;

已选择21883行。

已用时间: 00: 00: 44.89

执行计划
----------------------------------------------------------
Plan hash value: 920489920

--------------------------------------------------------------------------------

----------------------------------

--------------------------------------------------------------------------------

----------------------------------

| 0 | SELECT STATEMENT | | 20094 | 20M| 3186

(1)| 00:00:39 | | |

| 1 | PARTITION HASH SINGLE | | 20094 | 20M| 3186

(1)| 00:00:39 | 2 | 2 |

| 2 | TABLE ACCESS BY INDEX ROWID| ATEST_TNO_IGH | 20094 | 20M| 3186

(1)| 00:00:39 | | |

|* 3 | INDEX RANGE SCAN | IND_ATEST_TNO_IGH | 20094 | | 45

(0)| 00:00:01 | 2 | 2 |

--------------------------------------------------------------------------------

----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("FILE_ID"=283)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
37587 consistent gets
3725 physical reads
72 redo size
24904177 bytes sent via SQL*Net to client
16415 bytes received via SQL*Net from client
1460 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21883 rows processed

SQL> select * from atest_tno_ino where file_id =283;

已选择21883行。

已用时间: 00: 00: 34.79

执行计划
----------------------------------------------------------
Plan hash value: 2316676857

--------------------------------------------------------------------------------

-----------------

%CPU)| Time |

--------------------------------------------------------------------------------

-----------------

| 0 | SELECT STATEMENT | | 20094 | 20M| 3186
(1)| 00:00:39 |

| 1 | TABLE ACCESS BY INDEX ROWID| ATEST_TNO_INO | 20094 | 20M| 3186
(1)| 00:00:39 |

|* 2 | INDEX RANGE SCAN | IND_ATEST_TNO_INO | 20094 | | 45
(0)| 00:00:01 |

--------------------------------------------------------------------------------

-----------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("FILE_ID"=283)

统计信息
----------------------------------------------------------
190 recursive calls
0 db block gets
6657 consistent gets
3729 physical reads
0 redo size
24904177 bytes sent via SQL*Net to client
16415 bytes received via SQL*Net from client
1460 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21883 rows processed

SQL>

总结：这四种情况下，只有table不分区而索引分区的情形测试结果最劣，其他几种情形相似，由于global index维护的复杂性，建议使用local partition index
因为local index partition的维护由oracle自动维护

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/70612/viewspace-1026841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/70612/viewspace-1026841/