Oracle多表查询的优化

1，重复性查询：

当你向oracle提供一个sql的时候，oracle会首先在相应的内存中查找这条语句，如果匹配到，就能直接返回结果，这样就能节省内存和提高数据访问效率。但是匹配sql语句需要注意以下几点：

（1）字符级的比较：sql语句和共享池中的语句必须完全相同才能匹配完整，尤其注意大小写和空格

（2）两个语句所指的对象必须完全相同，就是说sql语句共享是区分用户的，两个用户的公共对象才能共享sql

（3）sql语句中必须使用相同的名字的绑定变量

2，表名顺序对效率的影响：oracle是从右到左的顺序解析from字句中的表名

例如表A中有 1500条数据，B中有1条数据

低效率：select count(*) from B,A

高效率：select count(*) from A,B

3，三个以上的表连接查询，就需要选择交叉表作为基础表

低效率：select * from E,A,B

            where E.x = xxx

            and E.y = A.y

            and E.z = B.z

高效率：select * from A,B,E

            where E.x = xxx

            and E.y = A.y

            and E.z = B.z

4,where子句中的连接顺序：oracle采用自上而下的顺序解析where子句，所以表之间的连接一定写在其他的条件之前，

那些可以过滤掉最带数据量的条件要放在末尾

低效率：select xxx

            from EMP e

            where sal > 5000

                      and job = 'manager'

                      and 管理人数>20

高效率：select xxx

            from EMP e

            where 管理人数>20

                      and al > 5000

                      and job = 'manager'

5，select子句中避免使用*：实际上，oracle在解析的过程中，会将“*”依次转换成所有的列名

6，尽量减少访问数据库的次数

7，decode函数的使用能减少处理时间：decode（参数，匹配值1，返回值1，匹配值2，返回值2。。。默认值）

例：select type ,decode(blood_test_flag,'Y','Yes','N','No',null,'None','Invalid') from A;

使用实例：

低效率：             

               select count(*) ,sum(sal) from emp

               where dept_no = 1

               and ename like 'zj';

               select count(*) ,sum(sal) from emp

               where dept_no = 2

               and ename like 'zj';

高效率：             

               select count(decode(dept_no,1,'x',null)) count_1,

                         count(decode(dept_no,2,'x',null)) count_2,

                         sum(decode(dept_no,1,sal,null)) sal_1,

                         sum(decode(dept_no,2,sal,null)) sal_2

               from emp

               where ename like 'zj';

8，删除重复记录

最高效删除重复记录的方法（使用rowID）

delete from emp e

where e.rowid > (

      select min(x.rowid)

      from emp x

      where x.emp_no = e.emp_no

);

9，update语句

低效率：update A set a = 1,b=2 where id = 01;

高效率：update A set （a ,b）=（1,2） where id = 01;

10，用exist代替IN

低效率：select * from emp

            where  empno > 0

                  and  empno in (

                  select deptno

                  from dept

                  where loc = 'melg'

            )

高效率：select * from emp

            where empno > 0

                      and exists (

                          select  'x' from dept

                          where dept.deptno = emp.deptno

                                    and loc = 'melg'

                       )

 

11，识别’低效率执行‘的sql语句

可以使用下面的sql语句查询出低效sql：

select executions ,disk_reads,buffer_gets,

          round((buffer_gets-disk_reads)/buffer_gets,2) hit_radio.

          round(disk_reads/executions,2)reads_per_run,

          sql_text

from v$sqlarea

where executions>0

          and buffer_gets>0

          and (buffer_gets-disk_reads)/buffer_gets<0.8

group by 4 desc;