oracle分区索引介绍及实例演示
分区索引（或索引分区）主要为分区表，数据量的增长，普通堆表需要转换的分区表，及其指标转换为分区索引，分区索引的优势是明显的，它仅仅是一个指标分为多段，只有一个较小的索引碎片（块）可以访问时，获取所需的数据。同时，放置在不同的表空间可以提高分区的分区的可用性和可靠性。本文主要介绍了分区索引的相关特性，给出了一个实例。1、分区索引的相关概念。
分区索引的几种方式：表被分割，索引没有被分区，表没有被分区，索引被划分，表和索引被划分。
分区索引可分为局部分区索引和全局分区索引。
局部分区索引：
本地分区索引信息的存储依赖于父表分区，也就是说，必须根据分区表创建本地索引。
默认情况下，当创建本地索引时，如果未指定索引，则自动存储表空间，并在数据分区时自动将本地索引存储在表空间中。
本地索引可以是B-树索引或位图索引，如表的分区机制。
本地索引是一个单独的分区，每个分区索引只指向一个表分区，这是一个对等分区。
本地索引支持分区独立性，因此对于这些单独的分区，不需要同时删除或重建截取、删除、分割和脱机处理。
本地索引主要用于数据仓库环境中。
全局分区索引：
当全局分区索引被索引时，分区表与全局索引不同，分区键的范围和值必须在创建时定义。
全局分区索引应该在创建全局关键字时创建，全局分区索引只能是B树索引。
全局索引可以分区或非分区，全局索引必须是前缀索引，也就是说，索引列必须包含分区键。
在全局索引分区中，分区索引可以指向n个表分区，而表分区也可以指向n个索引分区。
默认情况下，全局索引被添加到分区中，当索引的截取、删除和分区必须被重建或修改时，必须指定更新全局索引。
全局分区索引仅为一个范围或哈希散列分区。
全局分区索引主要用于OLTP系统。
前缀索引和非前缀索引
局部索引和全局分区索引分为前缀索引和非前缀索引。
前缀和非前缀索引都可以支持消除索引分区，前提是查询的条件包含索引分区键。
有一个前缀索引：
前缀索引包含分区键，也就是索引中包含分区键列。
前缀索引支持本地分区索引和全局分区索引。
非前缀索引：
如果没有前缀索引，分区键的前导列不作为索引的前导列。
非前缀索引只支持本地分区索引。
2。局部分区索引演示
复制代码代码如下所示：
环境
SQL >选择*从V版本rownum < 2美元；
旗帜
----------------------------------------------------------------
Oracle数据库10g企业版发布10.2.0.3.0 - 64bi
SQL >创建用户leshami经XXX；
已授予DBA leshami；
-创建演示所需的表空间
创建表空间的数据文件的SQL > tbs_tmp / / / sybo2 U02数据库 / / tbs_tmp oradata dbf'size 10m自动；
SQL >修改用户的默认表空间tbs_tmp leshami；
创建表空间的数据文件的SQL > tbs1 / / / sybo2 U02数据库 / / tbs1 oradata dbf'size 10m自动；
创建表空间的数据文件的SQL > tbs2 / / / sybo2 U02数据库 / / tbs2 oradata dbf'size 10m自动；
创建表空间的数据文件的SQL > tbs3 / / / sybo2 U02数据库 / / tbs3 oradata dbf'size 10m自动；
创建表空间的数据文件的SQL > IDX1 / / / sybo2 U02数据库 / / oradata IDX1 dbf'size 10m自动；
创建表空间的数据文件的SQL > idx2 / / / sybo2 U02数据库 / / idx2 oradata dbf'size 10m自动；
创建表空间的数据文件的SQL > idx3 / / / sybo2 U02数据库 / / oradata idx3 dbf'size 10m自动；
SQL> Conn leshami / XXX
-创建查找表
创建表（查找）
身份证号码（10），
描述VARCHAR2（50）
）；
-添加主键约束
修改表查找（添加）
约束lookup_pk主键（ID）
）；
插入数据
插入查找（ID，描述）值（1话）；
插入查找（ID，描述）值（2，）；
插入查找（ID，描述）值（3，）；
承诺；
创建表（big_table
身份证号码（10），
created_date日期，
lookup_id号（10），
数据VARCHAR2（50）
）
按范围分区（created_date）
（分区big_table_2012值小于（to_date（01 / 01 / 2013，'dd /毫米/ yyyy））的表空间tbs1，
分区big_table_2013值小于（to_date（01 / 01 / 2014，'dd /毫米/ yyyy））的表空间tbs2，
分区big_table_2014值小于（次）表空间tbs3）；
-将数据填充到分区表中
声明
l_lookup_id查找ID为型；
l_create_date日期；
开始
我在1…10000环路
如果mod（i，3）＝0
l_create_date：= add_months（SYSDATE，- 24）；
l_lookup_id：= 2；
elsif MOD（I，2）= 0
l_create_date：= add_months（SYSDATE，- 12）；
l_lookup_id：= 1；
其他的
l_create_date：= SYSDATE；
l_lookup_id：= 3；
最后如果；
插入big_table（ID，created_date，lookup_id，数据）
（我的价值观，l_create_date，l_lookup_id，这是我| |一些数据）；
结束循环；
承诺；
结束；
/
在不指定索引分区和存储表空间的情况下创建索引
已创建big_table指数bita_created_date_i（created_date）的地方；
建立索引。
SQL >选择index_name，partitioning_type partition_count，从user_part_indexes；
index_name分区partition_count
----------------------------------------------------
bita_created_date_i范围3
--作者：Leshami
从下面的查询中，索引直接存储到与表表相对应的表空间中。
SQL >选择partition_name，high_value tablespace_name，从user_ind_partitions；
partition_name high_value tablespace_name
----------------------------------------------------------------------------------------------------
big_table_2014次tbs3
big_table_2013 to_date（'2014-01-01 00:00:00，'syyyy-m tbs2
m-dd hh24：MI：学生，'nls_calendar =格雷戈里亚
big_table_2012 to_date（'2013-01-01 00:00:00，'syyyy-m tbs1
m-dd hh24：MI：学生，'nls_calendar =格雷戈里亚
删除索引
SQL >索引bita_created_date_i；
-指定索引分区名称、表空间名称、创建索引
SQL >创建索引bita_created_date_i
2 big_table（created_date）
3局部（
4 idx_2012 IDX1分区表空间，
5 idx_2013 idx2分区表空间，
6 idx_2014 idx3）分区表空间
7平行3；
建立索引。
SQL >选择partition_name，high_value tablespace_name，从user_ind_partitions；
partition_name high_value tablespace_name
----------------------------------------------------------------------------------------------------
idx_2014 MAXVALUE idx3
idx_2013 to_date（'2014-01-01 00:00:00，'syyyy-m idx2
m-dd hh24：MI：学生，'nls_calendar =格雷戈里亚
idx_2012 to_date（'2013-01-01 00:00:00，'syyyy-m IDX1
m-dd hh24：MI：学生，'nls_calendar =格雷戈里亚
SQL >选择*从big_table在rownum < 2；
我created_ lookup_id数据
------------------------------------------------------------------------------
1413201206252这是1413的一些数据。
-看看是否使用本地索引，从下面的执行计划中知道索引是用来支持分区消除的。
SQL >设置autot微量口；
SQL >选择*从big_table哪里created_date = to_date（'20120625 '，'yyyymmdd）；
执行计划
----------------------------------------------------------
计划哈希值：2556877094
--------------------------------------------------------------------------------------------------------------------------
Id Operation Name | | | | Rows | | Bytes Cost (%CPU) Time Pstart| Pstop | | |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 41 2（0）| 00:00:01 | | |
1单个分区| | | | | | 1 41 2（0）| 00:00:01 | 1 | 1 |
表2 | |访问本地索引rowid | big_table | | | 1 41 2（0）| 00:00:01 | 1 | 1 |
3索引范围扫描| * | | bita_created_date_i | 1 | | 1（0）| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------
三.全局分区索引演示
复制代码代码如下所示：
-为表添加主键
SQL> ALTER TABLE big_table（添加
2约束big_table_pk主键（ID）
3）；
表的改变。
SQL >选择index_name，index_type，tablespace_name，global_stats，分区
2从user_indexes哪里index_name = 'big_table_pk；
index_name index_type tablespace_name GLO PAR
---------------------------------------------------------------------------------------------
big_table_pk正常tbs_tmp啊
SQL >设置autot微量口；
SQL> select * from big_table id是1412；
执行计划
----------------------------------------------------------
计划哈希值：2662411593
-------------------------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| PStart Pstop | | |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 62 2（0）| 00:00:01 | | |
1表访问的| |全局索引rowid | big_table | | | 1 62 2（0）| | | | 00:00:01 ROWID ROWID
2索引唯一扫描| * | | big_table_pk | 1 | | 1（0）| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------
-如上所述，在执行计划中，Pstart和Pstop是rowid
-全局索引rowid的出现，我们没有指定全局当我们添加主键，但执行计划表明，全局索引访问执行
-对于分区表，这一点仍有待确认，主键或非分区键上的唯一索引是否必须是全局索引。
SQL >索引bita_created_date_i；
-在下面创建全局索引，在创建时需要指定分区键的范围和值。
SQL >创建索引bita_created_date_i
在big_table（created_date）
在全球范围内分区（created_date）
（
分区
idx_1值小于（to_date（01 / 01 / 2013，'dd /毫米/ yyyy））
表空间IDX1，
分区
idx_2值小于（to_date（01 / 01 / 2014，'dd /毫米/ yyyy））
表空间idx2，
分区idx_3值小于（次）表idx3）；
SQL >选择index_name，partitioning_type，partition_count，当地user_part_indexes；
index_name分区partition_count定位
----------------------------------------------------------
bita_created_date_i_g全球范围3
SQL >选择partition_name，high_value tablespace_name，从user_ind_partitions；
partition_name high_value tablespace_name
---------------------------------------------------------------------------------
idx_1 to_date（'2013-01-01 IDX1
idx_2 to_date（'2014-01-01 idx2
idx_3 MAXVALUE idx3
-下面是可以支持分区消除的执行计划
SQL >设置autot微量口；
SQL >选择*从big_table哪里created_date = to_date（'20130625 '，'yyyymmdd）；
执行计划
----------------------------------------------------------
计划哈希值：1378264218
---------------------------------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| PStart Pstop | | |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 41 2（0）| 00:00:01 | | |
1单个分区| | | | | | 1 41 2（0）| 00:00:01 | 2 | 2 |
2表访问的| |全局索引rowid | big_table | | | 1 41 2（0）| 00:00:01 | 2 | 2 |
3索引范围扫描| * | | bita_created_date_i | 1 | | 1（0）| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------------------------------
以下是一个范围查询，PStart 1和Pstop 2，同时也支持分区消除
SQL SELECT * FROM big_table >
2在created_date > = to_date（'20120625 '，'yyyymmdd）和created_date <= to_date（'20130625 '，'yyyymmdd）；
执行计划
----------------------------------------------------------
计划哈希值：213633793
------------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| PStart Pstop | | |
------------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | 3334 133k | 14（0）| 00:00:01 | | |
1分区范围的迭代器| | | | | 3334 133k | 14（0）| 00:00:01 | 1 | 2 |
2表访问全| * | | | | big_table 3334 133k | 14（0）| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------