partitioning EXAMPLE - HASH

partitioning EXAMPLE - HASH

CREATE TABLE SF_COURSE_ENROLLMENT ( 
  SCH_END_YR                VARCHAR2 (4)  NOT NULL, 
  CRS_ENROLL_REC_ID         NUMBER        NOT NULL, 
  STD_STUDENT_PRIMARY_KEY   NUMBER, 
  SCH_LOCATION_PRIMARY_KEY  NUMBER, 
  TCH_ID                    NUMBER, 
  YR_ID                     NUMBER, 
  TRC_ID                    NUMBER, 
  OCC_ID                    NUMBER, 
  CRS_ID                    NUMBER, 
  CRS_NUM_UNQ7              VARCHAR2 (2), 
  ETHNICITY_ID              NUMBER, 
  GENDER_ID                 NUMBER, 
  CLASS_OF_ID               NUMBER, 
  HOME_LANG_ID              NUMBER, 
  LANG_CLS_ID               NUMBER, 
  GRADE_ID                  NUMBER, 
  HOMELESS_INDICATOR_ID     NUMBER, 
  MIGRANT_INDICATOR_ID      NUMBER, 
  POVERTY_INDICATOR_ID      NUMBER, 
  TCH_LAUSD_YEARS_QTY       NUMBER, 
  TCH_STATUS_ID             NUMBER, 
  SCHOOL_TYPE_ID            NUMBER, 
  CLASS_PERIOD              VARCHAR2 (1), 
  CRS_LANG_CODE             VARCHAR2 (10), 
  STUDENT_HIST_REC_ID       NUMBER, 
  TCH_HIST_REC_ID           NUMBER, 
  LOCATION_HIST_REC_ID      NUMBER, 
  SEC_RPT_OCC_ID            NUMBER, 
  ELEM_RPT_OCC_ID           NUMBER, 
  ANNUAL_RPT_INDICATOR_ID   NUMBER, 
  SOURCE_SYSTEM_CODE        VARCHAR2 (1), 
  CREATE_DATE               DATE, 
  CREATED_BY                VARCHAR2 (30), 
  LAST_UPDATE_DATE          DATE, 
  LAST_UPDATED_BY           VARCHAR2 (30))
partition by hash (STD_STUDENT_PRIMARY_KEY, OCC_ID) (
   partition p_a tablespace data_med03,
   partition p_b tablespace data_med04,
   partition p_c tablespace data_med03,
   partition p_d tablespace data_med04)
NOLOGGING 
PCTFREE 0;

-- to create constraint
ALTER TABLE SF_COURSE_ENROLLMENT drop CONSTRAINT SF_COURSE_ENROLLMENT_UK;

PROMPT Creating Unique Key on 'SF_COURSE_ENROLLMENT'
ALTER TABLE SF_COURSE_ENROLLMENT
 ADD (CONSTRAINT SF_COURSE_ENROLLMENT_UK UNIQUE 
  (SCH_END_YR
  ,YR_ID
  ,OCC_ID
  ,SCH_LOCATION_PRIMARY_KEY
  ,STD_STUDENT_PRIMARY_KEY
  ,CRS_ID
  ,TRC_ID
  ,TCH_ID
  ,CRS_NUM_UNQ7
  ,CLASS_PERIOD
  ,ELEM_RPT_OCC_ID
  ,SEC_RPT_OCC_ID)
    USING INDEX LOCAL (
       partition p_a tablespace indx_med01,
       partition p_b tablespace indx_med01,
       partition p_c tablespace indx_med01,
       partition p_d tablespace indx_med01)
    NOLOGGING);

-- to enable constraint (if it's ever disabled)
ALTER TABLE SF_COURSE_ENROLLMENT
enable
  CONSTRAINT SF_COURSE_ENROLLMENT_UK
    USING INDEX LOCAL (
       partition p_a tablespace indx_med01,
       partition p_b tablespace indx_med01,
       partition p_c tablespace indx_med01,
       partition p_d tablespace indx_med01)
    NOLOGGING;

.