我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Oracle如何创建分区索引

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

Oracle如何创建分区索引

这篇文章主要介绍了Oracle如何创建分区索引,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。


分区索引总结:

一,分区索引分为2类:


1、global,它必定是Prefix的。不存在non-prefix的
2、local,它又分成2类:
   2.1、prefix:索引的第一个列等于表的分区列。
   2.2、non-prefix:索引的第一个列不等于表的分区列。

  
LOCAL的索引只能是表的分区方式,不能自己写分区方式。他们是EQUI-Partition的。
GLOBAL索引可以不分区,这个时候就是普通的一个索引。同一个列只能只有一个索引,这个列可以是GLOBAL或者是LOCAL的索引。如果唯一索引所在的列不是表的分区列,只能建立GLOBAL索引。

例如:分区表
create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);

--在ID列上创建一个LOCAL的索引
SQL>create index id_local on test(id) local;

Index created.

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
ID_LOCAL                       P1                             10000                USABLE
ID_LOCAL                       P2                             20000                USABLE
ID_LOCAL                       P3                             MAXVALUE             USABLE

从上面可以看出索引的分区和表一样,即是EQUI-PARTITION

--如果我在表上增加个分区,则Oracle会自动维护分区的索引,注意此时加分区必须是用split,直接加会出错的。例如:
SQL> alter table test add partition p4 values less than (30000);
alter table test add partition p4 values less than (30000)
                               *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4);

Table altered.

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
ID_LOCAL                       P1                             10000                USABLE
ID_LOCAL                       P2                             20000                USABLE
ID_LOCAL                       P3                             30000                USABLE
ID_LOCAL                       P4                             MAXVALUE             USABLE

 

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_LOCAL';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_LOCAL                       NORMAL                      TEST


--删除id_local索引
SQL> drop index id_local;

Index dropped.

--重新在ID列上创建一个GLOBAL的索引
SQL> create index id_global on test(id) global;

Index created.

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';

no rows selected

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_GLOBAL                      NORMAL                      TEST

从上面可以看出,它此时是个普通索引。dba_ind_partitions里根本就没有记录。

---删除索引
SQL> drop index id_global;

Index dropped.

注意:不删会报:ORA-01408: such column list already indexed

--创建全局索引

SQL> create index i_id_global on test(data) global
   partition by range(id)
   ( partition p1 values less than (10000) ,
     partition p2 values less than (MAXVALUE)
   );
   partition by range(id)
                        *
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed
此错误表示GLOBAL的索引必须是prefixed,即索引分区的列,必须是其基表的分区列。


SQL>create index id_global on test(id) global
   partition by range(id)
   ( partition p1 values less than (10000) ,
     partition p2 values less than (MAXVALUE)
   );

Index created.


SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
ID_GLOBAL                      P1                             10000                USABLE
ID_GLOBAL                      P2                             MAXVALUE             USABLE

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_GLOBAL                      NORMAL                      TEST

从上面可以看出,它此时是个GLOBAL的索引了。dba_ind_partitions里有记录。请和上面的做个比较,加深印象。

 

二,到底如何判断建立怎样的分区索引(GLOBAL 还是LOCAL)

我将用下面的例子来分析到底需要创建什么类型索引好。

create table TT(id number,createdate date)
partition by range(createdate)
(
  partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')),
  partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')),
  partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')),
  partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')),
  partition Q_OTHERS VALUES LESS THAN (MAXVALUE)
);

注意:只能是to_date,其他的任何函数都不行,maxvalue必须在最后,他可以包括NULL值。


第一种情况:
如果查询的语句的条件是where createdate='2012-10-19' and id>100,则此时查询的是4号分区,假设他有10万条记录。在扫描这10万条记录的时候,

可以使用id列上的索引。这个时候可以在ID列上建立个local nonprofiex索引
create index index_tt1_local on TT(id) local
( partition p1,
  partition p2,
  partition p3,
  partition p4,
  partition p5
);

注意:索引分区的数量和其基本的分区数量要一样。

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT1_LOCAL                P1                             TO_DATE(' 2012-03-30 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL                P2                             TO_DATE(' 2012-06-30 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL                P3                             TO_DATE(' 2012-09-30 USABLE

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL                P4                             TO_DATE(' 2012-12-31 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL                P5                             MAXVALUE             USABLE

 

 

第二种情况:

如果查询的语句条件只有一个createdate,如where createdate='2010-10-19',则这种情况就在createdate上建立一个local profiex索引
SQL> create index index_TT2_local on TT(createdate) local;

Index created.

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT2_LOCAL                Q1                             TO_DATE(' 2012-03-30 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL                Q2                             TO_DATE(' 2012-06-30 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL                Q3                             TO_DATE(' 2012-09-30 USABLE

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL                Q4                             TO_DATE(' 2012-12-31 USABLE
                                                               00:00:00', 'SYYYY-M
                                                              M-DD HH24:MI:SS', 'N
                                                              LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL                Q_OTHERS                       MAXVALUE             USABLE

从上面查询可以看出他和表是equi-partitioned.

 

第三种情况:

如果查询根本就没有createdate,而是有像where id>100的条件,则就只能在ID列上建立GLOBAL索引了
SQL> drop index index_tt1_local;

Index dropped.

注意:不删报ORA-01408: such column list already indexed

SQL>
create index index_tt3_global on TT(id)
global partition by range(id)
(
 partition p1 values less than (100000),
 partition p2 values less than (200000),
 partition p3 values less than (MAXVALUE)
);

从上面可以看出,GLOBAL的索引的分区数和其基表是没有关系的。他甚至可以像如下建立索引,即一个普通索引。但是LOCAL的必须和其基本分区数一致。

-创建需先删索引index_tt3_global

SQL> create index index_tt3_global on TT(id) global;

Index created.

感谢你能够认真阅读完这篇文章,希望小编分享的“Oracle如何创建分区索引”这篇文章对大家有帮助,同时也希望大家多多支持亿速云,关注亿速云行业资讯频道,更多相关知识等着你来学习!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

Oracle如何创建分区索引

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

oracle如何创建索引

oracle 中创建索引需遵循以下步骤:确定表和索引列。使用 create index 语句,指定索引名称、表名称和列名称。指定索引类型(默认 b-tree),并可添加 unique、primary key 等选项。注意事项包括性能影响、维
oracle如何创建索引
2024-06-12

MySQL怎么创建分区索引

要在MySQL中创建分区索引,首先需要确保表已经被分区。对于已经分区的表,可以使用ALTER TABLE语句来添加分区索引。以下是创建分区索引的步骤:首先,使用ALTER TABLE语句来添加分区索引。例如,如果要为名为table_nam
MySQL怎么创建分区索引
2024-04-23

什么是索引,在Oracle中如何创建索引

索引是一种数据结构,它可以提高数据库检索数据的性能。索引是一个指向表中数据位置的指针,可以帮助数据库引擎更快地定位和检索数据。在Oracle数据库中,可以使用CREATE INDEX语句来创建索引。语法如下:CREATE INDEX i
什么是索引,在Oracle中如何创建索引
2024-04-09

oracle分区表怎么建索引

在Oracle中,可以使用以下语法来为分区表建立索引:```CREATE INDEX index_nameON table_name (column1, column2, ...)LOCAL (PARTITION partition_nam
2023-08-18

Oracle创建和管理分区索引的操作方法

目录创建分区索引1. 创建分区表2. 创建本地分区索引3. 创建全局分区索引管理分区索引1. 重建分区索引http://www.lsjlt.com3. 拆分分区4. 删除分区5. 查看分区索引状态示例代码总结创建和管理分区索引(Partit
Oracle创建和管理分区索引的操作方法
2024-08-21

oracle如何查看索引创建时间

要查看Oracle数据库中索引的创建时间,可以通过以下步骤进行操作:1. 首先,使用以下命令登录到Oracle数据库的SQL*Plus或任何其他可用的SQL客户端应用程序中:```sqlplus username/password@data
2023-08-20

oracle分区索引失效如何解决

当Oracle分区索引失效时,可以尝试以下几种解决方法:1. 重新构建分区索引:使用ALTER INDEX语句来重建分区索引,例如:ALTER INDEX index_name REBUILD PARTITION partition_nam
2023-08-25

mysql索引如何创建

mysql索引创建指南:确定要索引的列:常被搜索或排序的列。选择索引类型:b-tree索引(范围查询)、哈希索引(等值查询)、全文索引(文本搜索)、空间索引(地理空间搜索)。命名索引:指定有意义的名称。创建索引:使用create index
mysql索引如何创建
2024-08-01

oracle索引怎么创建

oracle中创建索引的步骤有三个:1. 确定要索引的列;2. 确定索引类型;3. 创建索引。Oracle 索引创建方法创建索引的步骤:确定要索引的列。确定索引类型。创建索引。确定要索引的列:选择索引列时,应考虑以下因素:经常用于查
oracle索引怎么创建
2024-05-21

MySQL索引如何创建和使用索引

创建MySQL索引可以通过以下两种方式:使用CREATE INDEX语句创建索引:CREATE INDEX index_name ON table_name (column_name);例如,要在名为students的表上创建一个名为stu
MySQL索引如何创建和使用索引
2024-03-06

mysql中如何创建索引

这篇文章主要介绍mysql中如何创建索引,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。1、
2023-06-15

编程热搜

目录