Oracle分区表创建本地索引和局部索引的性能

本文通过对比本地索引和全局索引在Oracle数据库中的表现,详细分析了不同索引类型对查询性能的影响。实验中创建了一个分区表并对其进行了索引建立,通过实际案例展示了两种索引类型下查询执行计划的区别,以及它们对递归调用、一致性读取等关键指标的影响。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >



1、演示环境:
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
 
2、测试数据创建:
表创建SQL如下:

点击(此处)折叠或打开
1. create table T65_F_S_PGZ10023_M
2. (
3.   STATISTIC_DT DATE,
4.   INDICKEY VARCHAR2(20),
5.   OBJKEY VARCHAR2(64),
6.   INDICVAL1 NUMBER(30,2),
7.   INDICVAL2 NUMBER(30,2),
8.   INDICVAL3 NUMBER(30,2),
9.   INDICVAL4 NUMBER(30,2),
10.   INDICVAL5 NUMBER(30,2)
11. )
12. partition by range (STATISTIC_DT)
13. (
14.   partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
15.   partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
16.   partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
17.   partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
18.   partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
19.   partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
20.   partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
21.   partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
22.   partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
23.   partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
24.   partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
25.   partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01 
26. );

CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 PARALLEL LOCAL;


测试数据生成SQL如下:
SQL>insert into T65_F_S_PGZ10023_M(INDICKEY,STATISTIC_DT,OBJKEY,INDICVAL1)
 select 'PGZ10023' as INDICKEY, to_date('2012-01-08','yyyy-mm-dd') as STATISTIC_DT ,'81'||lpad(to_char(rownum),7,'0') as OBJKEY  ,trunc(dbms_random.value(1,rownum)) as INDICVAL1 from dual connect by  rownum <100000;

修改日期可以得到2012-01-01 到2012-01-08这8天的测试数据
测试数据按天汇总的记录数
SQL>  select statistic_dt,count(*) from t65_f_s_pgz10023_m group by statistic_dt;
 
STATISTIC_DT   COUNT(*)
------------ ----------
2012-01-01        99999
2012-01-02        99999
2012-01-03        99999
2012-01-04        99999
2012-01-05        99999
2012-01-06        99999
2012-01-07        99999
2012-01-08        99999
3、 使用AUTOTRACE查看执行计划
打开CMD命令窗口,使用 aml 用户连接数据库
C:\Users\Administrator>sqlplus aml/abc
开启执行计划
SQL> set autotrace traceonly
设置行长度
SQL> set lines 300;
SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';
得到如下的执行计划,此索引是本地索引
SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2061015615


---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                | Name                              |  Rows  | Bytes | Cost (%CPU)| Time      | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                                      |     8    |   960 |    14   (0)    | 00:00:01 |         |         |
|   1 |  PARTITION RANGE ALL                             |                                      |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | T65_F_S_PGZ10023_M     |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|*  3 |    INDEX RANGE SCAN                              | PI_T65_F_S_PGZ10023_M |     8    |         |    13   (0)    | 00:00:01 |     1 |    12 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("OBJKEY"='810000836')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         32    recursive calls
          0    db block gets
        197   consistent gets
         32    physical reads
          0     redo size
       1182  bytes sent via SQL*Net to client
        492   bytes received via SQL*Net from client
          2    SQL*Net roundtrips to/from client
          0    sorts (memory)
          0    sorts (disk)
          8    rows processed

SQL>
我们删除本地索引,现在创建局部索引
SQL> drop index PI_T65_F_S_PGZ10023_M;
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 global;
将缓冲池清空
SQL> alter system flush buffer_cache;
再次执行刚才的SQL
SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';
全局索引的执行计划
8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3364546307


---------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                             | Name                            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart    | Pstop   |
---------------------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT                              |                                     |     8   |   960 |    12   (0)    | 00:00:01 |            |            |
|   1  |  TABLE ACCESS BY GLOBAL INDEX ROWID| T65_F_S_PGZ10023_M    |     8   |   960 |    12   (0)    | 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                              | I_T65_F_S_PGZ10023_M |     8   |          |     3   (0)    | 00:00:01 |            |            |
---------------------------------------------------------------------------------------------------------------------------


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

   2 - access("OBJKEY"='810000836')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0    recursive calls
          0    db block gets
         12   consistent gets
         32   physical reads
          0    redo size
       1182  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2    SQL*Net roundtrips to/from client
          0    sorts (memory)
          0    sorts (disk)
          8    rows processed

 objkey字段索引比较
Statistics 本地索引(LOCAL) 全局索引(GLOBAL)
recursive calls (递归调用)    32      0 
db block gets  (数据块)     0      0
consistent get  (一致性读)    197     12 
physical reads  (物理读)    32     32 
redo size                              0      0 
bytes sent via SQL*Net to client        1182   1182 
bytes received via SQL*Net from client   492    492 
SQL*Net roundtrips to/from client          2      2 
sorts (memory)                         0      0 
sorts (disk)                            0      0 
rows processed                          8      8 
查看段的记录数:
SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                                                            INDEX
全局索引的数据段只有一条记录,而本地索引的数据段有12条记录(创建了12个分区定义)。
 
SQL> drop index i_t65_f_s_pgz10023_m;
 
Index dropped
 
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
 
Index created
 
SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                             PT_20111231                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120100                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120101                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120102                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120103                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120104                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120105                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120106                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120107                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120108                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120109                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120111                    INDEX PARTITION
 
12 rows selected
 
看了索引的数据段分布,现在可以理解本地索引的一致性读与物理读为什么那么高,这是因为查找 objkey='810000836'这个值要去每个分区的段中查找,并将结果合并后返回。

创建分区字段的索引比较
SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 LOCAL;
SQL> exec dbms_stats.gather_table_stats(ownname => 'AML',tabname => 'T65_F_S_PGZ10023_M');
SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');
99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation                          | Name                          | Rows  | Bytes  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT           |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE  |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL          | T65_F_S_PGZ10023_M | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |

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


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          1         recursive calls
          0        db block gets
       7188      consistent gets
         549      physical reads
          0        redo size
    3186021   bytes sent via SQL*Net to client
      73818    bytes received via SQL*Net from client
       6668     SQL*Net roundtrips to/from client
          0       sorts (memory)
          0       sorts (disk)
      99999   rows processed
 
 
创建全局索引的执行计划
SQL> drop index PI_T65_F_S_PGZ10023_M_DT;
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 global;
SQL> alter system flush buffer_cache;
SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');
99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT       |                    | 99991 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE|                    | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | T65_F_S_PGZ10023_M | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
-------------------------------------------------------------------------------------------------------------


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1     recursive calls
          0     db block gets
       7188   consistent gets
        549    physical reads
          0      redo size
    3186021  bytes sent via SQL*Net to client
      73818   bytes received via SQL*Net from client
       6668   SQL*Net roundtrips to/from client
          0     sorts (memory)
          0     sorts (disk)
      99999  rows processed
 
STATISTIC_DT字段索引比较
Statistics 本地索引(LOCAL) 全局索引(GLOBAL)
recursive calls (递归调用)       1         1
db block gets  (数据块)          0         0
consistent get  (一致性读)  7188    7188
physical reads  (物理读)      549       549
redo size                                    0         0
bytes sent via SQL*Net to client          3186014   3186014
bytes received via SQL*Net from client      73818     73818
SQL*Net roundtrips to/from client            6668      6668
sorts (memory)                               0         0
sorts (disk)                                 0         0
rows processed                           99999     99999

来自 <http://blog.itpub.net/8092037/viewspace-1172757/>

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值