Hi Team,
Please have a look at table structure shared via LiveSQL Link -
https://livesql.oracle.com/apex/livesql/s/ee1slk0wo53esxy30e4l0ac2x table is used in application using stmt_end_date -
1. Since stmt_end_date is a part of primary key, is it recommended to as a PARTITION key?
2. Could you please share your views which partition methos combination can be used for this table.
3. Since there is not direct way to partition table in 11g, If we go for upgrading Oracle to 12c will ALTER COMMAND MODIFY will help to parition exiting non-partition table?
Copying table structure if incase Live links is not accessible -
CREATE TABLE TB_DEMOGRAPHIC
(CARD_NO VARCHAR2(25 CHAR),
CONSTRAINT PK_TB_DEMOGRAPHIC PRIMARY KEY (CARD_NO));
CREATE TABLE TB_STATEMENT
(CARD_NO VARCHAR2(25 CHAR),
STMT_BEGIN_DATE DATE,
ACC_NO VARCHAR2(25 CHAR),
PRIMARY_CARD_NO VARCHAR2(25 CHAR),
STATEMENT_TYPE CHAR(1 CHAR),
ACC_STATUS VARCHAR2(4 CHAR),
BLOCK_CD CHAR(1 CHAR),
BLOCK_REASON VARCHAR2(2 CHAR),
STMT_END_DATE DATE,
STMT_BEGIN_BAL NUMBER(13,2),
STMT_BEGIN_BAL_SIGN VARCHAR2(2 CHAR),
STMT_END_BAL NUMBER(13,2),
STMT_END_BAL_SIGN VARCHAR2(2 CHAR),
CREDIT_LIMIT NUMBER(18,2),
CASH_LIMIT NUMBER(18,2),
AVAIL_CREDIT_LIMIT NUMBER(18,2),
AVAIL_CREDIT_LIMIT_SIGN VARCHAR2(2 CHAR),
AVAIL_CASH_LIMIT NUMBER(18,2),
AVAIL_CASH_LIMIT_SIGN VARCHAR2(2 CHAR),
AMT_DUE_CYCLE1 NUMBER(18,2),
AMT_DUE_CYCLE2 NUMBER(18,2),
AMT_DUE_CYCLE3 NUMBER(18,2),
AMT_DUE_CYCLE4 NUMBER(18,2),
AMT_DUE_CYCLE5 NUMBER(18,2),
AMT_DUE_CYCLE6 NUMBER(18,2),
AMT_DUE_CYCLE7 NUMBER(18,2),
DUE_AMT NUMBER(18,2),
DUE_AMT_SIGN CHAR(1 CHAR),
NO_OF_DUE_DAYS NUMBER(3,0),
PAYMENT_DUE NUMBER(18,2),
PAYMENT_DUE_SIGN VARCHAR2(2 CHAR),
PAYMENT_DUE_DATE DATE,
PRINT_FLG CHAR(1 CHAR),
ONLINE_STMT_DISP_FLG CHAR(1 CHAR),
STMT_MSG_IND VARCHAR2(2 CHAR),
STMT_PAYMENT_RECD NUMBER(13,2),
STMT_PAYMENT_RECD_SIGN CHAR(1 CHAR),
NEW_TXN NUMBER(18,2),
NEW_TXN_SIGN VARCHAR2(2 CHAR),
STMT_GEN_FLG CHAR(1 CHAR),
UPLOAD_ID NUMBER(10,0),
REC_STATUS VARCHAR2(2 CHAR) DEFAULT 'AC',
CREATED_BY VARCHAR2(30 CHAR),
CREATED_ON DATE,
MODIFIED_BY VARCHAR2(30 CHAR),
MODIFIED_ON DATE,
FILEDATE DATE,
CYCLE_DAY VARCHAR2(2 CHAR),
SWISSGIRO VARCHAR2(75 CHAR),
SWEDEN_GIRO VARCHAR2(100 CHAR),
PAYEE_ACC_NO VARCHAR2(15 CHAR),
FINNISH_GIRO_REF_NO VARCHAR2(20 CHAR),
FINNISH_CHECK_DIGIT1 VARCHAR2(5 CHAR),
FINNISH_CHECK_DIGIT2 VARCHAR2(5 CHAR),
REWARD_FLAG CHAR(1 CHAR),
OPENING_BALANCE NUMBER(13,2),
POINTS_EARNED NUMBER(13,2),
POINTS_REDEEMED NUMBER(13,2),
POINTS_EXPIRED NUMBER(13,2),
TOT_AVAIL_POINTS NUMBER(13,2),
EXPIRY_DATE DATE,
ACH_PAYMENT_FLG VARCHAR2(5 CHAR),
DESPATCH_TYPE_FLAG VARCHAR2(2 CHAR),
NEXT_DUE_DATE DATE,
NEXT_STMT_DATE DATE,
CASH_ADVANCE_FEE NUMBER(18,2),
CASH_ADVANCE_PERCENTAGE NUMBER(4,2),
CASH_ADVANCE_ACTION VARCHAR2(1 CHAR),
LATE_CHARGE_FEE NUMBER(18,2),
LATE_CHARGE_PERCENTAGE NUMBER(4,2),
LATE_CHARGE_ACTION VARCHAR2(1 CHAR),
RETURN_CHEQUE_FEE NUMBER(18,2),
RETURN_CHEQUE_PERCENTAGE NUMBER(4,2),
RETURN_CHEQUE_ACTION VARCHAR2(1 CHAR),
FOREIGN_TXN_FEE NUMBER(18,2),
FOREIGN_TXN_PERCENTAGE NUMBER(4,2),
FOREIGN_TXN_ACTION VARCHAR2(1 CHAR),
ANNUAL_MEMBERSHIP_FEE NUMBER(18,2),
CONSTRAINT CHECK_C0023288 CHECK (CARD_NO IS NOT NULL) ENABLE,
CONSTRAINT CHECK_C0023289 CHECK (STMT_BEGIN_DATE IS NOT NULL) ENABLE,
CONSTRAINT CHECK_C0023290 CHECK (ACC_NO IS NOT NULL) ENABLE,
CONSTRAINT CHECK_C0023291 CHECK (PRIMARY_CARD_NO IS NOT NULL) ENABLE,
CONSTRAINT PK_TB_STATEMENT PRIMARY KEY (CARD_NO, STMT_BEGIN_DATE),
CONSTRAINT FK_TB_STATEMENT1 FOREIGN KEY (CARD_NO)
REFERENCES TB_DEMOGRAPHIC (CARD_NO) ENABLE
);
CREATE INDEX IDX_AP_CS_CD_NO ON TB_STATEMENT (CARD_NO, STMT_END_DATE);
CREATE INDEX IDX_AP_PRINT_FLG ON TB_STATEMENT (PRINT_FLG);