oracle——SQL复习08

select * from emp e order by deptno ;


/**范围   分区*/
drop table  emp_part01; 
CREATE TABLE emp_part01
   (  "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(sal)  
    (  
      partition bujige values less than(2000), --不及格  
      partition jige values less than(3200), --及格  
      partition youxiu values less than(maxvalue) --优秀  
    )  ;
    
    --
    insert into emp_part01
    select * from emp e   ;
    
    commit;  
     
    select * from emp_part01 partition(bujige); 
    select * from emp_part01 partition(jige); 
    select * from emp_part01 partition(youxiu);
    
    
/**列表   分区*/

CREATE TABLE emp_part02
   (  "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 d10 values('10'),  
      partition d20 values('20'),  
      partition d30 values('30')  
    )  
    
    
    
    --
    insert into emp_part02
    select * from emp e   ;
    
    commit;  
    
    select * from emp_part02 partition(d10); 
    select * from emp_part02 partition(d20); 
    select * from emp_part02 partition(d30); 
    select * from emp_part02 ; 
    
    
    /**散列  分区*/
    
CREATE TABLE emp_part03
   (  "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 hash(EMPNO)  
    (  
      partition p1,  
      partition p2,  
      partition p3  
    );  
    
    
    insert into emp_part03
    select * from emp e   ;
    
    commit;  
    
    select * from emp_part03 partition(p1); 
    select * from emp_part03 partition(p2); 
    select * from emp_part03 partition(p3); 
    select * from emp_part03 ;