Partitioning on W_GL_OTHER_F - 1

In our database, W_GL_OTHER_F table has more then 70 million records, and reports have already started hitting bad performance. Considering we will go live on more countries in our global OBIEE project, the number of records is getting higher day by day. According to Oracle Business Intelligence Applications Version 7.9.6 Performance Recommendations (BI_Apps796_Perf_Tech_Note_V5), we have decided to go on partitiopning over W_GL_OTHER_F table. I will try to write the all steps that we followed to help other people who are trying to do the same, as we had many questions before starting this process, and we could not find quick answers by just googling.

We are implementing the range partitioning with ACCT_PERIOD_END_DT_WID and also partitioning the data by quarterly. The first problem that we hit was how to partition the data by quarterly while the ACCT_PERIOD_END_DT_WID is a number field having populated by concatenating 3 values

M_CAL_CAL_WID || ACCT_PERIOD_END_DT|| IIF(ADJUSTMENT_FLG='Y', '999', '000')

M_CAL_CAL_WID is an ID of calendar being used, in our case it is 1003. And the last 3 digits has not much impact on partitioning. Like if accounting period end is 20101031 (31 Oct 2010) and if it is adjustment record then ACCT_PERIOD_END_DT_WID would be 100320101031999.  Therefore we came up with the solution that we will be loading this data into 4th Quarter of 2010, which can be defined as "partition PART_2010Q4 values less than (100320101232000)", see the below create table script for more detail, but itis simply that we do not need to comply with date formatting, it is a number in this column anyway, so we can play with it as we want.

Steps to be done in Datawarehouse Database
Rename the original table

rename W_GL_OTHER_F to W_GL_OTHER_F_ORIG;
  

Before running the create table ddl, execute the below query in database and change the partition range value accordingly.

SELECT row_wid
FROM w_mcal_cal_d
WHERE mcal_cal_name = 'YOUR CALENDAR'

Please use the value from the above query while defining the partition range. As in our environment the value is 1003 we have used 1003**** and replace this with the query result.

Create the partitioned table, using range partitioning by quarter;

Create table W_GL_OTHER_F partition by range (ACCT_PERIOD_END_DT_WID)
(
partition PART_MIN values less than (100320061232000),
partition PART_2007Q1 values less than (100320070332000),
partition PART_2007Q2 values less than (100320070632000),
partition PART_2007Q3 values less than (100320070932000),
partition PART_2007Q4 values less than (100320071232000),
partition PART_2008Q1 values less than (100320080332000),
partition PART_2008Q2 values less than (100320080632000),
partition PART_2008Q3 values less than (100320080932000),
partition PART_2008Q4 values less than (100320081232000),
partition PART_2009Q1 values less than (100320090332000),
partition PART_2009Q2 values less than (100320090632000),
partition PART_2009Q3 values less than (100320090932000),
partition PART_2009Q4 values less than (100320091232000),
partition PART_2010Q1 values less than (100320100332000),
partition PART_2010Q2 values less than (100320100632000),
partition PART_2010Q3 values less than (100320100932000),
partition PART_2010Q4 values less than (100320101232000),
partition PART_2011Q1 values less than (100320110332000),
partition PART_2011Q2 values less than (100320110632000),
partition PART_2011Q3 values less than (100320110932000),
partition PART_2011Q4 values less than (100320111232000),
partition PART_2012Q1 values less than (100320120332000),
partition PART_2012Q2 values less than (100320120632000),
partition PART_2012Q3 values less than (100320120932000),
partition PART_2012Q4 values less than (100320121232000),
partition PART_MAX values less than (maxvalue)
)
TABLESPACE XXXDW_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
nologging parallel
enable row movement
as (select * from W_GL_OTHER_F_ORIG);
/

Rename indexes on renamed table

ALTER INDEX W_GL_OTHER_F_71 rename to W_GL_OTHER_F_71_ORIG;
ALTER INDEX W_GL_OTHER_F_72 rename to W_GL_OTHER_F_72_ORIG;
ALTER INDEX W_GL_OTHER_F_73 rename to W_GL_OTHER_F_73_ORIG;
ALTER INDEX W_GL_OTHER_F_F59 rename to W_GL_OTHER_F_F59_ORIG;
ALTER INDEX W_GL_OTHER_F_F25 rename to W_GL_OTHER_F_F25_ORIG;
ALTER INDEX W_GL_OTHER_F_F36 rename to W_GL_OTHER_F_F36_ORIG;
ALTER INDEX W_GL_OTHER_F_F40 rename to W_GL_OTHER_F_F40_ORIG;
ALTER INDEX W_GL_OTHER_F_F5 rename to W_GL_OTHER_F_F5_ORIG;
ALTER INDEX W_GL_OTHER_F_M2 rename to W_GL_OTHER_F_M2_ORIG;
ALTER INDEX W_GL_OTHER_F_C70 rename to W_GL_OTHER_F_C70_ORIG;
ALTER INDEX W_GL_OTHER_F_F13 rename to W_GL_OTHER_F_F13_ORIG;
ALTER INDEX W_GL_OTHER_F_F11 rename to W_GL_OTHER_F_F11_ORIG;
ALTER INDEX W_GL_OTHER_F_F60 rename to W_GL_OTHER_F_F60_ORIG;
ALTER INDEX W_GL_OTHER_F_F61 rename to W_GL_OTHER_F_F61_ORIG;
ALTER INDEX W_GL_OTHER_F_U1 rename to W_GL_OTHER_F_U1_ORIG;

Create Global and Local indexes. Run the below statements to create indexes

CREATE BITMAP INDEX W_GL_OTHER_F_C70 ON W_GL_OTHER_F (LOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_72 ON W_GL_OTHER_F (X_REFERENCE_8 ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE UNIQUE INDEX W_GL_OTHER_F_U1 ON W_GL_OTHER_F (INTEGRATION_ID ASC,DATASOURCE_NUM_ID ASC) tablespace XXXDW_DATA GLOBAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_73 ON W_GL_OTHER_F (DOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_71 ON W_GL_OTHER_F (DOC_STATUS_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F11 ON W_GL_OTHER_F (COMPANY_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F13 ON W_GL_OTHER_F (COST_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F25 ON W_GL_OTHER_F (GL_ACCOUNT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F36 ON W_GL_OTHER_F (POSTED_ON_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F40 ON W_GL_OTHER_F (PROFIT_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F5 ON W_GL_OTHER_F (BUSN_AREA_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE INDEX W_GL_OTHER_F_F59 ON W_GL_OTHER_F (ACCT_PERIOD_END_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F60 ON W_GL_OTHER_F (LEDGER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F61 ON W_GL_OTHER_F (MCAL_CAL_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_M2 ON W_GL_OTHER_F (DELETE_FLG ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;

And finally compute statistics on partitioned table

BEGIN
dbms_stats.Gather_table_stats(
NULL,
tabname => 'W_GL_OTHER_F',
CASCADE => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;

Database part is done, we need to do DAC changes that I will try to post later. Actually for DAC part, the document from Oracle is more than enough, but I will repeat some steps to finalize the document. My team from offshore has done good job on delivering this solution, a big thanks to them.