Thursday, February 8, 2018

What is a LIST Partition in Oracle








/* ==========================================================================*/

DROP TABLE EMP_LIST;

CREATE TABLE EMP_LIST
  (
    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 LIST (DEPTNO)
      (
       PARTITION P1 VALUES ('10'),
       PARTITION P2 VALUES ('20'),
       PARTITION P3 VALUES ('30'),
       PARTITION P4 VALUES ('40')
     ) ENABLE ROW MOVEMENT;
  
INSERT INTO EMP_LIST SELECT * FROM EMP;  
COMMIT;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_LIST');
END;
 /
select * from EMP_LIST where deptno=20;

select * from emp where deptno=10;

No comments:

Post a Comment