sub partitioning in oracle or composite partitioning in Oracle RANGE-LI...

******************************************************************************/
drop table EMP_COMP_RH;
 CREATE TABLE EMP_COMP_RH
 (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
 PARTITION BY RANGE (HIREDATE)
 SUBPARTITION BY HASH (EMPNO) SUBPARTITIONS 4
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy')),
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy')),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy')),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
)ENABLE ROW MOVEMENT;;

INSERT INTO EMP_COMP_RH select * from EMP;
commit;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RH');
END;
 /


 select * from EMP_COMP_RH;


drop table EMP_COMP_RL;
 CREATE TABLE EMP_COMP_RL
 (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
 PARTITION BY RANGE (HIREDATE)
 SUBPARTITION BY LIST(DEPTNO)
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy'))
      (
       SUBPARTITION SP1 VALUES ('10'),
       SUBPARTITION SP2 VALUES ('20'),
       SUBPARTITION SP3 VALUES ('30'),
       SUBPARTITION SP4 VALUES ('40')
     ) ,
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy'))
(
       SUBPARTITION SP11 VALUES ('10'),
       SUBPARTITION SP21 VALUES ('20'),
       SUBPARTITION SP31 VALUES ('30'),
       SUBPARTITION SP41 VALUES ('40')
     ),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy'))
(
       SUBPARTITION SP12 VALUES ('10'),
       SUBPARTITION SP22 VALUES ('20'),
       SUBPARTITION SP32 VALUES ('30'),
       SUBPARTITION SP42 VALUES ('40')
     ),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
(
       SUBPARTITION SP13 VALUES ('10'),
       SUBPARTITION SP23 VALUES ('20'),
       SUBPARTITION SP33 VALUES ('30'),
       SUBPARTITION SP43 VALUES ('40')
     )
)ENABLE ROW MOVEMENT;

INSERT INTO EMP_COMP_RL select * from EMP;
commit;

select * from EMP_COMP_RL;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RL');
END;
 /