ORACLE SQL性能优化系列 (十二)

原创 2003年09月03日 10:34:00

39.       总是使用索引的第一个列<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

如果索引是建立在多个列上, 只有在它的第一个列(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'

  

很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

 

 

40.       ORACLE内部操作

当执行查询时,ORACLE采用了内部的操作. 下表显示了几种重要的内部操作.

ORACLE Clause

内部操作

ORDER BY

SORT ORDER BY

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />UNION

UNION-ALL

MINUS

MINUS

INTERSECT

INTERSECT

DISTINCT,MINUS,INTERSECT,UNION

SORT UNIQUE

MIN,MAX,COUNT

SORT AGGREGATE

GROUP BY

SORT GROUP BY

ROWNUM

COUNT or COUNT STOPKEY

Queries involving Joins

SORT JOIN,MERGE JOIN,NESTED LOOPS

CONNECT BY

CONNECT BY

 

 

41.       UNION-ALL 替换UNION ( 如果有可能的话)

 

SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.

如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

 

举例:

   低效:

    SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

高效:

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION ALL

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

 

译者按:

需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是

要从业务需求分析使用UNION ALL的可行性.

UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这

块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量

 

Select substr(name,1,25)  "Sort Area Name",

       substr(value,1,15)   "Value"

from v$sysstat

where name like 'sort%'

   

42.       使用提示(Hints)

对于表的访问,可以使用两种Hints.

FULL ROWID

 

FULL hint 告诉ORACLE使用全表扫描的方式访问指定表.

例如:

   SELECT /*+ FULL(EMP) */ *

   FROM EMP

   WHERE EMPNO = 7893;

 

   ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表.

 

   通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式, 你需要知道ROIWD的值或者使用索引.

   如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留

SGA,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA. 通常CACHE hint FULL hint 一起使用.

例如:

SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *

FROM WORK;

 

   索引hint 告诉ORACLE使用基于索引的扫描方式. 你不必说明具体的索引名称

例如:

   SELECT /*+ INDEX(LODGING) */ LODGING

   FROM LODGING

   WHERE MANAGER = ‘BILL GATES’;

  

   在不使用hint的情况下, 以上的查询应该也会使用索引,然而,如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引. 在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引.

 

   ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.

  

译者按:

   使用hint , 表示我们对ORACLE优化器缺省的执行路径不满意,需要手工修改.

这是一个很有技巧性的工作. 我建议只针对特定的,少数的SQL进行hint的优化.

ORACLE的优化器还是要有信心(特别是CBO)

 

ORACLE SQL性能优化系列(十二)

25.       用索引提高效率 索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACL...
  • hzfu007
  • hzfu007
  • 2006年09月13日 17:48
  • 607

ORACLE SQL性能优化系列 (十一) (十二)

36.       用UNION替换OR (适用于索引列)通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有...
  • cwwhy
  • cwwhy
  • 2005年06月27日 13:06
  • 803

ORACLE SQL性能优化汇总

ORACLE SQL性能优化汇总:SQL语句共享、ORACLE SQL 多表联合查询、where语句条件顺序、ORACEL多表连接查询指定表别名alias等...
  • tianwei7518
  • tianwei7518
  • 2015年03月11日 23:41
  • 1022

高级SQL优化(二) ——《12年资深DBA教你Oracle开发与优化——性能优化部分》

充分利用索引 索引的限制 1. 索引对不等号和NOT的限制   如果WHERE条件中出现!=或者 Oracle 10g起,在基于CBO的优化器模式下Oralce会进行自动优...
  • holandstone
  • holandstone
  • 2016年05月21日 23:36
  • 2015

ORACLE SQL性能优化系列

 选择最有效率的表名顺序(只在基于规则的优化器中有效)        ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table...
  • meteorlWJ
  • meteorlWJ
  • 2008年06月16日 13:36
  • 534

ORACLE SQL性能优化系列

ORACLE SQL性能优化系列1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本)c. CHOOSE (选择性) 设置缺省的...
  • xianbin
  • xianbin
  • 2006年11月19日 14:55
  • 787

ORACLE SQL性能优化No3--ORACLE SQL性能优化系列

1. 选用适合的ORACLE优化器      ORACLE的优化器共有3种:     a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)      设置...
  • elimago
  • elimago
  • 2007年07月02日 12:31
  • 790

Oracle查询性能优化(面试题:数据库查询优化也是常问的问题)

原则一:注意WHERE子句中的连接顺序:  ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WH...
  • hxpjava1
  • hxpjava1
  • 2017年02月21日 11:40
  • 1197

算法系列之二十二:离散傅立叶变换之听声音破解电话号码

利用离散傅立叶变换,将电话拨号音从时域信号转换成频域信号,然后通过频率分析找出对应的双音频频率组合,从而确定是哪个按键的拨号音...
  • orbit
  • orbit
  • 2013年12月09日 00:43
  • 22906

Oracle 数据库性能优化3日实战(企业培训)

课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述...
  • robinson_0612
  • robinson_0612
  • 2017年04月05日 11:49
  • 2488
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE SQL性能优化系列 (十二)
举报原因:
原因补充:

(最多只允许输入30个字)