Hi Team,
We have non partitioned Audit tables with billion of rows and now we are trying to partition those tables and archive them periodically .
From your blog we understood that partition strategy should be purely based on data access and manageability.
Most of our tables are parent/child relationship in 11g database and so we have chosen composite Reference partitioning in POC.
Main partition is Range over (log_date_time) and sub partition Hash on Corpid (CIID Number).
We tested both Global Hash /Local Indexes on primary keys( CIID, ADLOGKEY) and ( log_date_time,CIID, ADLOGKEY)
With Global Hash on indexes (insert performed better with less Index contention) but select where bad for 3months range too with proper Indexing.
With Local Indexes we see insert taking 2-3mints more than global with more contention and select doing good.
We want an optimal solution for both OLTP and OLAP.
Most of our select queries will be based on logdatetime range between 1 to 18 months ,with VPD Context on Corpid (CIID) in every query.
Ex:
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) Single partition Range on table and global on index primary key ( CIID,ADLOGKEY)
2) Composite partition Range-Hash on (logdatetime/CIID ) and with local Indexes (logdatetime,CIID,ADLOGKEY)
does single/composite partition makes a difference or not ? please suggest.
metrics are similar in both scenarios
single partition - Inserts elapsed time Indexing(global)
6M 6:23:24
Selects elapsed time
400krows 4Months 2:23:24
Composite (Range/Hash) 6M 6:00 23 Indexing(local)
400krows 4Mon 1:50 23
Composite(Range/Hash) 6M 4:59:05 (Global Hash)
selects elapsed time
400krows 4Months Query takes long time. fetches only 1500rows in 7mints.
Questions :
1) which one we should go with single /composite partitioning based on insert & selects.
2) what type of indexing would be better in this scenario
3) Why selects not performing good with globalHash indexing.
Table Partitioning:
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) ;
Thanks in Advance
Venu