当数据库的数据不断扩大时,这时可以考虑是用表分区,查询时可以根据表分区查找,节省时间从而提高查询效率。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system
SQL>
SQL> create tablespace CUS_TS01 datafile 'g://CUS_TS01' size 5m;
Tablespace created
SQL> create tablespace CUS_TS02 datafile 'g://CUS_TS02' size 5m;
Tablespace created
SQL> create tablespace CUS_TS03 datafile 'g://CUS_TS03' size 5m;
Tablespace created
SQL>
SQL> CREATE TABLE MYCUSTOMER
2 (
3 CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
4 FIRST_NAME VARCHAR2(30) NOT NULL,
5 SEX CHAR(1)
6 )
7 PARTITION BY RANGE (CUSTOMER_ID)
8 (
9 PARTITION CUS_PART1 VALUES LESS THAN (10) TABLESPACE CUS_TS01,
10 PARTITION CUS_PART2 VALUES LESS THAN (20) TABLESPACE CUS_TS02,
11 PARTITION CUS_PART3 VALUES LESS THAN (maxvalue) TABLESPACE CUS_TS03
12 );
Table created
SQL>
SQL> declare
2 begin
3 for i in 1..100 loop
4 insert into MYCUSTOMER values(i,'xm','m');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> select count(*) cn from MYCUSTOMER;
CN
----------
100
SQL> select count(*) cn from MYCUSTOMER PARTITION (CUS_PART1);
CN
----------
9
SQL> select count(*) cn from MYCUSTOMER PARTITION (CUS_PART2);
CN
----------
10
SQL> select count(*) cn from MYCUSTOMER PARTITION (CUS_PART3);
CN
----------
81
本文仅供参考,标准按照 ORACLE 官方规定
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#