Thanks Tom.
Sorry for the improper aligment in explain plan.
I tried to paste the content in smaller font size, still it appears larger on the screen. I could not paste the explain plain again.
The given query before should be hitting only 15 partitions.
Here is the monthly_rsrvs table DDL and indexes on it.
Please provide your suggestions on how to perform partition pruning.
Is there any better way to rewrite this query which has count(*) ?
== here is the date range to scan partitions. so there are 15 partitions (monthly) ==
1* select * from ETL.GLDATE
SQL> /
GL_EFFECT
---------
01-JAN-08
01-FEB-08
01-MAR-08
01-APR-08
01-MAY-08
01-JUN-08
01-JUL-08
01-AUG-08
01-SEP-08
01-OCT-08
01-NOV-08
01-DEC-08
01-JAN-09
01-FEB-09
01-MAR-09
15 rows selected.
SQL> l
1 select a.index_name, b.index_type, a.column_name from user_ind_columns a, user_indexes b
2 where a.index_name=b.index_name
3 and a.table_name=b.table_name
4* and a.table_name='MNTHLY_RSRVS'
SQL> /
INDEX_NAME INDEX_TYPE COLUMN_NAME
------------------------------ --------------------------- ------------------------------
I_MNTHLY_RSRVS_01 BITMAP REIN_DIRECTION_CD
I_MNTHLY_RSRVS_02 BITMAP REIN_PROC_CTGRY_CD
I_MNTHLY_RSRVS_03 BITMAP DOCUMENT_TYPE_CD
I_MNTHLY_RSRVS_04 BITMAP DOCUMENT_STATUS_CD
I_MNTHLY_RSRVS_05 NORMAL TRANSACTION_TYP_CD
I_MNTHLY_RSRVS_05 NORMAL TRANS_SUB_TYP_CD
6 rows selected.
CREATE TABLE ETL.MNTHLY_RSRVS
(
GL_EFFECTIVE_DT DATE NOT NULL,
ACCTNG_PERD_BEG_DT DATE NOT NULL,
ACCTNG_PERD_END_DT DATE NOT NULL,
DOCUMENT_STATUS_CD VARCHAR2(1) NULL,
DOCUMENT_TYPE_CD VARCHAR2(3) NULL,
ORPH_RJCT_IND VARCHAR2(3) NULL,
RNDRD_UNRD_IND VARCHAR2(1) NULL,
PREMIUM_LOSS_CD VARCHAR2(1) NULL,
NM_CONTRACT_NO NUMBER(10) NULL,
ORPHAN_STATUS_CD VARCHAR2(1) NULL,
INCOMPLT_TREATY_IN VARCHAR2(1) NULL,
MACH_NM_ORIG_CD VARCHAR2(1) NOT NULL,
MIS013_COUNTRY_CD NUMBER(5) NULL,
KIND_CD VARCHAR2(2) NOT NULL,
HOLD_IN VARCHAR2(1) NULL,
REIN_PROC_CTGRY_CD VARCHAR2(3) NOT NULL,
REIN_DIRECTION_CD VARCHAR2(3) NOT NULL,
REIN_RNDRG_FREQ_CD NUMBER(22) NULL,
REIN_NO VARCHAR2(20) NULL,
REIN_UW_YR_NO NUMBER(5) NULL,
REIN_EFCTV_DT DATE NULL,
REIN_KEY_CD VARCHAR2(6) NULL,
FAC_CERTIFICATE_NO VARCHAR2(20) NULL,
XTRNL_REIN_REF_NO VARCHAR2(50) NULL,
CAPTIVE_IN VARCHAR2(1) NOT NULL,
ASCO_MIS012_CUR_CD NUMBER(22) NOT NULL,
NM_ADJUSTMENT_CD NUMBER(5) NULL,
SOURCE_SYSTEM_ID VARCHAR2(25) NULL,
MIS780_ASCO_CODE VARCHAR2(2) NOT NULL,
POLICY_XPIRTN_DT DATE NULL,
INSURED_NM VARCHAR2(90) NULL,
POLICY_NO VARCHAR2(10) NOT NULL,
POLICY_EFCTV_DT DATE NOT NULL,
MIS780_COMP_CODE NUMBER(22) NULL,
MACH_NM VARCHAR2(2) NULL,
REIN_NO_LAYER_NO VARCHAR2(2) NULL,
REIN_NO_SECTION_NO VARCHAR2(2) NULL,
REIN_NO_FOREIGN_DOM_CD VARCHAR2(1) NULL,
RELTNSHP_TYP_CD VARCHAR2(3) NULL,
SOURCE_REIN_NO VARCHAR2(20) NULL,
SRC_REIN_EFCTV_DT DATE NULL,
SRC_REIN_UW_YR_NO NUMBER(5) NULL,
SRC_REIN_KEY_CD VARCHAR2(6) NULL,
SRC_REIN_NO_LAYER_NO VARCHAR2(2) NULL,
SRC_REIN_NO_SECTION_NO VARCHAR2(2) NULL,
SRC_REIN_NO_FOREIGN_DOM_CD VARCHAR2(1) NULL,
PUC_DSP_DIV NUMBER(5) NOT NULL,
PUC_DSP_PUC NUMBER(5) NULL,
PUC_DSP_SEC NUMBER(5) NULL,
LOSS_DT DATE NULL,
DEP_DDSP_DEPT NUMBER(5) NULL,
TERM_CONDITION_CD VARCHAR2(3) NULL,
MATRIX_CD VARCHAR2(3) NULL,
MIS404_TAX_ST_CD NUMBER(22) NULL,
CREDITED_BRANCH_NO NUMBER(22) NULL,
WORKING_BRANCH_NO NUMBER(22) NULL,
MIS020_CATAS_CD NUMBER(5) NULL,
MIS020_CATAS_YR_NO NUMBER(5) NULL,
CLAIM_BRANCH_NO NUMBER(5) NULL,
CLAIM_CASE_NO NUMBER(10) NULL,
KEY_BRANCH_NO VARCHAR2(3) NULL,
KEY_CASE_NO VARCHAR2(6) NULL,
CLA_ID VARCHAR2(22) NULL,
MIS028_MAJ_CLASS NUMBER(5) NOT NULL,
MIS028_STMT_LINE VARCHAR2(3) NULL,
TRANSACTION_TYP_CD NUMBER(5) NOT NULL,
TRANS_SUB_TYP_CD NUMBER(5) NOT NULL,
GEN_LEDGER_ACCT_NO NUMBER(10) NOT NULL,
REINSURER_NO VARCHAR2(6) NOT NULL,
REINSURER_BRNCH_CD VARCHAR2(3) NOT NULL,
INTERMEDIARY_NO VARCHAR2(6) NULL,
INTMDRY_BRNCH_CD VARCHAR2(3) NULL,
PARTICIPANT_TYP_CD VARCHAR2(3) NULL,
BALSHT_GL_ACCT_NO NUMBER(22) NOT NULL,
MACH_NM_ORIG_TS TIMESTAMP(6) NULL,
COUNT_KEYS NUMBER(22) NULL,
BEG_ASCO_FINANCIAL_AM NUMBER(20,4) NULL,
END_ASCO_FINANCIAL_AM NUMBER(20,4) NULL,
PROP_GL_DT DATE NULL,
REAL_IN VARCHAR2(2) NULL,
LOAD_DATE DATE NULL
)
TABLESPACE WIP_DATA
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT)
PARALLEL(DEGREE 4 INSTANCES 1)
NOCACHE
PARTITION BY RANGE(GL_EFFECTIVE_DT)
SUBPARTITION BY LIST(SOURCE_SYSTEM_ID)
SUBPARTITION TEMPLATE
(SUBPARTITION CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA,
SUBPARTITION CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA,
SUBPARTITION CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA,
SUBPARTITION CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA,
SUBPARTITION OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA)
(PARTITION MR_MINVAL VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MINVAL
(SUBPARTITION MR_MINVAL_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MINVAL,
SUBPARTITION MR_MINVAL_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MINVAL,
SUBPARTITION MR_MINVAL_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MINVAL,
SUBPARTITION MR_MINVAL_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MINVAL,
SUBPARTITION MR_MINVAL_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MINVAL),
PARTITION MR_200701 VALUES LESS THAN (TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200701
(SUBPARTITION MR_200701_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200701,
SUBPARTITION MR_200701_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200701,
SUBPARTITION MR_200701_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200701,
SUBPARTITION MR_200701_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200701,
SUBPARTITION MR_200701_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200701),
PARTITION MR_200702 VALUES LESS THAN (TO_DATE(' 2007-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200702
(SUBPARTITION MR_200702_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200702,
SUBPARTITION MR_200702_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200702,
SUBPARTITION MR_200702_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200702,
SUBPARTITION MR_200702_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200702,
SUBPARTITION MR_200702_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200702),
PARTITION MR_200703 VALUES LESS THAN (TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200703
(SUBPARTITION MR_200703_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200703,
SUBPARTITION MR_200703_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200703,
SUBPARTITION MR_200703_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200703,
SUBPARTITION MR_200703_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200703,
SUBPARTITION MR_200703_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200703),
PARTITION MR_200704 VALUES LESS THAN (TO_DATE(' 2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200704
(SUBPARTITION MR_200704_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200704,
SUBPARTITION MR_200704_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200704,
SUBPARTITION MR_200704_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200704,
SUBPARTITION MR_200704_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200704,
SUBPARTITION MR_200704_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200704),
PARTITION MR_200705 VALUES LESS THAN (TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200705
(SUBPARTITION MR_200705_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200705,
SUBPARTITION MR_200705_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200705,
SUBPARTITION MR_200705_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200705,
SUBPARTITION MR_200705_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200705,
SUBPARTITION MR_200705_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200705),
PARTITION MR_200706 VALUES LESS THAN (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200706
(SUBPARTITION MR_200706_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200706,
SUBPARTITION MR_200706_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200706,
SUBPARTITION MR_200706_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200706,
SUBPARTITION MR_200706_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200706,
SUBPARTITION MR_200706_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200706),
PARTITION MR_200707 VALUES LESS THAN (TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200707
(SUBPARTITION MR_200707_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200707,
SUBPARTITION MR_200707_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200707,
SUBPARTITION MR_200707_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200707,
SUBPARTITION MR_200707_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200707,
SUBPARTITION MR_200707_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200707),
PARTITION MR_200708 VALUES LESS THAN (TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200708
(SUBPARTITION MR_200708_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200708,
SUBPARTITION MR_200708_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200708,
SUBPARTITION MR_200708_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200708,
SUBPARTITION MR_200708_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200708,
SUBPARTITION MR_200708_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200708),
PARTITION MR_200709 VALUES LESS THAN (TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200709
(SUBPARTITION MR_200709_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200709,
SUBPARTITION MR_200709_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200709,
SUBPARTITION MR_200709_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200709,
SUBPARTITION MR_200709_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200709,
SUBPARTITION MR_200709_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200709),
PARTITION MR_200710 VALUES LESS THAN (TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200710
(SUBPARTITION MR_200710_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200710,
SUBPARTITION MR_200710_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200710,
SUBPARTITION MR_200710_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200710,
SUBPARTITION MR_200710_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200710,
SUBPARTITION MR_200710_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200710),
PARTITION MR_200711 VALUES LESS THAN (TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200711
(SUBPARTITION MR_200711_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200711,
SUBPARTITION MR_200711_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200711,
SUBPARTITION MR_200711_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200711,
SUBPARTITION MR_200711_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200711,
SUBPARTITION MR_200711_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200711),
PARTITION MR_200712 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200712
(SUBPARTITION MR_200712_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200712,
SUBPARTITION MR_200712_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200712,
SUBPARTITION MR_200712_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200712,
SUBPARTITION MR_200712_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200712,
SUBPARTITION MR_200712_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200712),
PARTITION MR_200801 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200801
(SUBPARTITION MR_200801_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200801,
SUBPARTITION MR_200801_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200801,
SUBPARTITION MR_200801_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200801,
SUBPARTITION MR_200801_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200801,
SUBPARTITION MR_200801_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200801),
PARTITION MR_200802 VALUES LESS THAN (TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200802
(SUBPARTITION MR_200802_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200802,
SUBPARTITION MR_200802_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200802,
SUBPARTITION MR_200802_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200802,
SUBPARTITION MR_200802_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200802,
SUBPARTITION MR_200802_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200802),
PARTITION MR_200803 VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200803
(SUBPARTITION MR_200803_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200803,
SUBPARTITION MR_200803_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200803,
SUBPARTITION MR_200803_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200803,
SUBPARTITION MR_200803_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200803,
SUBPARTITION MR_200803_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200803),
PARTITION MR_200804 VALUES LESS THAN (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200804
(SUBPARTITION MR_200804_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200804,
SUBPARTITION MR_200804_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200804,
SUBPARTITION MR_200804_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200804,
SUBPARTITION MR_200804_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200804,
SUBPARTITION MR_200804_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200804),
PARTITION MR_200805 VALUES LESS THAN (TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200805
(SUBPARTITION MR_200805_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200805,
SUBPARTITION MR_200805_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200805,
SUBPARTITION MR_200805_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200805,
SUBPARTITION MR_200805_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200805,
SUBPARTITION MR_200805_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200805),
PARTITION MR_200806 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG200806
(SUBPARTITION MR_200806_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200806,
SUBPARTITION MR_200806_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200806,
SUBPARTITION MR_200806_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200806,
SUBPARTITION MR_200806_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200806,
SUBPARTITION MR_200806_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200806),
PARTITION MR_200807 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200807_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200807_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200807_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200807_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200807_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200808 VALUES LESS THAN (TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200808_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200808_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200808_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200808_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200808_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200809 VALUES LESS THAN (TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200809_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200809_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200809_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200809_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200809_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200810 VALUES LESS THAN (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200810_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200810_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200810_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200810_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200810_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200811 VALUES LESS THAN (TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200811_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200811_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200811_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200811_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200811_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200812 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200812_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200812_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200812_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200812_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_200812_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
PARTITION MR_200901 VALUES LESS THAN (TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_200901_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200901_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200901_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200901_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200901_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
PARTITION MR_200902 VALUES LESS THAN (TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PART2009Q1
(SUBPARTITION MR_200902_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200902_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200902_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200902_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200902_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
PARTITION MR_200903 VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PART2009Q1
(SUBPARTITION MR_200903_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200903_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200903_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200903_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
SUBPARTITION MR_200903_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
PARTITION MR_200904 VALUES LESS THAN (TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PART2009Q2
(SUBPARTITION MR_200904_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200904_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200904_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200904_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200904_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
PARTITION MR_200905 VALUES LESS THAN (TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PART2009Q2
(SUBPARTITION MR_200905_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200905_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200905_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200905_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200905_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
PARTITION MR_200906 VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE WIP_DATA_PART2009Q2
(SUBPARTITION MR_200906_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200906_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200906_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200906_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
SUBPARTITION MR_200906_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
PARTITION MR_MAXVAL VALUES LESS THAN (MAXVALUE)
TABLESPACE WIP_DATA_PARTG_MAXVAL
(SUBPARTITION MR_MAXVAL_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_MAXVAL_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_MAXVAL_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_MAXVAL_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
SUBPARTITION MR_MAXVAL_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL))