Partition And Compression Implementation In CCB Environment For The Table F1_ATTACHMENT (Doc ID 2793417.1)

Last updated on JUNE 07, 2024

Applies to:

Goal

On :CCB 2.7.0.3.0 version, IP - Installation Upgrade Proc

Query 1:
The Table F1_ATTACHMENT will be partitioned based on below two column values,
PARTITION BY RANGE(CRE_DTTM) SUBPARTITION BY RANGE(ATTACHMENT_ID)
Please confirm if we can go ahead with these columns for partition
Script to be referred: CTAS.txt

Query 2:
The ROW chunk value is 8192 in DDL of DB table F1_ATTACHMENT, Is it required
to specify the same value in below commands which is as part of CTAS script
LOB (ATTACHMENT_DATA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW CHUNK CACHE
COMPRESS)
LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW CHUNK CACHE
COMPRESS)
Script to be referred: CTAS.txt

Query 3:
The indexes needs to be paritioned based on column values in either of these
partition types (Range, Hash, list and composite). Please suggest the
appropriate partition type to be applied for each index.
1.CREATE UNIQUE INDEX F1T819P0 ON CISADM.F1_ATTACHMENT(ATTACHMENT_ID)
TABLESPACE D2_D1T304_IND
GLOBAL PARTITION BY RANGE (ATTACHMENT_ID)
2.CREATE UNIQUE INDEX F1T819S1 ON CISADM.F1_ATTACHMENT(MAINT_OBJ_CD,
ATTACHMENT_ID)
GLOBAL PARTITION BY HASH (MAINT_OBJ_CD, ATTACHMENT_ID)
3.CREATE UNIQUE INDEX F1T819S2 ON
CISADM.F1_ATTACHMENT(ATTACHMENT_EXT_ID,ATTACHMENT_ID)
GLOBAL PARTITION BY HASH (ATTACHMENT_EXT_ID,ATTACHMENT_ID)
Script to be referred: index_creation.txt

Query 4:
MAINT_OBJ_CD and ATTACHMENT_EXT_ID columns have null value throughout all the
records. They are the columns which the indexes point to. The indexes are
being partitioned, so please comment on whether there will be any issue in
partitioning the indexes that point to such columns.
Script to be referred: index_creation.txt

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.