oracle数据库小小心得

最新推荐文章于 2024-10-11 17:04:33 发布

magicbluestone

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量709

点赞数

文章标签：数据库 oracle table oracle10g statistics delete

本文链接：https://blog.csdn.net/magicbluestone/article/details/3907067

版权

程序心得

数据库篇：

数据库里中我们主要的功能是就是查询，当面对几十万，几百万的数据记录查询时，我们自然会考虑使用索引，还有分区。对数据库里有几种情况不走索引的情况做一个更正，一个是模糊查询时，我们一般都会认为是全表的扫描，不会通过的索引要来完成，其实不然，当我们建立索引后对的索引字段做一个升序的树排列，这样当我们用like ‘1254%’时还是会用到索引的，通过一个三百万数据记录的表来做过测试，当没有建立索引时，我们查询一个ID时需要20-30秒的时间，当建立索引时，我们通过模糊查询也只2-3秒左右，速度还是比较快的。所以模糊查询还是通过有索引字段的速度比较好。还有一个count(*)，当我们在统计时的时候，可能会认为count(*)与count(字段)并没有什么大的区别都会是全表扫描，如果字段是已建立索引后，这样数据库做索引全表扫描，自然速度也比count(*)要来的快，所以平时用统计的时候，应该多写count(字段)，而不是count(*)比较省事的方法。

还有在group by这个分组里面，这个操作也是分组排序的，也是会进入到数据库排序内存里操作，这样我们平时在查询的时候应该写好查询条件再group by，还有就是group by字段的时候也应该尽量按索引字段来分组这样查询的速度也会快很多。

子查询的问题，当我们想到子查询时可能会觉的查询速度也比较慢，其实也不然，只要我们合理去sql语句时，速度也是很快的，一个原则是子查询里面的查询到数据集应该是尽量少的，这样的和上层的查询时相交的数据会有少一些，速度也会快，例 select * from table1 where field1 in (select field1 from table2 where field2 = ‘X’) 在这个查询里table1应该是放数据量相对大的表，table2应该是放数据量相对小的表，当select * from table1 where (field1,field2) in (select field1,field2 from table2 where field1 =’X’) 当table1里也没有对field1,field2做复合索引，这样查询时会全表扫描的，这样如果table1数据量很大时，这样查询的很慢，也就成为一个悲剧查询。还有一种子查询是 select* from (select* from table1 where field1 = ‘X’) where field2 =’Y’ 这种子查询我们平时使用也比较多，也很好用，因为我们通过子查询的查询到的数据集当做一个表来提供给外一层来查询的，当子查询后的数据集比较小的这样外查询的速度也会是很快的。当子查询里的字段有索引时，在外查询里也使用这个字段时，数据库也是会走索引的，这样的查询就比较理想的。

分区这是一个合理分配大表的存放空间的东西，也能使我们查询再加的合理与有效，一般我们做分区都是以时间也做分区条件，这样当我们有一年的数据，以每个月一个分区的方式，这样有12个分区，当我们要查询某一个月的数据时，数据库只会针对一个分区做查询，这样查询的数据自然快很多，也减少的数据库的负担。分区表里可以建立两种索引，一种是全局索引，一种是局部索引。全局索引它是维护全分区表的数据，局部索引只维护自己的独立的索引，个人感觉局部索引比较好用，当然也要看具体的情况，如果经常查询全表数据自己全局的比较好用。不过我们一般的查询也针对某个时间段的查询比较多，全表也只有偶尔查询。所以用局部索引比较好。

索引里面分为单一索引与复合索引，这里面我一般是使用单一索引，因为这样的查询条件可以灵活一些，可操作比较强，如果是使用复合索引时我们就要按复合索引的字段顺序来做查询条件，这样的查询方式比较单一，不易于查询。当我们对表建立索引以后，发现查询速度基本没有提高，在计划执行里也看到是根本不走索引，如果是原来表的数据很少，现在导入了大量的数据后，再建立索引时就会有可能出现不走索引的情况，个人的做法是analyze table table_name delete statistics这样表又会走索引的。（参见oracle9i&10g编程艺术的索引这一章）当数据里面有索引存在时，插入大量的数据时，速度会非常慢，如果是插入百万、千万的数据记录时我们要先删除索引再插入数据，然后再建立索引。当建立复合索引时，这时与字段的粒度有关，当我们有一张table_total是一张几千万数据记录的大表，里面有字段（时间，地区，ID，其它），我们建立的复合是（时间，地区，ID）这样顺序索引时，时间与地区这两个都是粒度很大的字段，当我们查询这两个字段条件时，速度自然比较快，当再上ID后，在查询ID时他是对（时间、地区）查询到数据集进行扫描，如果时间跨度小查询速度还可以，如果查询的时间跨度很大时，这样的查询将会变的很慢，又是一个悲剧查询。当然这样的索引插入数据的速度相当还是比较快的。因为主条件是时间字段，插入的维护开销自然比较小。

插入这个在数据库里对索引好像是天生的矛盾体，当我们要查询速度快就会建立索引，当建立索引后，插入的速度就会直线下降，这样就会让我们很为难两者不能兼得，我的想法是用分区局部索引能相对比较解决，当然这时我们对把分区分的更小一些，这样局部索引对数据插入维护就尽量的少。当然这样对查询时间跨度很大的查询就会差一些。还有在我们在程序里向数据库插入数据时，如果是用ADO时这里会是插入后马上提交，这样可能效率了不是很高，如果程序是用occi的话，程序自身去控制提交的功能的话，最好在插入100条提交一次，不要全部插入后再提交，这样的数据库会产生多版本的负担，万一回滚就产生很多回退，这样就不太好。

删除在数据库里delete使用这个操作可能对数据库产生很多的碎片，这样就不太好了，如果是删除一表的内容时，truncate这个操作比较好，不过truncate这个操作以后就找不回数据（有数据备份还是可以找回的），所以对truncate操作还是要小心。

视图这是一个很好多的功能，如果数据库里把一张大表按月分成很多小表时，我们用视图把小表联合起（union all）就相当一张大表，当然我们还可以针对每张小表进行具体的操作，灵活性比较高，这个和分区表是类似的，分区表的对索引的统一管理的，我们无法删除分区里某一个分区索引的，这样当我们要把数据插入到某一个分区时，我们就要删除全部的分区索引，插入后再建全部分区索引，这样的操作就会很废时间，当然这个也是数据库索引全部维护的关系，这样我们的用视图就会有优势，当我们要插入某一个月的数据，我们只要删除某个月的小表的索引，插入后再建立索引，当然我们要对全部小表自己维护，这样也很相对麻烦一些。当然视图可以表里字段处理，这样在程序只要读取数据就可以，而不用再去处理数据，例 select num , field1 from table 我们在视图里select to_char(num), fun1(field1) table 这样在数据库在数字转变为字符串，对字段field1进行函数处理，当然在数据库要写一个fun1这个函数处理。

写几个sql例子，table_dept(deptno,deptname,emp_name,emp_id,salary)

当要求出每个部门的最高工资几种写法

select deptno,deptname,emp_name,emp_id,salary from table_dept

Where (deptno,salary) in(select deptno,max(salary) from dept group by deptno)

select deptno,deptname,emp_name,emp_id,salary from table_dept a,

( select deptno,max(salary) from dept group by deptno) b where a. deptno = b. deptno

And a. salary = b. salary

Select deptno,deptname,emp_name,emp_id,salary from(

select deptno,deptname,emp_name,emp_id,salary ,max(salary) over(partition by deptno) max_salary from table_dept)

where salary = max_salary

三个sql语句都是能得出相同的记录，效率第一个最差，最后一个最好。

如果要求出每个部门最高工资前三名的话，