The following blog post details how Fact Partitioning can be implemented. An ODI procedure is provided in BI Apps repository that generates a template .SQL file for implementing partitions on a particular table - edit this .Currently the number of sub-partitions are set as 64 when the table was first created and I may have a requirement in near future to increase the number to a higher value (probably to 128). The number of partitions you specify determines how many hash values there are. Since this apply to forward partions could happen to have same col value with different hash results old subpartition vs New one?
In-Database Archiving stores operational and archival data in the same database so that the data is still online and available and the cost is only slightly higher than storing this information on tape.– Data is partitioned to distinguish active data from those in archived state. are compressed and located on low cost storage tiers. are identified as read-only so that subsequent backups are not required after the initial database backup.create table emp (id number, name varchar2(30), retired char(1)) row archival partition by list(ora_archive_state) (partition p_active values(0) tablespace active_tbs, partition p_archived values (default) tablespace archived_tbs compress); select id, name, retired, ora_archive_state from emp partition (p_active); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 1 EMP1 N 0 2 EMP2 N 0 3 EMP3 N 0 4 EMP4 N 0 5 EMP5 N 0 select id, name, retired, ora_archive_state from emp partition (p_archived); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 6 EMP6 Y 1 7 EMP7 Y 1 8 EMP8 Y 1 9 EMP9 Y 1 10 EMP10 Y 1 connect target sys/[email protected] configure exclude for tablespace pdb1:archived_tbs; Tablespace PDB1: ARCHIVED_TBS will be excluded from future whole database backups new RMAN configuration parameters are successfully stored I hope information in this post was useful. Once the tables are loaded, determine an appropriate partition strategy based on your data profile, then implement the partitions per this document.Performing an initial load on partitioned tables can take longer than performing an initial load on unpartitioned tables - the Oracle database has to spend extra cycles creating multiple new partitions and determining which partition a record should be assigned to.