pg分区表怎么加索引分区表如何建索引

转载

mob64ca13f83523 2024-04-25 11:26:14

文章标签 pg分区表怎么加索引分区表 oracle 表分区 文章分类 数据仓库大数据

全局索引：

create index idx_t1 on system.t1(date_id,comp_kpi_code) tablespace users parallel 4;
 --实际上，不加global关键字创建的也是全局索引
 --680G的分区表，16并行创建索引，大约用时90分钟左右
 alter index system.idx_t1 noparallel;
 --为了建索引速度快，会加上并行，加上并行之后，此索引就会是并行了。
 --访问有并行度的索引时，CBO可能可能会考虑并行执行，这可能会引发一些问题，如在服务器资源紧张的时候用并行会引起更加严重的争用。当使用并行后，需要把索引的并行度改成1SQL> select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1';
  
 OWNER                          INDEX_NAME                     DEGREE                                   PARTITIONED STATUS   LAST_ANALYZED
 ------------------------------ ------------------------------ ---------------------------------------- ----------- -------- -------------
 SYSTEM                         IDX_T1                         1                                        NO          VALID    2018/8/2 8:56 
move分区，truncate分区，rename表，drop分区后对索引的影响测试：
alter table system.t1 move partition PART201806 tablespace TBS_ZBA_DMA;
 --move的分区如果没有数据，索引不会失效否则会失效
 alter table system.t1 move partition PART201808 tablespace users update indexes parallel 4;
 --move表空间的同时自动维护索引，并且索引的degree不会改变alter table system.t1 truncate partition part201806;
 --如果分区上有数据索引会失效，否则不会失效
 alter table system.t1 truncate partition part201806 update indexes;
 --truncate分区并维护索引，防止索引失效alter table system.t1 drop partition PART201809;
alter table system.t1 rename to t2;
 --分区表上的索不失效alter index system.idx_t1 unusable;
 alter table system.t1 move partition PART201808 tablespace users update indexes parallel 4;
 --上面两种，都会更新dba_objects.last_ddl_time时间 select * from DBA_PART_KEY_COLUMNS where name='T1';
 --查看某个分区表的分区键create index system.idx_t1_global on system.t1(month_id) global
 partition by range(date_id,comp_kpi_code)
 ( partition part2017 values less than ('201801') tablespace users,
 partition part2018 values less than ('201901') tablespace users,
 partition partmax values less than (maxvalue) tablespace users
 );
 --全局分区索引，只能创建在分区键上。 move有数据的分区表后，全局索引失效，get_ddl中就会有
 alter index xxxx unusable的语句。
 last_ddl_time的时间不会更新。------------------------------

本地索引：

create index system.idx_t1_local on system.t1(date_id,comp_kpi_code) local tablespace users parallel 2;
SQL> select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1' and owner='SYSTEM';
  
 OWNER                          INDEX_NAME                     DEGREE                                   PARTITIONED STATUS   LAST_ANALYZED
 ------------------------------ ------------------------------ ---------------------------------------- ----------- -------- -------------
 SYSTEM                         IDX_T1_LOCAL                   1                                        YES         N/A      2018/8/2 8:11select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1';
 --如果是分区索引，partitioned＝yes，status＝N/Aselect p.owner,p.index_name,p.alignment,i.partition_name,i.status,i.tablespace_name,i.logging,i.compression,i.last_analyzed
 from dba_part_indexes p,dba_ind_partitions i
 where p.index_name=i.index_name and p.owner=i.index_owner
 and p.table_name='T1';
 --查看分区索引的信息alter table system.t1 move partition PART201806 tablespace TBS_ZBA_DMA;
 --本地索引会变为unusable
 alter index system.idx_t1_local rebuild partition part201806 online parallel 4;
 alter index system.idx_t1_local noparallel;
 --重建本地分区索引alter table t1 truncate partition part201808;
 --不影响本地分区索引alter table t1 add partition part201809 values less than ('201810') ;
 --添加分区会自动添加分区索引alter table system.t1 drop partition PART201809;
 --不影响其他分区上的索引alter table system.t1 rename to t2;
 --不影响分区索引状态 ------------------------------

测试用表：

CREATE TABLE "SYSTEM"."T1" 
    (    "MONTH_ID" VARCHAR2(6), 
     "DAY_ID" VARCHAR2(2), 
     "DATE_ID" VARCHAR2(8), 
     "PROV_ID" VARCHAR2(10), 
     "AREA_NO" VARCHAR2(10), 
     "PRODUCT_CLASS" VARCHAR2(30), 
     "COMP_KPI_CODE" VARCHAR2(50), 
     "KPI_VALUE" NUMBER, 
     "D_LD_VALUE" NUMBER, 
     "D_LM_VALUE" NUMBER, 
     "D_CMT_VALUE" NUMBER, 
     "D_LMT_VALUE" NUMBER, 
     "D_LY_VALUE" NUMBER, 
     "D_LMY_VALUE" NUMBER
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   STORAGE(
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "TBS_ZBA_DMA" 
   PARTITION BY RANGE ("MONTH_ID") 
  (PARTITION "PART201804"  VALUES LESS THAN ('201805') 
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING 
   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "TBS_ZBA_DMA" , 
  PARTITION "PART201805"  VALUES LESS THAN ('201806') 
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING 
   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "TBS_ZBA_DMA" );

------------------------------
create /rebuild index online时会修改数据库字典表obj$,并在该索引用户下创建表sys_journal_obj#（具体的对象号）和在ind$、ind_online$表里(ind_online$字典基表记录了索引在线创建/重建的历史)标记256或512,（11g里rebuild online是514？？）。
如果服务器进程在语句执行过程中意外终止的话，可能会导致相关在ind$标记位信息及在线日志中间表不能及时处理及清除（清除动作一般有smon进程处理,如果重建过程异常中断,smon会清理重建痕迹,
但是如果系统非常繁忙导致smon无法清除,或dml操作频繁,导致smon无法获取相关表上的锁,从而造成无法清理痕迹,
当再次重建索引或对表进行dml操作会报本篇提示错误），这将导致对该索引的后续操作因ora-08104错误而无法继续，如果是分区表，索引是global，在添加分区也无法继续。

ORA-08104: 该索引对象 275316 正在被联机建立或重建:

sqlplus / as sysdba
 declare
 done boolean;
 begin
    done:=dbms_repair.online_index_clean(275316);
 end;
 /

------------------------------
本地索引特点：

1.本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，本地索引的分区机制和表的分区机制一样。
2.如果本地索引的索引列以分区键开头，则称为前缀局部索引。
3.如果本地索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。
4.前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
5.本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。
6.本地分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，
对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，本地分区索引具有更高的可用性。
7.位图索引只能为本地分区索引。
8.本地索引多应用于数据仓库环境中。
本地索引:创建了一个分区表后，如果需要在表上面创建索引，并且索引的分区机制和表的分区机制一样，那么这样的索引就叫做本地分区索引。
本地索引是由ORACLE自动管理的，它分为有前缀的本地索引和无前缀的本地索引。什么叫有前缀的本地索引？有前缀的本地索引就是包含了分区键，
并且将其作为引导列的索引。什么叫无前缀的本地索引？无前缀的本地索引就是没有将分区键的前导列作为索引的前导列的索引。下面举例说明：
创建本地索引
本地索引前缀(prefix)的索引和无前缀(nonprefix)的索引，就是是否是分区键上的索引。

全局索引特点：

1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。
2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
4.全局索引多应用于oltp系统中。
5.全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。
6.oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
7.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。

全局索引：与本地分区索引不同的是，全局分区索引的分区机制与表的分区机制不一样。
全局分区索引全局分区索引只能是B树索引，到目前为止(10gR2)，oracle只支持有前缀的全局索引。
另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果执行修改的语句不加上update global indexes的话，那么索引将不可用

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。