Oracle-SQL语句的优化 SQL语句的执行过程 执行计划 绑定变量共享sql语句 索引的使用 一般原则 常用操作符 SQL书写的影响 其他建议SQL语句的执行过程 1.分析 2.绑定变量 3.按照执行计划执行 4.抓取返回结果 分析 语法 分析语法是否正确 语意权限是否符合 视图的代替 优选出执行计划根据优化器CBO,RBO优化器CBO 所以读表的路径为先找出empno=10的，再找出薪资大于4000的，最 后再排序。CBO 基于成本的优化器，在以前10g以后的数据库版本主推的优化器。需 要经过一系列的运算，找出一个成本最低的执行计划。包括是否走索 引，选择合适的表连接方式。绑定变量共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后， ORACLE 将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的 数据库用户共享。 因此，当你执行一个SQL语句(有时被称为一 个游标)时，如果它和之前的执行过的语句完全相同， ORACLE 就能很快获得已经被解析的语句以及最好的执行路径。 ORACLE 的这个功能大大地提高了SQL的执行性能并节省了内存的使用。数据库管理员必须在init.ora中为这个区域设置合适的参数，当 这个内存区域越大，就可以保留更多的语句，当然被共享的可 能性也就越大了。当你向ORACLE 提交一个SQL语句，ORACLE会首先在这块内存 中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配， 要达成共享，SQL语句必须完全相同(包括空格，换行等)。共享的语句必须满足三个条件 A. 字符级的比较：当前被执行的语句和共享池中的语句必须完全相 同。例如：SELECT * FROM EMP; 和下列每一个都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP; 共享的语句必须满足三个条件（续 ）B. 两个语句所指的对象必须完全相同：例如：用户 对象名 如何访问Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner 考虑一下下列SQL语句能否在这两个用户之间共享。SQL 能否共享 原因select max(sal_cap) from sal_limit; 不能 每个用户都有一个private synonym - sal_limit , 它 们是不同的对象select count（*） from work_city where sdesc like NEW%; 能 两个用户访问相同的对象 public synonym - work_cityselect a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用 户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.共享的语句必须满足三个条件（续 ） C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)例如：第一组的两个SQL语句是相同的(可以共享)，而第二组中的两 个语句是不同的(即使在运行时，赋于不同的绑定变量相同的值)a. select pin ， name from people where pin = ：blk1.pin;select pin ， name from people where pin = ：blk1.pin; eg； Sqlvar aa number Sqlexec :aa:=7900; Sqlselect * from emp where empno=:aa; Sqlexec :aa=7839; Sqlselect * from emp where empno=:aa;读表的方式 读取一张表有两种方式，全表扫描，ROWID 访问表。 a. 全表扫描全表扫描就是顺序地访问表中每条记录。 ORACLE采用 一次读入多个数据块(database block)的方式优化全表扫 描。b. 通过ROWID访问表，-索引的方式你可以采用基于ROWID的访问方式情况，提高访问表的 效率， ROWID包含了表中记录的物理位置信息。 ORACLE采用索引(INDEX)实现了数据和存放数据的物理 位置(ROWID)之间的联系。通常索引提供了快速访问 ROWID的方法，因此那些基于索引列的查询就可以得到 性能上的提高。尽量避免全表扫描 消除不必要的大表全表搜索：不必要的全表搜索 导致大量不必要的I/O，从而拖慢整个数据库的性 能。在一个有序的表中，如果查询返回少于40% 的行，或者在一个无序的表中，返回少于7%的行 ，那么这个查询都可以调整为使用一个索引来代 替全表搜索。对于不必要的全表搜索来说，最常 见的调优方法是增加索引。可以在表中加入标准 的B树索引，也可以加入bitmap和基于函数的索引 。索引的类型 B-tree （默认索引类型）B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可 以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括 32列。树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从 两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。 Hash 索引只使用于等值查询，返回的结果是无序的。 位图适合于跨度小的列 函数 反向键 降序索引索引的使用1.唯一定位（主键，唯一索引）INDEX UNIQUE SCAN 2.范围扫描 INDEX range SCAN 3.全扫索引（full scan) -有序的读取，走链表查看的列处于索引列，将会只扫描索引，不会扫描表。 4.快速全扫（fast full scan) -无序的读取，读高水位 一下 5.降序使用索引 6.索引的位图运算 7.索引的连接 8.跳跃扫描使用索引的目的 减少I/O操作 消除磁盘排序 加快查询速度用索引提高效率 索引是表的一个概念部分，用来提高检索数据的效率。 实际上， ORACLE使用了一个复杂的自平衡B-tree结构。通常，通过索引查询数 据比全表扫描要快。 当ORACLE找出执行查询和Update语句的最佳路 径时， ORACLE优化器将使用索引。同样在联结多个表时使用索引也 可以提高效率。 另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。除了那些LONG或LONG RAW数据类型， 你可以索引几乎所有的列。 通常， 在大型表中使用索引特别有效。 当然，你也会发现， 在扫描 小表时，使用索引同样能提高效率。虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的 代价。 索引需要空间来存储，也需要定期维护，每当有记录在表中增 减或索引列被修改时， 索引本身也会被修改。 这意味着每条记录的 INSERT ， DELETE ， UPDATE将为此多付出4 ， 5 次的磁盘I/O . 因为索 引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应 时间变慢。定期的重构索引是有必要的。ALTER INDEXREBUILDSQL语句索引的利用采用函数处理的字段不能利用索引，如： substr(empno,1,3)=790，优化处理：empno like 790%进行了显式或隐式的运算的字段不能进行索引， 如 sal+10003000，优化处理：ss_df2000 X | hbs_bhX5400021452，优化处理：hbs_bh5400021542 hiredate+5=sysdate，优化处理：hiredate=sysdate-5条件内包括了多个本表的字段运算时不能进行索引，如：ys_dfcx_df 无法进行优化选择最有效率的表名顺序(只在基于 规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句 中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个 表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个 表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句 中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中最后第 二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行 合并。例如：表 TAB1 16，384 条记录表 TAB2 1 条记录选择TAB2作为基础表 (最好的方法)select count(*) from tab1，tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法) select count(*) from tab2，tab1 执行时间26.09秒 选择最有效率的表名顺序(续)如果有3个以上的表连接查询， 那就需要选择交叉表(intersection table)作为基础 表， 交叉表是指那个被其他表所引用的表。例如： EMP表描述了LOCATION表和CATEGORY表的交集。SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN 将比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000 SELECT子句中避免使用 “ * ” 当你想在SELECT子句中列出所有的COLUMN 时，使用动态SQL列引用 * 是一个方便的 方法。不幸的是，这是一个非常低效的方 法。实际上，ORACLE在解析的过程中， 会 将* 依次转换成所有的列名， 这个工作是 通过查询数据字典完成的， 这意味着将耗 费更多的时间。用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避 免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换例如：低效：SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM D