Oracle 分区策略实施

嗨，团队，

我们有几十亿行的非分区审计表，现在我们正在尝试对这些表进行分区并定期存档。
从您的博客中，我们了解到分区策略应该完全基于数据访问和可管理性。
我们的大多数表都是11g数据库中的父/子关系，因此我们在POC中选择了复合引用分区。
主分区是Range over (log_date_time) 和子分区Hash on Corpid (CIID编号)。
我们测试了主键 (CIID，ADLOGKEY) 和 (log_date_time，CIID，ADLOGKEY) 上的全局哈希/本地索引
在索引上使用全局哈希 (insert在较少索引争用的情况下表现更好)，但在适当的索引范围内选择3个月的不良位置。
使用本地索引，我们看到insert比全局2-3mints具有更多的争用和选择做得好。
我们想要一个OLTP和OLAP的最优解决方案。

我们的大多数select查询将基于1到18个月之间的logdatetime范围，每个查询中都有Corpid (CIID) 上的VPD上下文。
例如:

 select few columns from FROM  ADLOG_TMP, ADLOG_DETAIL_TMP
 WHERE  ADLOG_TMP.account_id IS NOT NULL
       AND ADLOG_TMP.auditlog_key = ADLOG_DETAIL_TMP.auditlog_key (+)
       AND (ADLOG_TMP.log_date_time) >= to_date('1/1/2018 00:00:00 ','mm/dd/yyyy hh24:mi:ss')
       AND (ADLOG_TMP.log_date_time) <= to_date('12/31/2018 23:59:59','mm/dd/yyyy hh24:mi:ss')
       AND (ADLOG_TMP.record_type) NOT IN (
   'Forfeiture Rate',
            'Performance Measurement',
            'Contribution'
            
    )
    AND nvl((ADLOG_TMP.log_type), 'NULL_LOG_TYPE') = CASE ('All')
        WHEN 'All'         THEN nvl( (ADLOG_TMP.log_type),'NULL_LOG_TYPE')
  WHEN 'Additions'   THEN 'ADD'
  WHEN 'Deletions'   THEN 'DELETE'
  WHEN 'Changes'     THEN 'CHANGE'
    END
复制

Partition strategy POC details :


1) 表上的单分区范围和索引主键上的全局范围 (CIID，ADLOGKEY)
2) 复合分区范围-哈希 (logdatetime/CIID) 和本地索引 (logdatetime，CIID，ADLOGKEY)

单个/复合分区是否有区别？请建议。

在两种情况下，指标都是相似的

单分区-插入经过的时间索引 (全局)
6m 6:23:24
选择经过时间
400克朗4个月2:23:24

复合 (范围/哈希) 6M 6:00 23索引 (本地)
400克朗4Mon 1:50 23

复合 (范围/哈希) 6m 4:59:05 (全局哈希)
选择经过时间
400krows 4个月查询需要很长时间。在7个薄荷糖中仅获取1500行。

问题:
1) 我们应该根据插入和选择进行单/复合分区。
2) 在这种情况下，哪种类型的索引会更好
3) 为什么选择在globalHash索引中表现不佳。

表分区:

CREATE TABLE "XXX"."ADLOG_TMP" 
   ( "AUDITLOG_KEY" NUMBER(38,0) NOT NULL ENABLE, 
 "RECORD_TYPE" VARCHAR2(100 CHAR), 
 "ACCOUNT_ID" VARCHAR2(11 CHAR), 
 "GRANT_NUMBER" VARCHAR2(25 CHAR), 
 "REFERENCE" VARCHAR2(100 CHAR), 
 "AUDIT_CONTEXT" VARCHAR2(100 CHAR), 
 "LOG_TYPE" VARCHAR2(15 CHAR), 
 "USER_ID" VARCHAR2(30 CHAR), 
 "LOG_DATE_TIME" DATE not null, 
 "PROCESS_TYPE" VARCHAR2(50 CHAR), 
 "LASTNAME" VARCHAR2(30 CHAR), 
 "RECORDCOUNT" NUMBER, 
 "RELATION" NUMBER, 
 "PROCESS_KEY" NUMBER, 
 "PARENTCHECK" NUMBER(1,0), 
 "CORPINSTANCE_ID" NUMBER(15,0) DEFAULT SYS_CONTEXT ('VPDSETM_EEO_CONTEXT','CIID') NOT NULL ENABLE, 
 "RECORD_LAST_MODIFIED" DATE, 
 "RECORD_ADDED" DATE, 
 "USER_NAME" VARCHAR2(20 CHAR), 
 "VERSIONCOLUMN" NUMBER(15,0), 
 "LOG_DATE" DATE , 
 "SQLTEXT" VARCHAR2(4000 CHAR)
  )
  SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 15 MAXTRANS 255 
 COMPRESS FOR OLTP  LOGGING 
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EEO_LARGE_DATA" 
  PARTITION BY RANGE ("LOG_DATE_TIME") 
  SUBPARTITION BY HASH(CORPINSTANCE_ID) SUBPARTITIONS 64
( PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P2"  VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P3"  VALUES LESS THAN (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P4"  VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P5"  VALUES LESS THAN (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P6"  VALUES LESS THAN (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P7"  VALUES LESS THAN (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P8"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P9"  VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P10"  VALUES LESS THAN (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P11"  VALUES LESS THAN (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P12"  VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P13"  VALUES LESS THAN (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P14"  VALUES LESS THAN (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P15"  VALUES LESS THAN (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P16"  VALUES LESS THAN (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P17"  VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P18"  VALUES LESS THAN (TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P19"  VALUES LESS THAN (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P20"  VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P21"  VALUES LESS THAN (TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P22"  VALUES LESS THAN (TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P23"  VALUES LESS THAN (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P24"  VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P25"  VALUES LESS THAN (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P26"  VALUES LESS THAN (TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P27"  VALUES LESS THAN (TO_DATE(' 2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P28"  VALUES LESS THAN (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P29"  VALUES LESS THAN (TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
  PARTITION "P30"  VALUES LESS THAN (TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION "P31"  VALUES LESS THAN (TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  );
  
Child Table :
=========
 CREATE TABLE "xxxx"."ADLOG_DETAIL_TMP" 
   (
    "AUDITLOGDETAIL_KEY" NUMBER(38,0) not null, 
 "AUDITLOG_KEY" NUMBER(38,0) not null, 
 "FIELD_CHANGED" VARCHAR2(30 CHAR), 
 "PRIOR_VALUE" VARCHAR2(500 CHAR), 
 "NEW_VALUE" VARCHAR2(500 CHAR), 
 "RELATION" NUMBER, 
 "CORPINSTANCE_ID" NUMBER(15,0) DEFAULT SYS_CONTEXT ('VPDSETM_EEO_CONTEXT','CIID') not null,
 "RECORD_LAST_MODIFIED" DATE, 
 "RECORD_ADDED" DATE, 
 "USER_NAME" VARCHAR2(20 CHAR), 
 "VERSIONCOLUMN" NUMBER(15,0) DEFAULT 0, 
 "USER_ID" NUMBER(38,0),
 "LOG_DATE_TIME" date not null,
 CONSTRAINT "FK_ADLOGDETTMP" FOREIGN KEY ("LOG_DATE_TIME","CORPINSTANCE_ID","AUDITLOG_KEY")
   REFERENCES "CISETM1"."ADLOG_TMP" ("LOG_DATE_TIME","CORPINSTANCE_ID","AUDITLOG_KEY" ) ON DELETE CASCADE ENABLE 
    )
 SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 15 MAXTRANS 255 
 COMPRESS FOR OLTP  LOGGING STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EEO_LARGE_DATA" 
    Partition by Reference (FK_ADLOGDETTMP) ;
复制

提前感谢
Venu

如果您尚未这样做，请查看Connor的分区指南:

https://asktom.oracle.com/partitioning-for-developers.htm

这应该有助于你决定去做什么。最终总会有取舍。你需要决定哪些收益值得付出代价。这是您需要牢记业务目标的决定。

1) 我不完全清楚您的数字显示了什么。到底插入和选择是什么？

但是我会坚持KISS原则，并进行单层分区。除非您发现复合分区的强大性能或可管理性优势。

2) 在没有看到您使用的确切查询的情况下很难确定。但是您已经提到了VPD，因此您需要确保为将添加的谓词创建的任何索引。

3) 我们需要查看这些查询的执行计划。如果您说要花7分钟才能返回1,500行...有些不对劲。

如果不确定如何获得计划，请阅读:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan