Hi
Database Version :
DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
OS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user license
APP : SAP - ERP
I have to RANGE partition on UPDATED_ON or PROFILE either one table which is having below structure :
Name Null? Type
-------------------- -------- --------------
------------------
MANDT NOT NULL VARCHAR2(9)
MR_ID NOT NULL VARCHAR2(60)
PROFILE NOT NULL VARCHAR2(54)
REGISTER_ID NOT NULL VARCHAR2(30)
INTERVAL_DATE NOT NULL VARCHAR2(24)
AGGR_CONSUMPTION NOT NULL NUMBER(21,6)
MDM_VERS_NO NOT NULL VARCHAR2(9)
MDP_UPDATE_DATE NOT NULL VARCHAR2(24)
MDP_UPDATE_TIME NOT NULL VARCHAR2(18)
NMI_CONFIG NOT NULL VARCHAR2(120)
NMI_CONFIG_FLAG NOT NULL VARCHAR2(3)
MDM_DATA_STRM_ID NOT NULL VARCHAR2(6)
NSRD NOT NULL VARCHAR2(24)
REASON_CODE NOT NULL VARCHAR2(9)
QUALITY_FLAG NOT NULL VARCHAR2(3)
METHOD_FLAG NOT NULL VARCHAR2(6)
MSATS_UPDATE_DAT NOT NULL VARCHAR2(24)
MSATS_UPDATE_TIM NOT NULL VARCHAR2(18)
READ_STATUS NOT NULL VARCHAR2(3)
LEGACY_FLAG NOT NULL VARCHAR2(3)
CREATED_ON NOT NULL NUMBER(21,7)
CREATED_BY NOT NULL VARCHAR2(36)
UPDATED_ON NOT NULL NUMBER(21,7)
UPDATED_BY NOT NULL VARCHAR2(36)
CVERSNO NOT NULL VARCHAR2(18)
OLDER_MD_FLAG NOT NULL VARCHAR2(3)
TRANSACTION_ID NOT NULL VARCHAR2(108)
As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr .
Column PROFILE
Its is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible. However, So could you please suggest how to RANGE partition on PROFILE ?
Column CREATED_ON :
It is of NUMBER with decimal. Could you please guide me?
Please let me know if you need more information ?
Cheers
Sameer