1、Oracle千万级记录插入和查询的技巧 Oracle千万级记录进行处理并不简单,下面就为您总结了千万级记录插入和查询的技巧,希望对您能够有所启迪。
最近做了个项目,实现对存在Oracle千万级记录的库表执行插入、查询操作。原以为对数据库的插入、查询是件很容易的事,可不知当数据达到百万甚至千万条级别的时候,这一切似乎变得相当困难。几经折腾,总算完成了任务。
1、防止运用 Hibernate框架 Hibernate用起来虽然方便,但对于海量数据的操作显得力不从心。
关于Oracle千万级记录插入:
试过用Hibernate一次性执行 5万条左右数据的插入,若ID运用 sequence方式生成,Hibernate将分5万次从数据库取得5万个sequence,构造成相应对象后,再分五万次将数据保存到数据库。花了我十分钟时间。主要的时间不是花在插入上,而是花在5万次从数据库取sequence上,弄得我相当郁闷。虽然后来把ID生成方式改成increase处理了疑问,但还是对那十分钟的等待心有余悸。
关于Oracle千万级记录查询:
Hibernate对数据库查询的主要思想还是面向对象的,这将使许多我们不须要查询的数据占用了大量的系统资源(包括数据库资源和本地资源)。由于对Hibernate的偏爱,本着不抛弃、不放弃的作风,做了包括配SQL,改良 SQL等等的相当多的尝试,可都以失败告终,不得不忍痛割爱了。
2、写查询语句时,要把查询的字段一一列出
查询时不要运用类似select * from x_table的语句,要尽量运用 select id,name from x_table,以防止查询出不须要的数据浪费资源。对于海量数据而言,一个字段所占用的资源和查询时间是相当可观的。
3、减少不必要的查询条件 当我们在做查询时,常常是前台提交一个查询表单到后台,后台分析这个表单,而后执行查询操作。在我们分析表单时,为了方便起见,常常喜欢将一些不须要查询的条件用永真的条件来代替(如:select count(id) from x_table where name like ‘%’),其实这样的SQL对资源的浪费是相当可怕的。
4、sql语句优化技巧
我试过对于同样的近一千万条记录的查询来说,运用 select count(id) from x_table 执行 表查询须要 11秒,而运用 select count(id) from x_table where name like ‘%’却花了33秒。 IN 操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
推荐方案:在业务密集的SQL当中尽量不采用IN操作符。
NOT IN操作符
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替
<> 操作符(不等于)
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 推荐方案:用其它相同功能的操作运算代替,如
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’
IS NULL 或IS NOT NULL操作(判断字段是否为空)
判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
推荐方案:
用其它相同功能的操作运算代替,如
a is not null 改为 a>0 或a>’’等。
不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。
建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)
> 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
LIKE操作符
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高
2、Oracle表分区和索引分区汇总
分区概述
为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑元限膨胀的大表,组大表在物理一级的可管理性.将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。
分区的优点:
1、 增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分
区仍可以使用;
2、 减少关闭时间:如果系统故障只影响表的一部份分区,那么只有这部份分区需要修复,矿能比整个大表修复花的时间更少;
3、 维护轻松:如果需要得建表,独产管理每个公区比管理单个大表要轻松得多; 4、 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能; 5、 改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快,在数据仓库的TP查询特别有用。
6、 分区对用户透明,最终用户感觉不到分区的存在。
create tablespace dw1
datafile 'D:"oracle"oradata"ora9"dw11.ora' size 50M
create tablespace dw2
datafile 'D:"oracle"oradata"ora9"dw21.ora' size 50M
一、按范围分区:固名思义就是按一定range来分区,看下面的例子: SQL> set linesize 1000
SQL> create table niegc_part
2 (
3 part_id integer primary key,
4 part_date date,
5 part_dec varchar2(100)
6 )
7 partition by range(part_date)
8 (
9 partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,
10 partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,
11 partition part_03 values less than(maxvalue) tablespace dw1 12 );
表已创建。
SQL>
SQL> insert into niegc_part values(1,to_date('2005-12-30','yyyy-mm-dd'),'less 20
06-01-01');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into niegc_part values(2,to_date('2006-01-01','yyyy-mm-dd'),'equal 2
007-01-01');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into niegc_part values(3,sysdate,'sysdate');
已创建 1 行。
SQL> commit;
提交完成。
SQL>
SQL>
SQL> select * from niegc_part partition(part_01);
PART_ID PART_DATE PART_DEC
---------- ---------- ----------------------------------------------------------
1 30-12月-05 less 2006-01-01
SQL>
相信只要对oracle 有点熟,都能知道上面的range分区的意思了.
两个字段以上的range分区大同小异,请看下面的例子:
create table niegc_part
(
part_id integer primary key,
part_date date,
part_dec varchar2(100)
)
partition by range(part_id,part_date)
(
partition part_01 values less than(1,to_date('2006-01-01','yyyy-mm-dd')) tablespace dw, partition part_02 values less than(10,to_date('2007-01-01','yyyy-mm-dd')) tablespace dw, partition part_03 values less than(maxvalue,maxvalue) tablespace dw
);
二、Hash分区(散列分区)。散列分区通过指定分区编号来均匀分布数据
的一种分区类型,因为通过在I/O设备上进行散列分区,使行这些分区大小一致。如将part_id的数据根据自身的情况散列地存放在指定的三个表空间中:
createtableniegc_part
(
part_id integerprimarykey,
part_date date,
part_dec varchar2(100)
)
partitionbyhash(part_id)
(
partition part_01 tablespace dw1,
partition part_02 tablespace dw2
);
系统将按part_id将记录散列地插入三个分区中,这里也就是二个不同的表空间中。
三、复合分区。根据范围分区后,每个分区内的数据再散列地分布在几个表空
间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区同再使用散列分区的一种分区方法,如将part_date的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中:
createtableniegc_part
(
part_id integerprimarykey,
part_date date,
part_dec varchar2(100)
)
partition by range(part_date)subpartition by hash(part_id)
subpartitions2 store in(dw1,dw2)
(
partition part_01 valueslessthan(to_date('2006-01-01','yyyy-mm-dd'))tablespace dw1,
partition part_02 valueslessthan(to_date('2007-01-01','yyyy-mm-dd'))tablespace dw2,
partition part_03 valueslessthan(maxvalue)tablespace dw1
);
先根据part_date进行范围分区,然后根据交易的ID将记录散列地存储在二个表空间中。
四、索引分区:
注意:对某个字段已做了分区了,是不允许再建立索引分区的。这一点要非常注意。
全局索引建立时global子句允许指定索引的范围值,这个范围值为索引字段的范围值:
create index idx_part_id onniegc_part(part_dec)
global partition by range(part_dec)
(
partition idx_1 valueslessthan('1000')tablespace dw,
partition idx_2 valueslessthan(maxvalue)tablespace dw
)
局部索引分区的建立:(注意:表必须存在分区,此分区的个数必须和分区表的分区个数一样,不然是建立不起来的)
createindex idx_part_id onniegc_part(part_dec)
local
(
partition idx_1 tablespace dw1,
partition idx_2 tablespace dw2
)
五、分区维护:(只对范围分区)
(1)、增加一个分区:分区范围只能往上增,不能增加一个少于原有的分区: altertableniegc_partaddpartition part_03 valueslessthan(maxvalue)
(2)、合并分区:(合并后的分区必须指下最后一个大value的分区)
altertableniegc_partmergepartitions part_02,part_03 into partition part_03
(3)、删除一个分区:
altertableniegc_partdroppartition part_01
六、总结:
分区表是将大表的数据分成称为分区的许多小的子集,9i提供四种分区方法:列表分区,范围分区,哈希分区和混合分区;
1.范围分区是根椐分区键的不同取值范围来划分子集的,关键字RANGE, VALUES LESS THAN;
2.列表分区是根椐分区键的一些离散的取值来划分子集的,关键字LIST, VALUES;
3.哈希分区是应用哈希算法将分区键对应到某个子集中去,关键字HASH, PARTITIONS;
4.混合分区只能有两层,第一层是范围分区,第二层可以是列表分区或者哈希分区;
5.范围分区和列表分区中,如果插入记录的分区键没有对应的容纳分区,会产生ORA-14400;
6.update操作如果会使记录从一个分区迁移到另一个分区,且分区表的ROW MOVEMENT属性是DISABLE,会产ORA-14402;
7.分区表上的索引有两大类:普通的二叉树索引,分区索引,下面讲到的都是分区索引:
8.按索引分区和表分区间的对应关系可以分为局部索引和全局索引;
9.局部索引的索引分区和表分区间是一一对应的,全局索引则相反;
10.局部索引的分区方法可以用上面提到四种的任何一种,全局索引的分区方法只有范围分区(而且最高的分区必须用MAXVALUE来定义);
11.ORACLE自动维护局部索引的分区,当表分区被合并,分裂或删除时,关联的索引分区也会被合并,分裂或删除;对分区表执行管理操作时会使其上的全局索引失效;
12.建在分区表的位图索引必须是局部分区索引;
13.ORACLE推荐尽可能地使用局部索引;
14.按索引栏位和分区键间的关系分为前缀索引和非前缀索引;
15.前缀索 引最前面的栏位是分区键栏位,非前缀索引相反;
16.在 这两 带来很多选项,如分区排除,并行分区连接等。