SQL功能优化ITeye - 凯时娱乐

SQL功能优化ITeye

2019年02月24日12时10分04秒 | 作者: 寻桃 | 标签: 优化,运用,句子 | 浏览: 660

1、选用适宜的ORACLE优化器
ORACLE的优化器共有3种:
   a.  RULE (依据规矩)   b. COST (依据本钱)  c. CHOOSE (挑选性)

   设置缺省的优化器,能够经过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行掩盖.
   为了运用依据本钱的优化器(CBO, Cost-Based Optimizer) , 你有必要常常运转analyze 指令,以添加数据库中的目标核算信息(object statistics)的准确性.
   假如数据库的优化器办法设置为挑选性(CHOOSE),那么实践的优化器办法将和是否运转过analyze指令有关. 假如table现已被analyze过, 优化器办法将主动成为CBO , 反之,数据库将选用RULE办法的优化器.
   在缺省状况下,ORACLE选用CHOOSE优化器, 为了防止那些不必要的全表扫描(full table scan) , 你有必要尽量防止运用CHOOSE优化器,而直接选用依据规矩或许依据本钱的优化器.

2、拜访Table的办法
ORACLE 选用两种拜访表中记载的办法:
a.全表扫描
       全表扫描就是次序地拜访表中每条记载. ORACLE选用一次读入多个数据块(database block)的办法优化全表扫描.
   
b.经过ROWID拜访表
       你能够选用依据ROWID的拜访办法状况,进步拜访表的功率, , ROWID包括了表中记载的物理方位信息..ORACLE选用索引(INDEX)完结了数据和寄存数据的物理方位(ROWID)之间的联络. 一般索引供给了快速拜访ROWID的办法,因而那些依据索引列的查询就能够得到功用上的进步

3、同享SQL句子
为了不重复解析相同的SQL句子,在第一次解析之后, ORACLE将SQL句子寄存在内存中.这块坐落体系大局区域SGA(system global area)的同享池(shared buffer pool)中的内存能够被一切的数据库用户同享. 因而,当你履行一个SQL句子(有时被称为一个游标)时,假如它和之前的履行过的句子完全相同, ORACLE就能很快取得现已被解析的句子以及最好的
履行途径. ORACLE的这个功用大大地进步了SQL的履行功用并节省了内存的运用.
     惋惜的是ORACLE只对简略的表供给高速缓冲(cache buffering) ,这个功用并不适用于多表衔接查询.数据库管理员有必要在init.ora中为这个区域设置适宜的参数,当这个内存区域越大,就能够保存更多的句子,当然被同享的可能性也就越大了.
当你向ORACLE 提交一个SQL句子,ORACLE会首要在这块内存中查找相同的句子.
 这儿需求注明的是,ORACLE对两者采纳的是一种严厉匹配,要达到同享,SQL句子有必要
完全相同(包括空格,换行等).
     同享的句子有必要满意三个条件:
A.字符级的比较:
当时被履行的句子和同享池中的句子有必要完全相同(sql句子的大小写、中心的空格都要完全相同。)
B. 两个句子所指的目标有必要完全相同
C. 两个SQL句子中有必要运用相同的姓名的绑定变量(bind variables)
比方,下面两个SQL句子就不相同:
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;

4、用EXISTS代替IN
在许多依据根底表的查询中,为了满意一个条件,往往需求对另一个表进行联接.在这种状况下, 运用EXISTS(或NOT EXISTS)一般将进步查询的功率
低效:
SELECT *
FROM EMP (根底表)
WHERE EMPNO   0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
   
高效:
SELECT *
FROM EMP (根底表)
WHERE EMPNO   0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
5. 用NOT EXISTS代替NOT IN
在子查询中,NOT IN子句将履行一个内部的排序和兼并. 不管在哪种状况下,NOT IN都是最低效的 (由于它对子查询中的表履行了一个全表遍历).  为了防止运用NOT IN ,咱们能够把它改写成外衔接(Outer Joins)或NOT EXISTS.

例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
                         FROM DEPT
                         WHERE DEPT_CAT=’A’);

为了进步功率.改写为:

(办法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’


(办法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
                    FROM DEPT D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = ‘A’);

6. 用表衔接替换EXISTS
一般来说 , 选用表衔接的办法比EXISTS更有功率
      SELECT ENAME
      FROM EMP E
      WHERE EXISTS (SELECT ‘X’
                      FROM DEPT
                      WHERE DEPT_NO = E.DEPT_NO
                      AND DEPT_CAT = ‘A’);

     (更高效)
      SELECT ENAME
      FROM DEPT D,EMP E
      WHERE E.DEPT_NO = D.DEPT_NO
      AND DEPT_CAT = ‘A’ ;
7. 用EXISTS替换DISTINCT
当提交一个包括一对多表信息(比方部分表和雇员表)的查询时,防止在SELECT子句中运用DISTINCT. 一般能够考虑用EXIST替换

例如:
低效:
    SELECT DISTINCT DEPT_NO,DEPT_NAME
    FROM DEPT D,EMP E
    WHERE D.DEPT_NO = E.DEPT_NO
高效:
    SELECT DEPT_NO,DEPT_NAME
    FROM DEPT D
    WHERE EXISTS ( SELECT ‘X’
                    FROM EMP E
                    WHERE E.DEPT_NO = D.DEPT_NO);

  EXISTS 使查询更为敏捷,由于RDBMS中心模块将在子查询的条件一旦满意后,马上回来成果.

8. 挑选最有功率的表名次序(只在依据规矩的优化器中有用)
ORACLE的解析器依照从右到左的次序处理FROM子句中的表名,因而FROM子句中写在最终的表(根底表 driving table)将被最早处理. 在FROM子句中包括多个表的状况下,你有必要挑选记载条数最少的表作为根底表.当ORACLE处理多个表时, 会运用排序及兼并的办法衔接它们.首要,扫描第一个表(FROM子句中最终的那个表)并对记载进行派序,然后扫描第二个表(FROM子句中最终第二个表),最终将一切从第二个表中检索出的记载与第一个表中适宜记载进行兼并.
假如有3个以上的表衔接查询, 那就需求挑选穿插表(intersection table)作为根底表, 穿插表是指那个被其他表所引证的表.
9. WHERE子句中的衔接次序.
ORACLE选用自下而上的次序解析WHERE子句,依据这个原理,表之间的衔接有必要写在其他WHERE条件之前, 那些能够过滤掉最大数量记载的条件有必要写在WHERE子句的结尾.
10. SELECT子句中防止运用"*"
当你想在SELECT子句中列出一切的COLUMN时,运用动态SQL列引证"*"是一个便利的办法.不幸的是,这是一个十分低效的办法. 实践上,ORACLE在解析的过程中, 会将’*’ 顺次转换成一切的列名, 这个作业是经过查询数据字典完结的, 这意味着将消耗更多的时刻

11. 删去重复记载
最高效的删去重复记载办法 ( 由于运用了ROWID)

DELETE FROM EMP E
WHERE E.ROWID   (SELECT MIN(X.ROWID)
                   FROM EMP X
                   WHERE X.EMP_NO = E.EMP_NO);
12. 用TRUNCATE代替DELETE
13. 尽量多运用COMMIT
14. 核算记载条数
     和一般的观念相反, count(*) 比count(1)稍快 , 当然假如能够经过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
15. 用Where子句替换HAVING子句
     防止运用HAVING子句, HAVING 只会在检索出一切记载之后才对成果集进行过滤. 这个处理需求排序,总计等操作. 假如能经过WHERE子句约束记载的数目,那就能削减这方面的开支.

16、 用IN来替换OR
下面的查询能够被更有功率的句子替换:
低效:
SELECT….
FROM LOCATION
WHERE LOC_ID = 10
OR     LOC_ID = 20
OR     LOC_ID = 30

高效
SELECT…
FROM LOCATION
WHERE LOC_IN IN (10,20,30);
17、防止在索引列上运用IS NULL和IS NOT NULL
18、总是运用索引的第一个列
假如索引是建立在多个列上, 只要在它的第一个列(leading column)被where子句引证时,优化器才会挑选运用该索引.
SQL  create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL  create index multindex on multiindexusage(inda,indb);
Index created.
SQL  set autotrace traceonly

SQL   select * from  multiindexusage where inda = 1;
Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE
   2    1     INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE)

SQL  select * from  multiindexusage where indb = 1;
Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF MULTIINDEXUSAGE
  
很明显, 当仅引证索引的第二个列时,优化器运用了全表扫描而疏忽了索引

19、用WHERE代替ORDER BY
ORDER BY 子句只在两种严厉的条件下运用索引.

ORDER BY中一切的列有必要包括在相同的索引中并保持在索引中的摆放次序.
ORDER BY中一切的列有必要界说为非空.

WHERE子句运用的索引和ORDER BY子句中所运用的索引不能并排.
20、运用日期
当运用日期是,需求留意假如有超越5位小数加到日期上, 这个日期会进到下一天!
例如:
a.
SELECT TO_DATE(‘01-JAN-93’+.99999)
FROM DUAL;
Returns:
   ’01-JAN-93 23:59:59’
b.
SELECT TO_DATE(‘01-JAN-93’+.999999)
FROM DUAL;
Returns:
   ’02-JAN-93 00:00:00’

21、运用显式的游标(CURSORs)
运用隐式的游标,将会履行两次操作. 第一次检索记载, 第2次查看TOO MANY ROWS 这个exception . 而显式游标不履行第2次操作.

!EndFragment
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯时娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    SQL功能优化ITeye

    优化,运用,句子
  • 2
  • 3
  • 4

    SQL编程(一)ITeye

    类型,编程,进程
  • 5

    Oralce序列机制ITeye

    序列,问题,现象
  • 6

    数据库的粗浅知道ITeye

    数据库,范式,主键
  • 7

    [Oracle]取OracleITeye

    检查,数据库,用户
  • 8
  • 9

    Oracle树立DBLINK的具体过程ITeye

    数据库,北京,新疆
  • 10

    Oracle的splitITeye

    函数,数组,结构