500万的数据量不用分区,但是考虑到以后的数据增长,最好作一下 分区的准备工作. 例子: create table t (dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY) partition by range(dt) ( partition part2000 values less than (to_date('01-01-2001','dd-mm-yyyy')), partition part2001 values less than (to_date('01-01-2002','dd-mm-yyyy')), partition part2002 values less than (to_date('01-01-2003','dd-mm-yyyy')) ) as select sysdate dt, all_objects.* from all_objects where 1=0;
还有就是把表和索引放在不同的表空间上.
而对于大容量表所取分区方法(oracle中心已采取的方法)
以考帮助文档:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767
index就不必说了
另外,就是优化查询,500万的数据是小意思,但是如你所说,每天增长,
还是赶快行动吧,不然,嘿嘿…… ……
越来越麻烦的
分区的准备工作. 例子: create table t (dt, OWNER,
OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY)
partition by range(dt) (
partition part2000 values less than
(to_date('01-01-2001','dd-mm-yyyy')),
partition part2001 values less than
(to_date('01-01-2002','dd-mm-yyyy')),
partition part2002 values less than
(to_date('01-01-2003','dd-mm-yyyy'))
) as
select sysdate dt, all_objects.*
from all_objects where 1=0;