本地分区索引spit测试

好记忆不如烂笔头abc

628人浏览 · 2024-12-31 12:59:08

好记忆不如烂笔头abc · 2024-12-31 12:59:08 发布

测试结论：注意是非复合分区表，如果split不移动数据，分区索引不会失效

也就是说split的时候，没有把数据拆分到两个分区，那么新旧分区的本地索引就不会失效。

哪怕有1条记录被拆分到了新分区，本地索引都将失效，需要重建分区索引。

测试发现复合分区表全局索引和本地索引都失效，需重建。

以下为测试记录：

[oracle@lncs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 31 12:06:54 2024

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn jyc/jyc
Connected.
SQL> drop table part_tab_drop purge;
create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
drop table part_tab_drop purge
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> 2 3 4 5 6 7 8 ;
insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;
commit;
Table created.

SQL>
50000 rows created.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL> create index idx_part_drop_col2 on part_tab_drop(col2) local;

Index created.

SQL> select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL2';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_DROP_COL2 N/A

SQL> select index_name, partition_name, status
from user_ind_partitions
2 3 where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2 P1 USABLE
IDX_PART_DROP_COL2 P2 USABLE
IDX_PART_DROP_COL2 P3 USABLE

SQL> select max(id) from part_tab_drop;

MAX(ID)
----------
50000

SQL>
SQL>
SQL> ALTER TABLE part_tab_drop
2 SPLIT PARTITION p3 AT (50000)
3 INTO (
4 PARTITION p5,
5 PARTITION P_MAX
6 );

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2 P1 USABLE
IDX_PART_DROP_COL2 P2 USABLE
IDX_PART_DROP_COL2 P5 UNUSABLE
IDX_PART_DROP_COL2 P_MAX UNUSABLE

SQL> ALTER TABLE part_tab_drop
2 SPLIT PARTITION P_MAX AT (60000)
3 INTO (
4 PARTITION p6,
5 PARTITION P_MAX
6 );

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

SQL> select count(*) from part_tab_drop PARTITION(P5);

COUNT(*)
----------
30000

SQL> select count(*) from part_tab_drop PARTITION(P6);

COUNT(*)
----------
1

SQL> select count(*) from part_tab_drop PARTITION(P_MAX);

COUNT(*)
----------
0

SQL> alter index IDX_PART_DROP_COL2 rebuild;
alter index IDX_PART_DROP_COL2 rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION p5;

Index altered.

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION p6;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS p5, p6,P_MAX INTO PARTITION P_MAX;
ALTER TABLE part_tab_drop MERGE PARTITIONS p5, p6,P_MAX INTO PARTITION P_MAX
*
ERROR at line 1:
ORA-14126: only a <parallel clause> may follow description(s) of resulting
partitions

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS p6,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS p5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2 P1 USABLE
IDX_PART_DROP_COL2 P2 USABLE
IDX_PART_DROP_COL2 P_MAX UNUSABLE

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION p_max;

Index altered.

SQL> select count(*) from part_tab_drop PARTITION(P_MAX);

COUNT(*)
----------
30001

SQL> select max(id) from part_tab_drop PARTITION(P_MAX);

MAX(ID)
----------
50000

SQL>
SQL>
SQL>
SQL> ALTER TABLE part_tab_drop
2 SPLIT PARTITION p3 AT (50001)
3 INTO (
4 PARTITION p5,
5 PARTITION P_MAX
6 );
SPLIT PARTITION p3 AT (50001)
*
ERROR at line 2:
ORA-02149: Specified partition does not exist

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2 P1 USABLE
IDX_PART_DROP_COL2 P2 USABLE
IDX_PART_DROP_COL2 P_MAX USABLE

SQL>
SQL>
SQL> ALTER TABLE part_tab_drop
2 SPLIT PARTITION P_MAX AT (50001)
3 INTO (
4 PARTITION p5,
5 PARTITION P_MAX
6 );

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'IDX_PART_DROP_COL2';

SQL>

相关参考：

Oracle学习笔记（十）分区索引失效的思考 - 石shi - 博客园

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能