暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 分区索引

Oracle 分区索引

DBA随笔记 2024-12-21
192

1 分区索引

1.1 概述

对于分区表，可以建立不分区索引。也就是说表分区，但是索引不分区。以下着重介绍分区表的分区索引。分区索引就是简单地把一个索引分成多个片段。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上 (避免 I/O 问题)。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的 TBS 中可以提高分区的可用性和可靠性。在使用分区后的表和索引时，Oracle 还支持并行查询和并行 DML。这样就可以同时执行多个进程，从而加快处理这条语句。

1.2 分类

索引与表一样，也可以分区。索引分为两类:locally partition index(本地分区索引) globally partition index(全局分区索引)

其中本地索引又可以分为有前缀(prefix
)的索引和无前缀(nonprefix
)的索引。 而全局索引目前只支持有前缀的索引。

B树和位图索引都可以被分区。 如果使用了位图索引，就必须是本地索引。

有前缀的分区索引：指包含了分区键，并且将其作为引导列的索引。(如此处的 id )。

    -- 如:
    create index i_id_global on PDBA(id)	-- 索引引导列
    global 
    partition by range(id)		-- 分区键
    (
    partition p1 values less than (200),
    partition p2 values less than (maxvalue)
    );


    无前缀的分区索引：无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。
      -- 如：
      create index ix_custaddr_local_id_p on custaddr(id) 
      local
      (
      partition t_list556 tablespace icd_service,
      partition p_other tablespace icd_service
      );
      这个分区是按照area code来的。但是索引的引导列是 ID。所以它就是非前缀分区索引。
       全局分区索引不支持非前缀分区索引
        SQL> create index i_time_global on PDBA(id)	-- 索引引导列
        global
        partition by range(time)				-- 分区建
        (
        partition p1 values less than (TO_DATE('2024-12-1', 'YYYY-MM-DD')),
        partition p2 values less than (maxvalue)
        );
        partition by range(time) 
                               *
        第 3 行出现错误: 
        ORA-14038: GLOBAL 分区索引必须加上前缀
        本地索引Local partition index
        1.本地索引比全局索引容易管理， 而全局索引比较快。 
        2.在表分区后，对每个表分区建立一个索引，对表的索引进行均匀分区称为局部分区。 
        3. Local索引肯定是分区索引，一个索引分区对应一个表分区，分区key就是索引key，分区边界就是索引边界。 
        4. 更新一个表分区时仅仅影响该分区的索引。 
        5. 其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。
        创建本地分区索引
          SQL> create index sale_idx on sale(sales_count) local;	//sale有4个分区，将会建立4个索引
          SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='SALE';
          INDEX_NAME	PARTITION_NAME	HIGH_VALUE
          ----------	--------------	----------
          SALE		P1				1000
          SALE		P2				2000
          SALE		P3				3000
          SALE		P4				MAXVALUE
          SQL> select index_name,partition_name,high_value from user_ind_partitions where index_name='SALE_IDX';
          INDEX_NAME	PARTITION_NAME	HIGH_VALUE
          ----------	--------------	----------
          SALE_IDX	P1				1000
          SALE_IDX	P2				2000
          SALE_IDX	P3				3000
          SALE_IDX	P4				MAXVALUE

          (1).本地索引一定是分区索引，分区键等同于表的分区键。

           (2). 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

           (3). 本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。 

          (4). 本地分区索引是对单个分区的，每个分区索引只指向一个表分区;     全局索引则不然，一个分区索引能指向 n 个表分区，同时，一个表分区，也可能指向 n 个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到 n 个全局索引分区，正为这点，本地分区索引具有更高的可用性。 

          (5). 位图索引必须是本地分区索引。 


          与分区索引有关的视图

            dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)

            dba_ind_partitions  每个分区索引的分区级统计信息

            dba_indexes minus dba_part_indexes （minus操作）可以得到每个表上有哪些非分区索引

            1、查询分区索引相关属性：索引类型  ---dba_part_indexes
            SQL> select  index_name,table_name,partitioning_type,locality,ALIGNMENT from
              dba_part_indexes where table_name='PDBA';
              
            2、查询索引关联的分区信息  --dba_ind_partitions
            SQL> select index_name,partition_name, status from  dba_ind_partitions  where index_name='I_ID_GLOBAL';


            3、查看索引对应的列  --dba_ind_columns


            set linesize 230
            col column_name format a30
            SQL> select table_name,index_name,column_name from dba_ind_columns where table_name='PDBA' and table_owner='SYS';


            4、查询非分区索引的基础信息：存储   --dba_indexes
            SQL> select  owner,index_name,table_name,status  from  dba_indexes  where INDEX_NAME='I_ID_GLOBAL';

            1.3 表分区的维护操作

            添加分区 ADD

              -- 添加一个P3分区:
              ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2024-12-01','YYYY-MM-DD'));
              -- 注意:以上添加的分区界限应该高于最后一个分区界限。
              -- 添加一个P3SUB1子分区:
              ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

              删除分区 Alter..drop

                -- 删除P3表分区:
                ALTER TABLE SALES DROP PARTITION P3;
                -- 删除P4SUB1子分区:
                ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
                --注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

                截断分区 Truncate

                  截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
                  当表中即使只有一个分区时，也可以截断该分区。
                  -- 截断分区:
                  ALTER TABLE SALES TRUNCATE PARTITION P2;
                  -- 截断子分区:
                  ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

                  合并分区 Merge

                  合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

                    -- P1 P2分区的合并:
                    ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

                    拆分分区 Split

                    拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对 HASH 类型的分区进行拆分。

                      ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2024-12-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

                      重命名表分区 Rename

                        -- 以下代码将P21更改为P2
                        ALTER TABLE SALES RENAME PARTITION P21 TO P2;

                        参考链接：https://www.cnblogs.com/james1207/p/3278472.html

                        oracle索引oracle索引
                        文章转载自DBA随笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                        评论