oracle海量数据

对海量数据进行分区操作
建立广泛的索引
建立缓存机制
加大虚拟内存
分批处理
使用临时表和中间表
优化查询SQL语句
使用文本格式进行处理
定制强大的清洗规则和出错处理机制
建立视图或者物化视图
避免使用32位机子（极端情况）
考虑操作系统问题
使用数据仓库和多维数据库存储
使用采样数据，进行数据挖掘




表按时间做的range分区


==================================================================================
以下信息摘抄自互联网


1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 


2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致数据库引擎放弃使用索引而进行全表扫描，如： 
select id from t where num is null 
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 
select id from t where num=0


3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。


4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 
select id from t where num=10 or num=20 
可以这样查询： 
select id from t where num=10 
union all 
select id from t where num=20


5.in 和 not in 也要慎用，否则会导致全表扫描，如： 
select id from t where num in(1,2,3) 
对于连续的数值，能用 between 就不要用 in 了： 
select id from t where num between 1 and 3


6.下面的查询也将导致全表扫描： 
select id from t where name like '%abc%' 
若要提高效率，可以考虑全文检索。


7.如果在 where 子句中使用参数，也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；
它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，
因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num


8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2


9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'


10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。


11.在使用索引字段作为条件时，
如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。


12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)


13.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)


14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，
如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。


15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。


16.应尽可能的避免更新 clustered 索引数据列，
因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，
一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。


17.尽量使用数字型字段，
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。


18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，
因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。


19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。


20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。


21.避免频繁创建和删除临时表，以减少系统表资源的消耗。


22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，
例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。


23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，
避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。


24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，
这样可以避免系统表的较长时间锁定。


25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。


26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。


27.尽量避免大事务操作，提高系统并发能力。


28.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。 
==================================================================================


B-Tree索引上创建并行属性：


create index index_name on TEST1 (TEST1_COLUMN) parallel;


说明：索引创建脚本中PARALLEL后面没有值，说明使用的是默认值，
它的计算方式和表上的并行度计算方式相同，索引上的并行度，只有在访问索引时才可以被使用


范围分区 按ID的范围分区
CREATE TABLE T_testing 
(字段) 
PARTITION BY RANGE (ID) 
(
PARTITION P1 VALUES LESS THAN (10000) tablespace space1, 
PARTITION P2 VALUES LESS THAN (20000) tablespace space2, 
PARTITION P3 VALUES LESS THAN (30000) tablespace space3, 
PARTITION P4 VALUES LESS THAN (40000) tablespace space4, 
PARTITION P5 VALUES LESS THAN (50000) tablespace space5, 
PARTITION P6 VALUES LESS THAN (maxvalue) 
)
 AS SELECT 字段 FROM testing;


(3)按日期范围分区
create table dinya_test ( 
transaction_id number primary key, 
item_id number(8) not null, 
item_description varchar2(300), 
transaction_date date not null 
) partition by range (transaction_date) ( 
partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)) tablespace dinya_space01, 
partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)) tablespace dinya_space02, 
partition part_03 values less than(maxvalue) tablespace dinya_space03 
); 


增加主机性能、网络传输能力、优化数据库、分表、分区、优化SQL！


分区表


使用各种分区表技术
使用各种分区索引技术


Oracle提供了分区技术以支持VLDB(Very Large DataBase)。
分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。


Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。
查询时可以通过查询表来访问各个分区中的数据，
也可以通过在查询时直接指定分区的方法来进行查询。


Oracle 10g提供了以下几种分区类型：


（1）范围分区（range）；


（2）哈希分区（hash）；


（3）列表分区（list）；


（4）范围－哈希复合分区（range-hash）；


（5）范围－列表复合分区（range-list）。


Range分区：


　　Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。


如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。


在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。


如：


create table pdba (id number, time date) partition by range (time)


(


partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),


partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),


partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),


partition p4 values less than (maxvalue)


)


Hash分区：


　　 对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。
hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，
因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。


如：


create table test


(


transaction_id number primary key,


item_id number(8) not null


)


partition by hash(transaction_id)


(


partition part_01 tablespace tablespace01,


partition part_02 tablespace tablespace02,


partition part_03 tablespace tablespace03


);


在这里，我们指定了每个分区的表空间。


List分区：


　　List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。


　　在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。


在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。


如：


create table custaddr
(


id varchar2(15 byte) not null,


areacode varchar2(4 byte)
)


partition by list (areacode)
( partition t_list025 values ('025'), 
partition t_list372 values ('372') , 
partition t_list510 values ('510'),


partition p_other values (default)


)


表可以按range、hash、list分区，表分区后，其上的索引和普通表上的索引有所不同，
oracle对于分区表上的索引分为2类，即局部索引和全局索引


建全局分区索引  
CREATE INDEX IDX_PT_C4 ON P_TAB(C4) GLOBAL PARTITION BY RANGE(C4)  
(  
PARTITION IP1 VALUES LESS THAN(10000),  
PARTITION IP2 VALUES LESS THAN(20000),  
PARTITION IP3 VALUES LESS THAN(MAXVALUE)  
);  


建本地分区索引  
CREATE INDEX IDX_PT_C2 ON P_TAB(C2) LOCAL (PARTITION P1,PARTITION P2,PARTITION P3,PARTITION P4); 


对表每个月一个分区的方式来进行创建分区


普通索引（字段重复率非常低的时候很有效）、
CREATE INDEX 索引名 ON 表名(字段名)


位图索引（字段重复率非常高的时候，建议使用,尽量少用，最好不要用）
CREATE bitmap INDEX 索引名 ON 表名(字段名)
OLTP不能使用位图索引，这应该是谨记的！ 


!!!针对船舶：
船舶mmsi使用位图索引(尽量少用，最好不要用)
OLTP不能使用位图索引，这应该是谨记的！ 


入库时间使用普通索引







==========================表空间=====================================
查了很多资料发现原来是Oracle表空间限制，导致无法继续导入数据的原因。如果在建立数据库时没有设置，


Oracle 默认的表空间大小为400M，当数据库中数据量达到这个值，再向数据库中导入数据就会报错。解决方法是


扩展表空间。可以选择将表容量扩大，比如扩展到5G，或者当表空间不够时每次自动增加一定的容量,如每次自增200M。


下面列出详细过程：


1.通过sql plus 命令登录数据库。


　　在命令行下输入sqlplus “登录用户名/口令 as 登录类型”就可以登录，系统内建的用户名常用的是sys，密码是在安装oracle过程中设置的密码，清务必牢记，如果用sys帐户登录，登录类型一定要是sysdba。


2.查看各表空间分配情况。


select tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files  


 group by tablespace_name;  


3.查看各表空间空闲情况。


select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;  


4.更改数据表大小(10G)


alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；


5.设置表空间不足时自动增长


5.1查看表空间是否自动增长


SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;


5.2 设置表空间自动增长


ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//打开自动增长


ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m


ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//每次自动增长200m，数据表最大不超过1G
==========================表空间=====================================




可以看看：
我是periodDate时间字段按月做的range分区，然后用companyid字段做的Hash子分区，
一般查询条件里面都会有companyId，periodDate这两个字段，还有一个accid字段可能有可能没有的


故建立：普通索引
不管是局部还是全局，都可以指定分区的，只不过局部的是按照分区条件自动分区的而已，全局的可以自己设定
主要还是根据查询情况来的，比如你只访问单个分区，可以建立局部分区


方式：分区表+普通索引。用mmsi做hash分区。普通索引：mmsi和入库时间