在Oracle优化中所用到的语句有哪些

数据库 Oracle
以下的文章主要描述的是Oracle优化的查询,本文章主要是通过实际操作代码的示例还有相关实例的解析的方式来说明其原理。

我们今天是要和大家一起讨论的是Oracle优化的查询,我前两天在相关网站看见的资料,觉得挺好,就拿出来供大家分享。如果你对Oracle优化的查询,心存好奇的话,以下的文章将会揭开它的神秘面纱。

假设LARGE_TABLE是一个较大的表,且username列上没有索引,则运行下面的语句:

  1. SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST';   
  2. Query Plan   
  3. SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)   
  4. TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED] 

在这个例子中,TABLE ACCESS FULL LARGE_TABLE是***个操作,意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后,产生的row source中的数据被送往下一步骤进行处理,在此例中,SELECT STATEMENT操作是这个查询语句的***一步。

Optimizer=CHOOSE 指明这个查询的optimizer_mode,即optimizer_mode初始化参数指定的值,它并不是指语句执行时真的使用了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部分。例如,如果给出的是下面的形式,则表明使用的是CBO优化器,此处的cost表示Oracle优化器认为该执行计划的代价:

 

  1. SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14

然而假如执行计划中给出的是类似下面的信息,则表明是使用RBO优化器,因为cost部分的值为空,或者压根就没有cost部分。

  1. SELECT STATEMENT Optimizer=CHOOSE Cost=   
  2. SELECT STATEMENT Optimizer=CHOOSE 

这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了什么样的Oracle优化器。特别的,如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n,则使用的是CBO优化器;如果Optimizer=RULE,则使用的是RBO优化器。

cost属性的值是一个在Oracle内部用来比较各个执行计划所耗费的代价的值,从而使优化器可以选择***的执行计划。不同语句的cost值不具有可比性,只能对同一个语句的不同执行计划的cost值进行比较。

[:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的,以便该操作可以区别于串行执行的操作。

[ANALYZED] 表明操作中引用的对象被分析过了,在数据字典中有该对象的统计信息可以供CBO使用。

例2:

假定A、B、C都是不是小表,且在A表上一个组合索引:A(a.col1,a.col2) ,注意a.col1列为索引的引导列。考虑下面的查询:

  1. select A.col4   
  2. from A , B , C   
  3. where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5   
  4. Execution Plan   
  5. SELECT STATEMENT Optimizer=CHOOSE   
  6. MERGE JOIN   
  7. SORT (JOIN)   
  8. NESTED LOOPS   
  9. TABLE ACCESS (FULL) OF 'B'   
  10. TABLE ACCESS (BY INDEX ROWID) OF 'A'   
  11. INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
  12. SORT (JOIN)   
  13. TABLE ACCESS (FULL) OF 'C'   
  14. Statistics   
  15. 0 recursive calls   
  16. 8 db block gets   
  17. 6 consistent gets   
  18. 0 physical reads   
  19. 0 redo size   
  20. 551  bytes sent via SQL*Net to client   
  21. 430  bytes received via SQL*Net from client   
  22. 2 SQL*Net roundtrips to/from client   
  23. 2 sorts (memory)   
  24. 0 sorts (disk)   
  25. 6 rows processed  

 

在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为B与A先连接,然后再与C连接:
B  <--->  A  <--->  C
col3=10  col3=5

如果没有执行计划,分析一下,上面的3个表应该拿哪一个作为***个驱动表?从SQL语句看来,只有B表与C表上有限制条件,所以***个驱动表应该为这2个表中的一个,到底是哪一个呢?

B表有谓词B.col3 = 10,这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上,从而得到一个较小的row source, 所以B表应该作为***个驱动表。而且这样的话,如果再与A表做关联,可以有效利用A表的索引(因为A表的col1列为leading column)。

当然上面的查询中C表上也有谓词(C.col3 = 5),有人可能认为C表作为***个驱动表也能获得较好的性能。让我们再来分析一下:如果C表作为***个驱动表,则能保证驱动表生成很小的row source,但是看看连接条件A.col2 = C.col2,此时就没有机会利用A表的索引,因为A表的col2列不为leading column,这样nested loop的效率很差,从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。

因此上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的Oracle优化器,它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说,CBO都会选择正确的连接顺序,如果CBO选择了比较差的连接顺序,我们还可以使用Oracle提供的hints来让CBO采用正确的连接顺序。如下所示:

 

  1. select /*+ ordered */ A.col4   
  2. from B,A,C   
  3. where B.col3 = 10   
  4. and A.col1 = B.col1   
  5. and A.col2 = C.col2   
  6. and C.col3 = 5 

既然选择正确的驱动表这么重要,那么让我们来看一下执行计划,到底各个表之间是如何关联的,从而得到执行计划中哪个表应该为驱动表:在执行计划中,需要知道哪个操作是先执行的,哪个操作是后执行的,这对于判断哪个表为驱动表有用处。

判断之前,如果对表的访问是通过rowid,且该rowid的值是从索引扫描中得来得,则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中,判断执行顺序的指导原则就是:最右、最上的操作先执行。具体解释如下:

得到去除妨碍判断的索引扫描后的执行计划:

  1. Execution Plan  
  2. SELECT STATEMENT Optimizer=CHOOSE   
  3. MERGE JOIN   
  4. SORT (JOIN)   
  5. NESTED LOOPS   
  6. TABLE ACCESS (FULL) OF 'B'   
  7. TABLE ACCESS (BY INDEX ROWID) OF 'A'   
  8. SORT (JOIN)   
  9. TABLE ACCESS (FULL) OF 'C'  
  10.  

 

看执行计划的第3列,即字母部分,每列值的左面有空格作为缩进字符。在该列值左边的空格越多,说明该列值的缩进越多,该列值也越靠右。如上面的执行计划所示:***列值为6的行的缩进最多,即该行最靠右;***列值为4、5的行的缩进一样,其靠右的程度也一样,但是***列值为4的行比***列值为5的行靠上;谈论上下关系时,只对连续的、缩进一致的行有效。

从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为***个驱动表(外部表),自然,A表就为内部表了。

从图中还可以看出,B与A表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row source与C表做排序—合并连接。

上述的相关内容就是对Oracle优化查询的描述,希望会给你带来一些帮助在此方面。

文章出自:http://www.programbbs.com/doc/5047.htm

【编辑推荐】

  1. OraclePL编程语言的优点介绍
  2. Oracle导出某些用户中所有表的实际操作方法
  3. 不为人知的Oracle控制文件重建
  4. Oracle数据库的四种基本的启动方式
  5. 如何配置Oracle数据库
责任编辑:佚名 来源: programbbs
相关推荐

2010-05-05 10:37:40

Oracle优化查询

2010-04-27 09:38:57

Oracle修改表ow

2010-05-12 10:17:59

MySQL数据库优化

2010-05-10 18:38:08

Oracle分页语句

2010-05-05 09:33:46

Oracle事务

2017-08-07 15:52:33

Oracleonnect by优化

2010-04-13 15:04:16

Oracle优化

2010-03-29 13:33:27

Oracle模式

2009-01-04 17:41:07

2011-08-11 17:17:56

Java

2009-03-04 09:06:56

优化sqlOracle

2010-04-20 15:58:15

Oracle 语句

2010-04-27 10:32:54

Oracle优化CPU

2010-04-15 10:34:16

Oracle程序开发

2010-06-03 09:39:24

优化MySQL性能

2012-11-14 11:07:24

网络优化

2010-06-13 15:42:37

MySQL性能优化

2010-05-13 09:14:44

MySQL数据库

2010-03-22 09:25:57

Python学习笔记

2010-03-16 15:38:57

Python串口
点赞
收藏

51CTO技术栈公众号