分区索引总结

超详细解读!数据库表分区技术全攻略 分区是一种数据库优化技术,它可以将大表按照一定的规则分成多个小表,从而提高查询和维护的效率。在分区的过程中,数据库会将数据按照分区规则分配到不同的分区中,并且可以在分区中使用索引和其他优化技术来提高查询效率。在 MySQL 数据库中,我们可以使用分区表来实现分区。分区表是指将一个大表拆分成多个小表,每个小表称为一个分区,分区表中包含多个分区,每个分区对应一个独立的物理存储空间。 阅读详情

一,分区索引分为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的索引
create index id_local on test(id) local;

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索引
drop index id_local;

重新在ID列上创建一个GLOBAL的索引
create index id_global on test(id) global;
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>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索引了
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的必须和其基本分区数一致。
SQL> create index index_tt3_global on TT(id) global;

Index created.


总之,一般建议建立LOCAL的索引,因为GLOBAL的容易所有的都失效,而LOCAL的最多只在某个分区上失效。索引失效必须一个分区的一个分区的REBUILD。

关于分区技术的索引 index 关于分区技术的在了解,了解重要的分区索引 阅读详情

相关推荐

【数据库】银行/信贷业务测试工作中涉及的数据库表格

银行/信贷业务测试工作中涉及的数据库表格,金融信贷测试人必看!

孤之鹜的博客 1173

Oracle分区之五:创建分区索引总结

分区索引总结: 一,分区索引分为2类: 1、global,它必定是Prefix的。不存在non-prefix的 2、local,它又分成2类:    2.1、prefix:索引的第一个列等于表的分区列。    2.2、non-prefix:索引的第一个列不等于表的分区列。    LOCAL的索引只能是表的分区方式,不能自己写分区方式。他们是EQUI-Partition的。GLOBA

Watson的专栏 3万+

零基础用AHK操作Excel:从数据读取到自动填写(附完整脚本解析)

本文为零基础用户详细介绍了如何使用AutoHotkey(AHK)通过COM接口高效操作Excel,实现从数据自动读取到填写的完整自动化流程。文章包含完整的脚本解析、环境搭建指南、核心代码逐行讲解以及实际应用案例,帮助办公人员大幅提升数据处理效率,告别重复劳动。

weixin_29202213的博客 557

oracle创建分区表以及索引

一、分区表、索引的分类 1、分区表原理: 对于10gR2而言,ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在分区内部去查找数据,一个分区一 般保证四十多万条数据就比较正常了, 2、分区表的分类: •    Range(范围)分区 range分区方式,也算是最常用的分区方式,其通过某字段或几个字段的组合的值,从小到大,按照指定的范围说明进行分区,我们在INSER

sheep8521的博客 9686

VLA算法总结对比——RT1 / RT2 / Pi0 / Octo/ RDT / OpenVLA

本文主要针最近比较经典的VLA算法进行对比总结,暂不涉及到代码细节和实验细节上的展开,仅仅是从大面上掌握下目前VLA的一些思路和方法。

weixin_44580210的博客 9515

XDMA 驱动安装与性能测试全流程解析

本文详细解析了XDMA驱动的完整安装与性能测试流程。从环境准备、源码获取、编译安装到驱动加载与设备验证,逐步指导用户打通FPGA加速卡与主机间的高速数据通道。重点介绍了如何通过官方测试脚本进行功能验证与带宽性能测试,帮助用户评估XDMA驱动的实际传输效能,为AI推理、高频交易等低延迟高带宽应用奠定基础。

weixin_29216049的博客 526

Oracle分区表索引

导致全局索引失效,本地索引因为分区已经drop掉了,也就不存在索引失效的说法了,drop分区时指定update global indexes全局索引不会失效。导致全局索引和本地索引都失效,split分区时指定update global indexes全局索引不会失效,split相关的分区本地索引可以重建。导致全局索引失效,不会导致本地索引失效,truncate分区时指定update global indexes全局索引不会失效。不会导致全局和本地索引失效。...

viviliving的专栏 8689

英飞凌 AURIX TC3XX 系列单片机的链接文件

这篇介绍英飞凌 AURIX TC3XX 系列单片机的链接文件,主要是针对使用 SOTA 功能如何修改链接文件,完整地实现 SOTA 功能

大橙子的博客 4387

Oracle(65)如何创建和管理分区索引?

分区索引(Partitioned Index)通过将索引数据分成多个分区,提高了查询性能和管理效率。分区索引特别适用于处理大规模数据集,能够减少查询扫描的数据量,并提高并行处理能力。分区索引通常与分区表一起使用,但也可以在非分区表上创建。

qq_43012298的博客 1460

【数字孪生与仿真技术】10:半导体制造中的虚拟量测技术深度解析:从多尺度建模到工业级应用

摘要:半导体制造过程中,晶圆加工需实时监测300+关键参数,传统物理量测技术面临产能损耗、延迟检测等痛点。本文系统阐述虚拟量测(Virtual Metrology)技术的完整解决方案,通过多尺度混合建模实现物理机理与数据驱动的深度融合。内容涵盖等离子体刻蚀、薄膜沉积等关键工艺的物理模型构建,LSTM神经网络的数据校正算法,以及边缘-云协同的实时部署架构。某12英寸晶圆厂实践表明,虚拟量测技术可减少70%物理量测频次,异常漏检率从18%降至3%,机台利用率提升11%。

专注于人工智能、软件开发、工控自动化、工厂数字化及智能化等领域,希望和大家共同进步! 2558

vlan的LWIP使用配置

VLAN (Virtual Local Area Network)是将大的广播域划分为小的域的逻辑概念。VLAN协议基于IEEE 802.1Q。VLAN可以看作是一个子网。两个不同的子网在没有网桥或路由器的情况下无法相互通信。

weixin_43084908的博客 721

oracle 建分区索引_Oracle分区索引

索引与表类似,也可以分区;分区索引分为两类:Locally partitioned index(局部分区索引)Globally partitioned index(全局分区索引)下面就来详细解析一下这两类索引。一:Locally partitioned index(局部分区索引)1.概念:局部分区索引随表对索引完成相应的分区(即索引会使用与底层表相同的机制分区),每个表分区都有一个索引分区,并且...

weixin_34051133的博客 4490

S7-1200PLC与昆仑通态触摸屏以太网通信的具体方法和步骤(图文)

S7-1200PLC与昆仑通态触摸屏以太网通信的具体方法和步骤(图文)

Robot_PLC_HMI_Automation 1万+

mysql 分区 全局索引_全局分区索引与局部分区索引

分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。 1.全局非分区索引可以依赖普通的表,也可以依赖分区表建立。 CREATE INDEX month_ix ON s分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索...

weixin_42641385的博客 4324

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

Oracle索引和分区索引

一.索引 1.索引的概念 索引是一种可选的与表相关的数据库对象。数据库中引入索引的目的是为了提高对表中数据的查询速度。ORACLE并不是机械地为所有查询都使用索引,而是根据用户的具体使用情况,决定是否使用索引搜索数据。 2.索引类型 1 B树索引:又称平衡树索引,按照平衡树算法来组织的索引的,适合索引值取值范围广泛、重复率低的应用。2 位图索引:按位图结构组织的索引,适合索引取值范围小、重复率高...

CX的博客 2757

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

DB2分区表如何区分索引是分区索引还是非分区索引

问题描述: 经常有人问,我分区表里的索引到底是分区索引还是非分区索引? 因为是否是分区索引涉及到detach分区的时候是否会耗费大量的时间做异步索引清理:如果是非分区索引,则异步索引清理需要大量时间。 总体结论: --对于唯一索引或者主健,如果包含了分区健,则默认是分区索引;如果不包含分区健,则默认是非分区索引。 --对于非唯一索引,默认都是分区索引。 测试过程 DB2版本为10....

匿_名_用_户的专栏 3287

Data-Structures-and-Algorithm-Analysis-in-Java.pdf

作者: Weiss, Mark A. 出版社: Addison Wesley Data Structures and Algorithm Analysis in Java is an “advanced algorithms” book that fits between traditional CS2 and Algorithms Analysis courses. In the old ACM Curriculum Guidelines, this course was known as CS7. This text is for readers who want to learn good programming and algorithm analysis skills simultaneously so that they can develop such programs with the maximum amo...

分区表和分区索引

/* 分区表 */优点:* 提高查询性能,执行查询时只需要访问分区* 易于管理和维护,各个分区在进行维护时是相互独立的,可以单独对分区进行重建索引,备份和恢复等操作.对表进行分区后,各个分区可以具有不同的存储参数,或者可以位于不同的表空间中.一个表可以分割成任意数目的分区,但如果在表中包含有LONG或者LONG RAW类型的字段,则不能进行分区如下情况需要对表进行分区1.表的大小超过2G2.需要对

可 科 的 家 2511

oracle 普通索引分区索引,在Oracle中,分区索引分为哪几类?

♣答案部分索引按照是否分区可以分为分区索引(Partitioned Indexes)和非分区索引(NonPartitioned Indexes),如下图所示:图3-12分区索引与分区表类似,分区索引被分解成更小、更易于管理的索引片断。分区索引提高了可管理性、可用性、和可扩展性。分区索引根据索引列是否包含分区键及分区键是否是索引的引导列可以分为有前缀的分区索引和无前缀的分区索引。有前缀的分区索引指的...

weixin_33343681的博客 1424

25、分区 索引分区

索引分区分为本地索引分区和全局索引分区 全局索引不反应基础表的结构,因此若要分区就只能进行范围分区。 局部索引反应基础表结构,因此对表的分区或子分区进行维护时,系统会自动对本地索引分区进行维护,而不需要对本地索引的分区进行维护。 本地索引分区 使用和分区表同样的分区键进行分区的索引,也就是说,索引分区所采用的列表与该表的分区所采用的列是相同的。 本地索引分区的优点: 1.如果只有一个分区需要维护,则只有一个本地索引受影响。 2.支持分区独立性 3.只有本地索引能够支持单一分区的装入和卸载。 4.分表区和各自

我的菠萝呀的博客 744

MySQL分区及索引

mysql分区后每个分区成了独立的文件,虽然从逻辑上还是一张表其实已经分成了多张独立的表,从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中(从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外)),所以分区表的索引也是随着各个分区单独存储。

liudachu的博客 2258

Oracle 分区索引简介

⏹Oracle 分区索引简介

菜鸟记录 1745

Oracle非分区索引,全局分区索引和本地分区索引。

1.如果按照索引是否分区作为划分依据,Oracle 的索引类型可以分为非分区索引,全局分区索引和本地分区索引。 2.创建演示实例 --创建非分区表create table test_partition_01( number_1 number, number_2 number, string_1 varchar2(10), string_2 varchar2(20) ...

bantaoti1130的博客 1206

Oracle分区表及分区索引的创建

关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类:•          Range(范围)分区•          Hash(哈希)分区•          List(列表)分区•          以及组合分区:Range-Hash,Range-List。  对于表而言(常规意义上的堆组织表),上述分区形式都可...

linux__xu的博客 5万+

oracle删表分区同时维护索引,Oracle 分区表的索引、分区索引

对于分区表,可以建立不分区索引。也就是说表分区,但是索引不分区。以下着重介绍分区表的分区索引。索引与表一样,也可以分区。索引分为两类:locally partition index(局部分区索引)、globally partition index(全局分区索引)。1. 局部分区索引(locally partition index)局部分区索引随表对索引完成相应的分区,即索引会使用与表相同的机制进行...

weixin_29056781的博客 1821

分区表中主键索引是该是全局还是分区索引?

如题,现有一分区表,除主键索引外其余索引均为分区索引,疑问如下: 1、现在有dba指出修改计划将表主键的全局索引修改为分区索引,不知道修改后能不能够起到优化的作用?? 2、分区索引对主键中的数据一致性有没有影响?? 3、在不影响生产库的情况下怎样将全局主键索引修改为分区索引??   按照我的理解答的,不全对啊。谢谢 答1,分区表在管理上的确非常方便,在性能方面的表现也不错。 但是就是有

cuiyan1982的专栏 4142

ORACLE分区表、分区索引详解

ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在分区内部去查找数据,一个分区一般保证四十多万条数据就比较正常了,但是分区表并非乱建立,而其维护性也相对较为复杂一点,而索引的创建也是有点讲究的,这些以下尽量阐述详细即可。 1、类型说

shengguocun的专栏 2653
上一篇: oracle定时添加或删除分区表的分区
下一篇: oracle定期生成和删除表分区
myheartjava
博客等级 码龄14年 6粉丝 6原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值