oracle超大数据库表分区只复合分区表（范围散列分区表）

oracle超大 数据库表分区只复合分区表（范围散列分区表）

[sql]

create table graderecord04   

(    

   sno varchar2(10),    

sname varchar2(20),    

dormitory varchar2(3),    

  grade int   

)    

partition by range(grade)

subpartition by hash(sno,sname)    

(    

  partition p1 values less than(75)    

             (    

               subpartition sp1  tablespace test01,subpartition sp2  tablespace test02  ------指定表空间的地方 

             ),    

   partition p2 values less than(maxvalue)    

            (    

                subpartition sp3 tablespace test02,subpartition sp4 tablespace test03    -------指定表空间的地方 

             )  

);   

SQL> create table graderecord04 

  2  (  

  3    sno varchar2(10),  

  4    sname varchar2(20),  

  5    dormitory varchar2(3),  

  6    grade int 

  7  )  

  8  partition by range(grade)  

  9  subpartition by hash(sno,sname)  

10  (  

11    partition p1 values less than(75)  

12              (  

13                 subpartition sp1  tablespace test01,subpartition sp2  tablespace test02 

14              ),  

15    partition p2 values less than(maxvalue)  

16              (  

17                 subpartition sp3 tablespace test02,subpartition sp4 tablespace test03 

18              )

19  );

Table created.

SQL>

insert into graderecord04 values('511601','魁','229',92);  

insert into graderecord04 values('511602','凯','229',62);  

insert into graderecord04 values('511603','东','229',26);  

insert into graderecord04 values('511604','亮','228',77);  

insert into graderecord04 values('511605','敬','228',47);  

insert into graderecord04(sno,sname,dormitory) values('511606','峰','228');  

insert into graderecord04 values('511607','明','240',90);  

insert into graderecord04 values('511608','楠','240',100);  

insert into graderecord04 values('511609','涛','240',67);  

insert into graderecord04 values('511610','博','240',75);  

insert into graderecord04 values('511611','铮','240',60);  

insert into graderecord04 values('511612','狸','244',72);  

insert into graderecord04 values('511613','杰','244',88);  

insert into graderecord04 values('511614','萎','244',19);  

insert into graderecord04 values('511615','猥','244',65);  

insert into graderecord04 values('511616','丹','244',59);  

insert into graderecord04 values('511617','靳','244',95);

select * from graderecord04 partition(p1);  

select * from graderecord04 partition(p2);  

select * from graderecord04 subpartition(sp1);  

select * from graderecord04 subpartition(sp2);  

select * from graderecord04 subpartition(sp3); 

SQL> select * from graderecord04;

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

511615     ???                  244         65

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511615     ???                  244         65

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

511617     ???                  244         95

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511617     ???                  244         95

34 rows selected.

SQL> select * from graderecord04 partition(p1);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

511615     ???                  244         65

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511615     ???                  244         65

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

18 rows selected.

SQL> select * from graderecord04 partition(p2);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511617     ???                  244         95

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

511617     ???                  244         95

16 rows selected.

SQL> select * from graderecord04 subpartition(sp1);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

511615     ???                  244         65

511602     ???                  229         62

511605     ???                  228         47

511609     ???                  240         67

511612     ???                  244         72

511614     ???                  244         19

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511615     ???                  244         65

12 rows selected.

SQL> select * from graderecord04 subpartition(sp2);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

511603     ???                  229         26

511611     ???                  240         60

511616     ???                  244         59

6 rows selected.

SQL> select * from graderecord04 subpartition(sp3);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

511604     ???                  228         77

511606     ???                  228

511607     ???                  240         90

511613     ???                  244         88

8 rows selected.

SQL> select * from graderecord04 subpartition(sp4);

SNO        SNAME                DOR      GRADE

---------- -------------------- --- ----------

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

511617     ???                  244         95

511601     ???                  229         92

511608     ???                  240        100

511610     ???                  240         75

511617     ???                  244         95

8 rows selected.

SQL>