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

最新推荐文章于 2023-06-10 18:36:40 发布
最新推荐文章于 2023-06-10 18:36:40 发布
阅读量468 收藏
点赞数
文章标签： oracle建本地索引

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如下：

如下：

create table T65_F_S_PGZ10023_M

(

STATISTIC_DT DATE,

INDICKEY VARCHAR2(20),

OBJKEY VARCHAR2(64),

INDICVAL1 NUMBER(30,2),

INDICVAL2 NUMBER(30,2),

INDICVAL3 NUMBER(30,2),

INDICVAL4 NUMBER(30,2),

INDICVAL5 NUMBER(30,2)

)

partition by range (STATISTIC_DT)

(

partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01

);

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 processedSQL>

我们删除本地索引，现在创建局部索引

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 statementStatistics----------------------------------------------------------

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 statementStatistics----------------------------------------------------------

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

0b1331709591d260c1c78e86d0c51c18.png

德国人Leo乐柏说
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle SQL执行计划操作（1）——表相关操作
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
11-11 1003
本文详细说明了Oracle SQL语句执行计划中表相关操作，同时，结合实例对其进行了深入讲解。
Oracle 本地索引和全局索引
wozaizhe55
02-21 3115
文章目录背景全局索引本地索引索引的选择全局索引修改为本地索引参考 背景 上一篇Oracle 分区表 讲述了 Oracle 分区表建立的几种方式。那么分区表创建好了之后，其索引该如何选择呢？Oracle 分区表的索引可以分为本地索引和全局索引两种，什么时候改采用哪种索引呢？ 全局索引 全局索引即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。其结构如下图： 本地索引 本地索引其分区形式与
简单介绍oracle执行计划,Oracle性能优化之oracle中常见的执行计划及其简单解释
weixin_31419249的博客
04-15 3154
一、访问表执行计划1、table access full：全表扫描。它会访问表中的每一条记录(读取高水位线以内的每一个数据块)。2、table access by user rowid：输入源rowid来自于用户指定。3、table access by index rowid：输入源rowid来自于索引。4、table access by global index rowid：全局索引获取rowi...
分区索引
04-20 705
2.11.7  分区索引 分区索引就是简单地把一个索引分成多个片段。通过把索引分成多个物理片段，可以访问更小的片段(也更快)，并且可以把这些片段分别存放在不同的磁盘驱动器上(避免I/O竞争)。B树索引和位图索引都可以被分区，而哈希索引不可以被分区。可以有好几种分区方法：表分区而索引未分区；表未分区而索引分区；表和索引都分区。不管采用哪种方法，都必须使用基于成本的优化器。分区从多方面使改善性能
Oracle中的索引
m0_71406734的博客
06-10 8511
了解Oracle中的索引概念，有哪些类型，如何使用索引，清楚索引的作用！
oracle创建哈希索引,ORACLE10g新特性——全局HASH分区索引
weixin_42129300的博客
04-04 1646
这两天看文档时发现，Oracle10g中新增了全局分区索引的类型——HASH分区。在10g以前，Oracle的全局索引分区方法只有一种，即范围分区。SQL> CREATE TABLE T_PART_INDEX (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);表已创建。SQL> INSERT INTO T_PART_INDEX SELEC...
Oracle 分区表 分区索引
12-19
如何创建，管理分区，分区索引。
oracle分区索引的失效和重建代码示例
09-09
Oracle分区索引是一种...综上所述，Oracle分区索引的管理包括创建、删除、重建等操作，这些操作对于维持数据库性能和正确性至关重要。在处理大型数据集时，理解和熟练掌握这些技巧能帮助DBA更有效地维护数据库系统。
Oracle 分区索引介绍和实例演示
12-16
分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需...
oracle分区表分区索引.docx
06-20
下面将详细介绍 Oracle 分区表分区索引的类型、分类、创建方法和维护方式。 一、分区表和分区索引的概念 分区表是指将一个大型表分割成多个小表，每个小表称为一个分区。分区表可以根据不同的方式进行分区，例如...
全面学习分区表及分区索引
最新发布
10-25
本资源旨在全面学习Oracle数据库中的分区表和分区索引，涵盖了分区表和分区索引的基本概念、创建、管理和维护等方面的知识点。 分区表的概念 分区表是指将大型表分割成多个小的、独立的分区，以便于更好地管理和...
oracle 建分区索引_Oracle的分区表和Local索引创建与维护
weixin_39790102的博客
12-19 928
Oracle的分区表和Local索引创建与维护SQL> connect eygle/eygleConnected.SQL> CREATE TABLE dbobjs2 (OBJECT_ID NUMBER NOT NULL,3 OBJECT_NAME varchar2(128),4 CREATED DATE NOT NULL5...
Oracle超详细笔记6--索引创建及管理
QilanAllen的博客
12-27 675
1.Oracle索引简介 在Oracle数据库中，存储的每一行数据都有一个rowID来标识。当Oracle中存储着大量的数据时，意味着有大量的rowID，此时想要快速定位指定的rowID，就需要使用索引对象。 当对Oracle表执行指定条件的查询时，常规的方法是将所有的记录取出来，然后再把每一条记录与查询条件作对比，最后返回满足条件的记录。这样操作不仅耗费时...
Oracle全局索引和局部索引
jc_benben的专栏
03-06 2230
Oracle数据库中，分区表有两种类型的分区索引，全局索引和本地索引，其中本地索引又可以分为本地前缀索引和本地非前缀索引 全局索引以整个表的数据为对象建立索引， 索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，也可能是多个不同键值的组合。 所以，全局索引既允许索引分区的键值和表分区键值相同，也可以不相同. 举例 先建一个分区表： create table t_orders ...
Oracle创建索引
Zach__L的博客
01-21 9232
Oracle 的索引可分为5种，它们包括唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。 1、创建索引的标准语法 以下为引用内容：  CREATE INDEX 索引名 ON 表名 (列名)TABLESPACE 表空间名; 例如： 以下为引用内容：  CREATE INDEX idx_of_imsi ON uim_auth_file(imsi) TABLE...
Oracle索引详解（索引的原理，创建索引，删除索引，修改索引等）
xinliucs的博客
10-09 1万+
Oracle索引的介绍，索引的创建，索引的删除和修改等
table access by index rowid
老农民挖数据
05-22 1万+
table access by index rowid  回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的rowid，根据rowid再查询表中数据，就是回表。 --创建一个表， 索引只建立在object_id上 SQL> create table ml_1 as 2 select * from dba_objects 3 ; Table creat
oracle TABLE ACCESS BY INDEX ROWID 你不知道的索引回表-开发系列（三）
热门推荐
xiaohai798的专栏
11-11 3万+
TABLE ACCESS BY INDEX ROWID 你不知道的索引回表带来的低效率

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值