分区索引失效的处理！

最新推荐文章于 2022-04-22 10:42:36 发布
最新推荐文章于 2022-04-22 10:42:36 发布
阅读量4.1k 收藏 2
点赞数
分类专栏： partition 文章标签： sql table less user insert c
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zq9017197/article/details/6834069
版权

--创建范围表分区：

SQL> drop table t_partition_range;

表已删除。

SQL> create table t_partition_range (id number,name varchar2(50))  
  2  partition by range(id)( 
  3  partition t_range_p1 values less than (10) tablespace tbspart01,
  4  partition t_range_p2 values less than (20) tablespace tbspart02,
  5  partition t_range_p3 values less than (30) tablespace tbspart03,  
  6  partition t_range_pmax values less than (maxvalue) tablespace tbspart04); 

表已创建。

SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables where TABLE_NAME = 'T_PARTITION_RANGE';

TABLE_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
T_PARTITION_RANGE              RANGE                 4

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RANGE';

PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_RANGE_P1                     10              TBSPART01
T_RANGE_P2                     20              TBSPART02
T_RANGE_P3                     30              TBSPART03
T_RANGE_PMAX                   MAXVALUE        TBSPART04

 

--创建全局范围索引分区：

SQL> create index idx_parti_range_id on t_partition_range(id)  
  2  global partition by range(id)(  
  3  partition i_range_p1 values less than (10) tablespace tbspart01,  
  4  partition i_range_p2 values less than (20) tablespace tbspart02,  
  5  partition i_range_p3 values less than (30) tablespace tbspart03,
  6  partition i_range_pmax values less than (maxvalue) tablespace tbspart04);  

索引已创建。

SQL> select index_name,partitioning_type,partition_count from user_part_indexes where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
IDX_PARTI_RANGE_ID             RANGE                 4

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RANGE'; 

PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_RANGE_P1                     10              TBSPART01
T_RANGE_P2                     20              TBSPART02
T_RANGE_P3                     30              TBSPART03
T_RANGE_PMAX                   MAXVALUE        TBSPART04


--查看分区表的数据：

SQL> insert into t_partition_range values(1,'a');

已创建 1 行。

SQL> insert into t_partition_range values(11,'b');

已创建 1 行。

SQL> insert into t_partition_range values(21,'c');

已创建 1 行。

SQL> insert into t_partition_range values(31,'d');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_partition_range;

        ID NAME
---------- --------------------------------------------------
         1 a
        11 b
        21 c
        31 d

SQL> select * from t_partition_range partition(T_RANGE_P1);

        ID NAME
---------- --------------------------------------------------
         1 a

SQL> select * from t_partition_range partition(T_RANGE_P2);

        ID NAME
---------- --------------------------------------------------
        11 b

SQL> select * from t_partition_range partition(T_RANGE_P3);

        ID NAME
---------- --------------------------------------------------
        21 c

SQL> select * from t_partition_range partition(T_RANGE_PMAX);

        ID NAME
---------- --------------------------------------------------
        31 d


--查看分区索引的状态：

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes where INDEX_NAME = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
IDX_PARTI_RANGE_ID             ING                            T_PARTITION_RANGE              N/A

SQL> select INDEX_NAME,STATUS from user_ind_partitions where INDEX_NAME = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PARTI_RANGE_ID             USABLE
IDX_PARTI_RANGE_ID             USABLE
IDX_PARTI_RANGE_ID             USABLE
IDX_PARTI_RANGE_ID             USABLE

user_indexes.STATUS: Indicates whether a nonpartitioned index is VALID or UNUSABLE
valid:当前索引有效
unusable:索引失效
N/A :分区索引，它的status可以从user_ind_partitions中获得

 

--删除表分区：（可以看见，删除分区会删除相应分区的数据）

SQL> alter table T_PARTITION_RANGE drop partition T_RANGE_P1;

表已更改。

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RANGE';

PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_RANGE_P2                     20              TBSPART02
T_RANGE_P3                     30              TBSPART03
T_RANGE_PMAX                   MAXVALUE        TBSPART04

SQL> select * from t_partition_range;

        ID NAME
---------- --------------------------------------------------
        11 b
        21 c
        31 d


--第二次查看索引状态：（可以看见，删除表分区以后，导致索引失效，rebuild之后又变为有效拉。）

SQL> select INDEX_NAME,partition_name,high_value,tablespace_name,status from user_ind_partitions where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------------- ------------------------------ --------
IDX_PARTI_RANGE_ID             I_RANGE_P1                     10              TBSPART01                      UNUSABLE
IDX_PARTI_RANGE_ID             I_RANGE_P2                     20              TBSPART02                      UNUSABLE
IDX_PARTI_RANGE_ID             I_RANGE_P3                     30              TBSPART03                      UNUSABLE
IDX_PARTI_RANGE_ID             I_RANGE_PMAX                   MAXVALUE        TBSPART04                      UNUSABLE

SQL> alter index IDX_PARTI_RANGE_ID rebuild partition i_range_p1;

索引已更改。

SQL> alter index IDX_PARTI_RANGE_ID rebuild partition i_range_p2;

索引已更改。

SQL> alter index IDX_PARTI_RANGE_ID rebuild partition i_range_p3;

索引已更改。

SQL> alter index IDX_PARTI_RANGE_ID rebuild partition i_range_pmax;

索引已更改。

SQL> select INDEX_NAME,partition_name,high_value,tablespace_name,status from user_ind_partitions where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------------- ------------------------------ --------
IDX_PARTI_RANGE_ID             I_RANGE_P1                     10              TBSPART01                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_P2                     20              TBSPART02                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_P3                     30              TBSPART03                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_PMAX                   MAXVALUE        TBSPART04                      USABLE


--删除表分区指定update indexes子句：（可以看见，指定update indexes子句索引不会失效。）

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RANGE';

PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_RANGE_P2                     20              TBSPART02
T_RANGE_P3                     30              TBSPART03
T_RANGE_PMAX                   MAXVALUE        TBSPART04

SQL> alter table T_PARTITION_RANGE drop partition T_RANGE_P2 update indexes;

表已更改。

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RANGE';

PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_RANGE_P3                     30              TBSPART03
T_RANGE_PMAX                   MAXVALUE        TBSPART04

SQL> select INDEX_NAME,partition_name,high_value,tablespace_name,status from user_ind_partitions where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE      TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------------- ------------------------------ --------
IDX_PARTI_RANGE_ID             I_RANGE_P1                     10              TBSPART01                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_P2                     20              TBSPART02                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_P3                     30              TBSPART03                      USABLE
IDX_PARTI_RANGE_ID             I_RANGE_PMAX                   MAXVALUE        TBSPART04                      USABLE
事后诸葛亮
关注
专栏目录
oracle分区索引的失效和重建代码示例
09-09
以下是关于Oracle分区索引失效和重建的一些关键知识点： 1. **分区表的创建**： 在示例中，创建了一个名为`T_PART`的分区表，根据`OBJECT_ID`字段的值范围进行分区。`PARTITION BY RANGE`语句定义了不同分区的边界...
mysql分区唯一索引失效_[项目踩坑] MySQL 分区:分区键和唯一索引主键的关系，解决报错 A PRIMARY KEY...
weixin_39767513的博客
01-27 1335
场景【项目踩坑】Mysql分区:分区键和唯一索引主键的关系，解决报错 A PRIMARY KEY must include all columns in the table’s partitioning今天在建立mysql 分区表，设置主键与索引时候报错错误:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tabl...
Oracle执行了删除分区操作后，索引无效的情况
猿人的专栏
05-08 1721
Oracle异常ORA-01502: 索引或这类索引的分区处于不可用状态 原因： 出现这个问题，可能有人move过表，或者disable 过索引。  1. alter table xxxxxx move tablespace xxxxxxx 命令后，索引就会失效。 2. alter index index_name  unusable，命令使索引失效。 解决办法：  1.
mysql复合(组合)索引失效的几种情况总结（未使用索引、索引中断、范围查询、否定条件、模糊查询、使用函数、类型转换及“key_len”的说明）
whl8614john的博客
09-13 4761
复合(组合)索引失效的几种情况总结 创建一张测试表：emp 表 CREATE TABLE emp ( id INT (11) NOT NULL AUTO_INCREMENT, empno INT NOT NULL, NAME VARCHAR (30) DEFAULT NULL, age INT (13) DEFAULT NULL, deptId INT (11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREME
单个分区索引失效导致绑定变量查询无法使用索引
ckawt40802的专栏
06-27 149
一个客户碰到的问题，由于分区维护操作，导致个别分区对应的索引处于UNUSABLE状态，最终导致基于绑定变量的查询无法利用索引。 通过一个具体的例子来说明这个问题： SQ...
分区索引笔记（四）--分区索引什么时候会失效及处理
ITer-Tony
08-25 7576
参考链接：http://space.itpub.net/35489/viewspace-684473        分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行，需要注意的是global 索引，当global索引所在表执行alter table 涉及下列操作时，会导至该索引失效，需要重新建立： Ø ADD PARTITION | SUBPARTITION
【MySQL进阶学习】优化索引与分区表
12-14
4. 避免在列上进行运算，比如在`WHERE`子句中使用函数，因为这可能导致索引失效，转为全表扫描。 然而，有时即使建立了索引，查询也不一定会使用它们，例如： 1. 查询包含`IS NOT NULL`条件。 2. 使用`NOT`操作符，...
数据库索引设计与优化
04-22
3. 分区索引：对于大型表，可以采用分区技术，将数据分成多个逻辑部分，每个部分有自己的索引，提高查询效率。 4. 定期分析和调整：定期分析索引的使用情况，对未使用的或低效的索引进行调整或删除。 五、索引选择...
数据库索引设计和优化
06-24
2. 避免索引失效：使用函数、IN操作符、NOT操作符等可能导致索引失效，应尽量让索引列直接参与比较。 3. 选择正确的JOIN策略：了解不同JOIN类型的性能差异，如NLJOIN、HASH JOIN和MERGE JOIN。 五、实际应用中的...
mysql 分区索引失效_数据库分区表全局索引失效故障
weixin_39595008的博客
01-21 1963
现场实施反馈一个分区表的几个全局索引老是失效。通过视图v$sql, v$sqlarea等视图查看，对这张分区表执行过的操作有drop partition和truncate partition操作，但是我查询了跟SQL相关视图(v$sql, v$sqlarea，还是几张历史记录的视图)，但是查询不到操作的具体时间，用这个方法定位今天分区表是否有drop partition和truncate part...
mysql 分区索引失效_分区表的本地索引竟然失效了——ORA-01502
weixin_39607620的博客
02-01 562
由于昨天对一个千万级别的分区表做了分区数据交换的操作，因为本表操作太慢，所以将其移出普通表操作后才移回分区表，但是今天又反馈该表的某字段为条件查询速度很慢，自己试了下果真如此，我看了查询语句的执行计划发现根本没用上索引(该字段是一个本地索引)，因此就想用hint强制使用该索引，结果报错了ora-01502.本地索引失效了？我很纳闷，什么原因导致的呢，难不成是我昨晚的操作导致的？因此网上一查，看到该...
Oracle 分区表索引失效（ORA-01502）
Luxy_wang的博客
08-26 2711
最近在做一个数据相关的项目，业务表采用了分区。在数据处理过程中由于部分数据需要清理，就采用了以下命令进行清除： alter table tablename truncate partition partiotionname; 清除之后再次使用insert向表中插入数据时，出现了以下异常： ORA-01502: 索引或这类索引的分区处于不可用状态 经确认，是因为tuncate表分区时，导致分区索引无效，之后采取以下措施进行索引重建。 查询分区无效索引： select index_name fr
关于 Oracle 分区索引的失效和重建
yimenglin的博客
11-07 4686
–创建测试表 SQL> create table t as select object_id,object_name from dba_objects; 表已创建。 SQL> select min(object_id),max(object_id) from t; MIN(OBJECT_ID) MAX(OBJECT_ID) -------------- -----------...
mysql索引失效的常见9种原因详解
qq_63815371的博客
04-22 2万+
目录 前言： 1.最佳左前缀法则 2.主键插入顺序 3.计算、函数、类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(!= 或者<>)导致索引失效 6.is null可以使用索引，is not null无法使用索引 7.like以通配符%开头索引失效 8.OR 前后只要存在非索引的列，都会导致索引失效 9.数据库和表的字符集统一使用utf8mb4 特别鸣谢： 前言： MySQL中提高性能的一个最有效的方式是对数据表设计合...
row_number() over (partition by * order by *) 排序失效
zhangchao的博客
05-13 3764
问题描述： 执行sql select v1.*, row_number() over (partition by v1.em_code, v1.cldate order by v1.rn) as rn2 from v1 结果 code status create_date rn rn2 hzdz-scsb014 1 2020/5/9 8:30:00 2 1 hzdz-scsb014 3 2020/5/9 8:46:06 1
【第三篇】MySQL 索引失效的常见原因【重点】
sunnyday0426的博客
03-03 1万+
1.1 概述    有时候不知道小伙伴有没有跟我一样的情况，明明已经建立了索引，但是通过explain发现语句并没有使用上索引，这可能是某些原因导致了我们的索引失效。所以本篇主要介绍的是索引失效的常见原因和如何用好索引，给有需要的朋友一个参考。 1.2 索引失效 1.2.1 索引失效常见原因 我们先来看一张图，总结了常见的索引失效的原因 1.2.2 索引失效常见误区 1.2.3 索引设计的几个建议 1.3 索引示例 1.3.1 准备工作 创建一张表 test1 CREATE TABLE `test1`
遇到的难题,sql2000分区表无法建立索引
weixin_30372371的博客
05-14 145
sql2000的分区表就是视图,而视图建立索引,要求视图的定义中不能有union, 而分区表的定义却就是采用 union all 才能建立分区表,这岂不是无解??? 分区表建立一个索引要求必须绑定结构,而绑定结构要求字段不能有text的字段,郁闷, 转载于:https://www.cnblogs.com/qqq88zz/archive/2011/05/14/2046131.html...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值