图片 6

Oracle编程入门经典 第6章 在Oracle中处理语句

工作原理

CBO在决定方案的时候会考虑对象的规模。从RBO和CBO的AUTOTRACE输出中可以发现一个有意思的现象是,CBO方案包含了更多的信息。在CBO生成的方案中,将会看到的内容有:

  • COST——赋予这个步骤的查询方案的数量值。它是CBO比较相同查询的多个备选方案的相对开销,寻找具有最低整体开销的方案时所使用的内部数值。
  • CARD——这个步骤的核心数据,换句话说,就是这个步骤将要生成的行的估计数量。例如,可以发现DEPT的TABLE
    ACCESS(FULL)估计要返回4条记录,因为DEPT表只有4条记录,所以这个结果很正确。
  • BYTES——方案中的这个步骤气概生成的数据的字节数量。这是附属列集合的平均行大小乘以估计的行数。

用户将会注意到,当使用RBO的时候,我们无法看到这个信息,因此这是一种查看所使用优化器的方法。

如果我们“欺骗”CBO,使其认为这些表比它们实际的要大,就可以得到不同的规模和当前统计信息。

1 SELECT DISTINCT
DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO

6.2.3          行源生成器

行源生成器是Oracle的软件片段,它可以从优化器获取输出,并且将其格式化为的执行方案。例如,在这部分之前我们看到了SQL*Plus中的AUTOTRACE工具所生成的查询方案。那个树状结构的方案就是行源生成器的输出;优化器会生成方案,而行源生成器会将其转换成为Oracle系统的其余部分可以使用的数据结构。

1 SELECT
DEPT_CODE FROM DEPT
WHERE DEPT_TYPE
> 0

6.1     SQL语句类别

  • DDL:数据定义语言语句。这样的语句有CREATE、TRUNCATE和ALTER,它们用于建立数据库中的结构,设置许可等。用户可以使用它们维护Oracle数据词典。
  • DML:数据操作语言语句。这些语句可以修改或者访问信息,包括INSERT、UPDATE和DELETE。
  • 查询:这是用户的标准SELECT语句。查询是指那么返回数据但是不修改数据的语句,是DML语句的子集。

1 SELECT
JOB,AVG(SAL)FROM EMP GROUP by JOB HAVING JOB= ‘PRESIDENT’ OR JOB = ‘MANAGER’

6.2.1          解析

这是Oracle中任何语句处理过程的第一个步骤。解析(parsing)是将已经提交的语句分解,判定它是哪种类型的语句(查询、DML或者DDL),并且在其上执行各种检验操作。

解析过程会执行三个主要的功能:

  • 语法检查。这个语句是正确表述的语句么?它符合SQL参考手册中记录的SQL语法么?它遵循SQL的所有规则么?
  • 语义分析。这个语句是否正确参照了数据库中的对象,它所引用的表和列存在么?用户可以访问这些对象,并且具有适当的特权么?语句中有歧义么?。
  • 检查共享池。这个语句是否已经被另外的会话处理?

以下便是语法错误:

SQL> select from where 2;

select from where 2

       *

ERROR 位于第 1 行:

ORA-00936: 缺少表达式

总而言之,如果赋予正确的对象和特权,语句就可以执行,那么用户就遇到了语义错误;如果语句不能够在任何环境下执行,那么用户就遇到了语法错误。

解析操作中的下一步是要查看我们正在解析的语句是否牵线
些会话处理过。如果处理过,那么我们就很幸运,因为它可能已经存储于共享池。在这种情况下,就可以执行软解析(soft
parse),换句话说,可以避免优化和查询方案生成阶段,直接进入执行阶段。这将极大地缩短执行查询的过程。另一方面,如果我们必须对查询进行解析、优化和生成执行方案,那么就要执行所谓的硬解析(hard
parse)。这种区别十分重要。当开发应用的时候,我们会希望有非常高的比例的查询进行软解析,以跳过优化/生成阶段,因为这些阶段非常占用CPU。如果我们必须硬解析大量的查询,那么系统就会运行得非常缓慢。

  1. ### Oracle怎样使用共享池

正如我们已经看到的,当Oracle解析了查询,并且通过了语法和语义检查之后,就会查看SGA的共享池组件,来寻找是否有另外的会话已经处理过完全相同的查询。为此,当Oracle接收到我们的语句之后,就会对其进行散列处理。散列处理是获取原始SQL文本,将其发往一下函数,并且获取一个返回编号的过程。如果我们访问一些V$表,就可以实际看到这些V$表在Oracle中称为动态性能表(dynamic
performance tables),服务器会在那里为我们存储一些有用的信息。

可能通过如下方式实现访问V$表:

为用户账号赋予SELECT_CATALOG_ROLE

使用另一个具有SELECT_CATALOG_ROLE的角色(例如DBA)

如果用户不能访问V$表以及V$SQL视图,那么用户就不能完成所有的“试验”,但是掌握所进行的处理非常容易。

1 SELECT
LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID= 10 OR REGION = ‘MELBOURNE’

试验:观察不同的散列值

(1)    首先,我们将要执行2个对大家来讲意图和目的都相同的查询:

SQL> select * from dual;

D

-

X

SQL> select * from DUAL;

D

-

X

(2)   
我们可以查询动态性能视图V$SQL来查看这些内容,它可以向我们展示刚才运行的2个查询的散列值:

SQL> select sql_text,hash_value from v$sql

  2  where upper(sql_text)='SELECT * FROM DUAL';

SQL_TEXT

------------------------------------------------

HASH_VALUE

----------

select * from DUAL

1708540716

select * from dual

4035109885

通常不需要实际查看散列值,因为它们在Oracle内部使用。当生成了这些值之后,Oracle就会在共享池中进行搜索,寻找具有相同散列值的语句。然后将它找到的SQL_TEXT与用户提交的SQL语句进行比较,以确保共享池中的文本完全相同。这个比较步骤很重要,因为散列函数的特性之一就是2个不同的字符串也可能散列为相同的数字。

注意:

散列不是字符串到数字的唯一映射。

总结到目前为止我们所经历的解析过程,Oracle已经:

  • 解析了查询
  • 检查了语法
  • 验证了语义
  • 计算了散列值
  • 找到了匹配
  • 验证与我们的查询完全相同的查询(它引用了相同的对象)

在Oracle从解析步骤中返回,并且报告已经完成软解析之前,还要执行最后一项检查。最后的步骤就是要验证查询是否是在相同的环境中解析。环境是指能够影响查询方案生成的所有会话设置,例如SORT_AREA_SIZE或者OPTIMIZER_MODE。SORT_AREA_SIZE会通知Oracle,它可以在不使用磁盘存储临时结果的情况下,为排序数据提供多少内存。圈套的SORT_AREA_SIZE会生成与较小的设置不同的优化查询方案。例如,Oracle可以选择一个排序数据的方案,而不是使用索引读取数据的方案。OPTIMIZER_MODE可以通知Oracle实际使用的优化器。

SQL> alter session set OPTIMIZER_MODE=first_rows;

会话已更改。

SQL> select * from dual;

D

-

X

SQL> select sql_text,hash_value,parsing_user_id

  2  from v$sql

  3  where upper(sql_text)='SELECT * FROM DUAL'

  4  /

SQL_TEXT

-------------------------------------------------

HASH_VALUE PARSING_USER_ID

---------- ---------------

select * from DUAL

1708540716               5

select * from dual

4035109885               5

select * from dual

4035109885               5

这2个查询之间的区别是第一个查询使用默认的优化器(CHOOSE),刚才执行的查询是在FIRST_ROWS模式中解析。

SQL> select sql_text,hash_value,parsing_user_id,optimizer_mode

  2  from v$sql

  3  where upper(sql_text)='SELECT * FROM DUAL'

  4  /

SQL_TEXT

--------------------------------------------------------------

HASH_VALUE PARSING_USER_ID OPTIMIZER_

---------- --------------- ----------

select * from DUAL

1708540716               5 CHOOSE

select * from dual

4035109885               5 CHOOSE

select * from dual

4035109885               5 FIRST_ROWS

在这个阶段的最后,当Oracle完成了所有工作,并且找到了匹配查询,它就可以从解析过程中返回,并且报告已经进行了一个软解析。我们无法看到这个报告,因为它由Oracle在内部使用,来指出它现在完成了解析过程。如果没有找到匹配查询,就需要进行硬解析。

两者的区别在于, 前者DBMS 将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPT NO=3的记录并且向前扫描到第一个DEPT 大于3的记录.
5、LIKE操作符
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE’X5400%’则会引用范围索引。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE’%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE
‘X5400%’ OR YY_BH LIKE ‘B5400%’
则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。

6.4     DML全过程

现在,我们要讨论怎样处理修改的数据库的DML语句。我们将要讨论怎样生成REDO和UNDO,以及怎样将它们用于DML事务处理及其恢复。

作为示例,我们将会分析如下事务处理会出现的情况:

INSERT INTO T(X,Y) VALUES (1,1);

UPDATE T SET X=X+1 WHERE X=1;

DELETE FROM T WHERE X=2;

最初对T进行的插入将会生成REDO和UNDO。如果需要,为了对ROLLBACK语句或者故障进行响应,所生成的UNDO数据将会提供足够的信息让INSERT“消失”。如果由于系统故障要再次进行操作,那么所生成的UNDO数据将会为插入“再次发生”提供足够的信息。UNDO数据可能会包含许多信息。

所以,在我们执行了以上的INSERT语句之后(还没有进行UPDATE或者DELETE)。我们就会拥有一个如图6-2所示的状态。

 图片 1

图6-2 执行INSERT语句之后的状态

这里有一些已经缓存的,经过修改的UNDO(回滚)数据块、索引块,以及表数据块。所有这些都存储在数据块缓存中。所有这些经过修改的数据块都会由重做日志缓存中的表项保护。所有这些信息现在都受到缓存。

现在来考虑一个在这个阶段出现系统崩溃的场景。SGA会受到清理,但是我们实际上没有使用这里列举的项,所以当我们臭不可闻启动的时候,就好像这个事务处理过程从来没有发生过样。所有发生改变的数据块都没有写入磁盘,REDO信息也没有写入磁盘。

在另一个场景中,缓存可能已经填满。在这种情况下,DBWR必须要腾出空间,清理我们已经改变的数据块。为了完成这项工作,DBWR首先会要求LGWR清理保护数据库数据块的REDO块。

注意:

在DBWR将已经改变的数据块定稿磁盘之前,LGWR必须清理与这些数据块相关联的REDO信息。

在我们的处理过程中,这时要清理重做日志缓存(Oracle会反复清理这个缓存),缓存中的一些改变也要写入磁盘。在这种情况下,即如图6-3所示。

 图片 2

图6-3 清理重做日志缓存的状态

接下来,我们要进行UPDATE。这会进行大体相同的操作。这一次,UNDO的数据将会更大,我们会得到图6-4所示情况。

 图片 3

图6-4 UPDATE图示

我们已经将更多的新UNDO数据块增加到了缓存中。已经修改了数据库表和索引数据块,所以我们要能够在需要的时候UNDO(撤销)已经进行的UPDATE。我们还生成了更多的重做日志缓存表项。到目前为止,已经生成的一些重做日志表项已经存入了磁盘,还有一些保留在缓存中。

现在,继续DELETE。这里会发生大体相同的情况。生成UNDO,修改数据块,将REDO发往重做日志缓存。事实上,它与UPDATE非常相似,我们要对其进行COMMIT,在这里,Oracle会将重做日志缓存清理到磁盘上,如图6-5所示。

 图片 4

图6-5 DELETE操作后图示

有一些已经修改的数据块保留在缓存中,还有一些可能会被清理到磁盘上。所有可以重放这个事务处理的REDO信息都会安全地放在磁盘上,现在改变已永久生效。

2、NOT
IN操作符

6.2.2          优化

当重用SQL的时候,可以路过这个步骤,但是每个特有的查询/DML语句都要至少实现一次优化。

优化器的工作表面上看起来简单,它的目标就是找到最好的执行用户查询的途径,尽可能地优化代码。尽管它的工作描述非常简单,但是实际上所完成的工作相当复杂。执行查询可能会有上千种的方式,它必须找到最优的方式。为了判断哪一种查询方案最适合:Oracle可能会使用2种优化器:

  • 基于规则的优化器(Rule Based
    Optimizer,RBO)——这种优化器基于一组指出了执行查询的优选方法的静态规则集合来优化查询。这些规则直接编入了Oracle数据库的内核。RBO只会生成一种查询方案,即规则告诉它要生成的方案。
  • 基于开销的优化器(Cost Based
    Optimizer,CBO)——这种优化器人基于所收集的被访问的实际数据的统计数据来优化查询。它在决定最优方案的时候,将会使用行数量、数据集大小等信息。CBO将会生成多个(可能上千个)可能的查询方案,解决查询的备选方式,并且为每个查询方案指定一个数量开销。具有最低开销的查询方案将会被采用。

OPTIMIZER_MODE是DBA能够在数据库的初始化文件中设定的系统设置。默认情况下,它的值为CHOOSE,这可以让Oracle选取它要使用的优化器(我们马上就会讨论进行这种选择的规则)。DBA可以选择覆盖这个默认值,将这个参数设置为:

  • RULE:规定Oracle应该在可能情况下使用RBO。
  • FIRST_ROWS:Oracle将要使用CBO,并且生成一个尽可能快地获取查询返回的第一行的查询方案。
  • ALL_ROWS:Oracle将要使用CBO,并且生成一个尽可能快地获取查询所返回的最后一行(也就获得所有的行)的查询方案。

正如我们在上面看到的,可以通过ALTER
SESSION命令在会话层次覆写这个参数。这对于开发者希望规定它们想要使用的优化器以及进行测试的应用都非常有用。

现在,继续讨论Oracle怎样选择所使用的优化器,及其时机。当如下条件为真的时候,Oracle就会使用CBO:

  • 至少有一个查询所参考的对象存在统计数据,而且OPTIMIZER_MODE系统或者会话参数没有设置为RULE。
  • 用户的OPTIMIZER_MODE系统/会话参数设置为RULE或者CHOOSE以外的值。
  • 用户查询要访问需要CBO的对象,例如分区表或者索引组织表。
  • 用户查询包含了RULE提示(hint)以外的其它合法提示。
  • 用户使用了只有CBO才能够理解的特定的SQL结构,例如CONNECT BY。

目前,建议所有的应用都使用CBO。自从Oracle第一次发布就已经使用的RBO被认为是过时的查询优化方法,使用它的时候很多新特性都无法利用。例如,如果用户想要使用如下特性的时候,就不能够使用RBO:

  • 分区表
  • 位图索引
  • 索引组织表
  • 规则的细粒度审计
  • 并行查询操作
  • 基于函数的索引

CBO不像RBO那样容易理解。根据定义,RBO会遵循一组规则,所以非常容易预见结果。而CBO会使用统计数据来决定查询所使用的方案。

为了分析和展示这种方法,可以使用一个简单的救命。我们将会在SQL*Plus中,从SCOTT模式复制EMP和DEPT表,并且向这些表增加主键/外键。将会使用SQL*Plus产品中内嵌工具AUTOTRACE,比较RBO和CBO的方案。

1 grant
plustrace to
username;

6.5     DDL处理

最后,我们来讨论Oracle怎样处理DDL。DDL是用户修改Oracle数据词典的方式。为了建立表,用户不能编写INSERT
INTO USER_TABLES语句,而是要使用CREATE
TABLE语句。在后台,Oracle会为用户使用大量的SQL(称为递归SQL,这些SQL会对其它SQL产生副作用)。

执行DDL活动将会在DDL执行之前发出一个COMMIT,并且在随后立即使用一个COMMIT或者ROLLBACK。这就是说,DDL会像如下伪码一样执行:

COMMIT;

DDL-STATEMENT;

IF (ERROR) THEN

    ROLLBACK;

ELSE

    COMMIT;

END IF;

用户必须注意,COMMIT将要提交用户已经处理的重要工作——即,如果用户执行:

INSERT INTO SOME_TABLE VALUES(‘BEFORE’);

CREATE TABLE T(X INT );

INSERT INTO SOME_TABLE VALUES(‘AFTER’);

ROLLBACK;

由于第一个INSERT已经在Oracle尝试CREATE
TABLE语句之前进行了提交,所以只有插入AFTER的行会进行回滚。即使CREATE
TABLE失败,所进行的BEFORE插入也会提交。

1 SELECT * FROM EMP
(基础表) WHERE EMPNO > 0 AND EXISTS
  (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC=’MELB’)

试验:比较优化器

(1)    用户确保作为SCOTT以外的其它用户登录到数据库上,然后使用CREATE
TABLE命令复制SCOTT.EMP和SCOTT.DEPT表:

SQL> create table emp

  2  as

  3  select * from scott.emp;

表已创建。

SQL> create table dept

  2  as

  3  select * from scott.dept;

表已创建。

(2)    向EMP和DEPT表增加主键

SQL> alter table emp

  2  add constraint emp_pk primary key(empno);

表已更改。

SQL> alter table dept

  2  add constraint dept_pk primary key(deptno);

表已更改。

(3)    添加从EMP到DEPT的外键

SQL> alter table emp

  2  add constraint emp_fk_dept

  3  foreign key(deptno) references dept;

表已更改。

(4)   
SQL*Plus中启用AUTOTRACE工具。我们正在使用的AUTOTRACE命令会向我们展示Oracle可以用来执行查询经过优化的查询方案(它不会实际执行查询):

SQL> set autotrace traceonly explain

如果启动失败,解决方法如下:

SQL> set autotrace traceonly explain

SP2-0613: 无法验证 PLAN_TABLE 格式或实体

SP2-0611: 启用EXPLAIN报告时出错

解决方法:

1.以当前用户登录

SQL> connect zhyongfeng/zyf@YONGFENG as sysdba;

已连接。

2.运行utlxplain.sql(在windows的C:\oracle\ora92\rdbms\admin下),即创建PLAN_TABLE

SQL> rem

SQL> rem $Header: utlxplan.sql 29-oct-2001.20:28:58 mzait Exp $ xplainpl.sql

SQL> rem

SQL> Rem Copyright (c) 1988, 2001, Oracle Corporation.  All rights reserved. 

SQL> Rem NAME

SQL> REM    UTLXPLAN.SQL

SQL> Rem  FUNCTION

SQL> Rem  NOTES

SQL> Rem  MODIFIED

SQL> Rem     mzait      10/26/01  - add keys and filter predicates to the plan table

SQL> Rem     ddas       05/05/00  - increase length of options column

SQL> Rem     ddas       04/17/00  - add CPU, I/O cost, temp_space columns

SQL> Rem     mzait      02/19/98 -  add distribution method column

SQL> Rem     ddas       05/17/96 -  change search_columns to number

SQL> Rem     achaudhr   07/23/95 -  PTI: Add columns partition_{start, stop, id}

SQL> Rem     glumpkin   08/25/94 -  new optimizer fields

SQL> Rem     jcohen     11/05/93 -  merge changes from branch 1.1.710.1 - 9/24

SQL> Rem     jcohen     09/24/93 - #163783 add optimizer column

SQL> Rem     glumpkin   10/25/92 -  Renamed from XPLAINPL.SQL

SQL> Rem     jcohen     05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)

SQL> Rem     rlim       04/29/91 -         change char to varchar2

SQL> Rem   Peeler     10/19/88 - Creation

SQL> Rem

SQL> Rem This is the format for the table that is used by the EXPLAIN PLAN

SQL> Rem statement.  The explain statement requires the presence of this

SQL> Rem table in order to store the descriptions of the row sources.

SQL>

SQL> create table PLAN_TABLE (

  2   statement_id  varchar2(30),

  3   timestamp     date,

  4   remarks       varchar2(80),

  5   operation     varchar2(30),

  6   options        varchar2(255),

  7   object_node   varchar2(128),

  8   object_owner  varchar2(30),

  9   object_name   varchar2(30),

 10   object_instance numeric,

 11   object_type     varchar2(30),

 12   optimizer       varchar2(255),

 13   search_columns  number,

 14   id  numeric,

 15   parent_id numeric,

 16   position numeric,

 17   cost  numeric,

 18   cardinality numeric,

19   bytes  numeric,

 20   other_tag       varchar2(255),

 21   partition_start varchar2(255),

 22          partition_stop  varchar2(255),

 23          partition_id    numeric,

 24   other  long,

 25   distribution    varchar2(30),

 26   cpu_cost numeric,

 27   io_cost  numeric,

 28   temp_space numeric,

 29          access_predicates varchar2(4000),

 30          filter_predicates varchar2(4000));

3.将plustrace赋给用户(因为是当前用户,所以这步可省略)

SQL> grant all on plan_table to zhyongfeng;

授权成功。

4.通过执行plustrce.sql(C:\oracle\ora92\sqlplus\admin\
plustrce.sql),如下

SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql;

会有以下结果:

SQL> create role plustrace;

角色已创建

SQL>

SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$session to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>

SQL> set echo off

5.授权plustrace到用户(因为是当前用户,这步也可以省略)

SQL> grant plustrace to zhyongfeng;

授权成功。

(5)    启用了AUTORACE,在我们的表上运行查询:

SQL> set autotrace on;

SQL> set autotrace traceonly explain;

SQL> select * from emp,dept

  2  where emp.deptno=dept.deptno;



Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

   4    3       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

由于没有收集任何统计信息(这是新建立的表),所以我们当前在这个例子中要使用RBO;我们无法访问任何需要CBO的特殊对象,我们的优化器目标要设置为CHOOSE。我们也能够从输出中表明我们正在使用RBO。在这里,RBO优化器会选择一个将要在EMP表上进行FULL
SCAN的方案。为了执行连接,对于在EMP表中找到的每一行,它都会获取DEPTNO字段,然后使用DEPT_PK索引寻找与这个DEPTNO相匹配的DEPT记录。

如果我们简单分析已有的表(目前它实际上非常小),就会发现通过使用CBO,将会获得一个非常不同的方案。

注意:

一、操作符优化:

试验:比较优化器2

为了完成这个试验,我们将要使用称为DBMS_STATS的补充程序包。通过使用这个程序包,就可以在表上设置任意统计(可能要完成一些测试工作,分析各种环境下的生成方案)。

(1)   
我们使用DBMS_STATS来欺骗CBO,使其认为EMP表具有1000万条记录,DEPT表具有100万条记录:

SQL> begin

  2  dbms_stats.set_table_stats

  3  (user,'EMP',numrows=>10000000,numblks=>1000000);

  4  dbms_stats.set_table_stats

  5  (user,'DEPT',numrows=>1000000,numblks=>100000);

  6  end;

  7  /

PL/SQL 过程已成功完成。

(2)    我们将要执行与前面完全相同的查询,查看新统计信息的结果:

SQL> select * from emp,dept

  2  where emp.deptno=dept.deptno;



Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79185 Card=200000000

          0000 Bytes=100000000000000)



   1    0   HASH JOIN (Cost=79185 Card=2000000000000 Bytes=10000000000

          0000)



   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=6096 Card=1000000 By

          tes=18000000)



   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=60944 Card=10000000 B

          ytes=320000000)

用户可以发现,优化器选择了完全不同于以前的方案。它不再散列这些明显很大的表,而是会MERGE(合并)它们。对于较小的DEPT表,它将会使用索引排序数据,由于在EMP表的DEPTNO列上没有索引,为了将结果合并在一起,要通过DEPTNO排序整个EMP。

(3)   
如果将OPTIMIZER_MODE参数设置为RULE,就可以强制使用RBO(即使我们有这些统计数据),可以发现它的行为是完全可以预期的:

SQL> alter session set OPTIMIZER_MODE=RULE;

会话已更改。


SQL> select * from emp,dept

  2  where emp.deptno=dept.deptno;


Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=RULE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

   4    3       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

注意:

无论附属表中的数据数量如何,如果给定相同的数据对象集合(表和索引),RBO每次都会生成完全相同的方案。

1 SELECT
LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10
  UNION SELECT LOC_ID , LOC_DESC
, REGION FROM
LOCATION WHERE REGION
= ‘MELBOURNE’

设置Autotrace的命令

序号

列名

解释

1

SET AUTOTRACE OFF

此为默认值,即关闭Autotrace

2

SET AUTOTRACE ON

产生结果集和解释计划并列出统计

3

SET AUTOTRACE ON EXPLAIN

显示结果集和解释计划不显示统计

4

SETAUTOTRACE TRACEONLY

显示解释计划和统计,尽管执行该语句,但您将看不到结果集

5

SET AUTOTRACE TRACEONLY STATISTICS

只显示统计

1 SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
2 SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

6.2.5          语句执行汇总

在语句执行部分中,我们已经分析了为了进程处理,用户提交给Oracle的语句气概经历的4个阶段。图6-1是汇总这个流程的流程图:

图片 5

图6-1 语句处理过程流图

当向Oracle提交SQL语句的时候,解析器就要确定它是需要进行硬解析还是软解析。

如果语句要进行软解析,就可以直接进行SQL执行步骤,获得输出。

如果语句必须要进行硬解析,就需要将其发往优化器,它可以使用RBO或者CBO处理查询。当优化器生成它认为的最优方案之后,就会将方案转递给行源生成器。

行源生成器会将优化器的结果转换为Oracle系统其余部分能够处理的格式,也就是说,能够存储在共享池中,并且被执行的可重复使用的方案。这个方案可以由SQL引擎使用,处理查询并且生成答案(也就是输出)。

1 SELECT
EXECUTIONS,DISK_READS,BUFFER_GETS,
2 ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
3 ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT
4 FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0
5 AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
6 ORDER BY 4 DESC;

Autotrace执行计划的各列的涵义

序号

列名

解释

1

ID_PLUS_EXP

每一步骤的行号

2

PARENT_ID_PLUS_EXP

每一步的Parent的级别号

3

PLAN_PLUS_EXP

实际的每步

4

OBJECT_NODE_PLUS_EXP

Dblink或并行查询时才会用到

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个
查询返回相同结果但第二个明显就快了许多.
低效:

AUTOTRACE Statistics常用列解释

序号

列名

解释

1

db block gets

从buffer cache中读取的block的数量

2

consistent gets

从buffer cache中读取的undo数据的block的数量

3

physical reads

从磁盘读取的block的数量

4

redo size

DML生成的redo的大小

5

sorts (memory)

在内存执行的排序量

6

sorts (disk)

在磁盘上执行的排序量

(6)   
ANALYZE通常是由DBA使用的命令,可以收集与我们的表和索引有关的统计值——它需要被运行,以便CBO能够具有一些可以参照的统计消息。我们现在来使用它:

SQL> analyze table emp compute statistics;

表已分析。

SQL> analyze table dept compute statistics;

表已分析。

(7)   
现在,我们的表已经进行了分析,将要重新运行查询,查看Oracle这次使用的查询方案:

SQL> select * from emp,dept

  2  where emp.deptno=dept.deptno;



Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=700)

   1    0   HASH JOIN (Cost=5 Card=14 Bytes=700)

   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=5 Bytes=90)

   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)

在这里,CBO决定在2个表进行FULL SCAN(读取整个表),并且HASH
JOIN它们。这主要是因为:

  • 我们最终要访问2个表中的所有行
  • 表很小
  • 在小表中通过索引访问每一行(如上)要比完全搜索它们慢

 

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变. 现在,假设EMP_TYPE 是一个字符类型的索引列.

6.6     小结

  • Oracle怎样解析查询、从语法和语义上验证它的正确性。
  • 软解析和硬解析。在硬解析情况下,我们讨论了处理语句所需的附加步骤,也就是说,优化和行源生成。
  • Oracle优化器以及它的2种模式RULE和COST。
  • 用户能够怎样在SQL*Plus中使用AUTOTRACE查看所使用的优化器模式。
  • Oracle怎样使用REDO和UNDO提供故障保护。

文章根据自己理解浓缩,仅供参考。

摘自:《Oracle编程入门经典》 清华大学出版社 http://www.tup.com.cn/

1 SELECT
JOB,AVG(SAL)FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB=’MANAGER’ GROUP by
JOB

6.2.4          执行引擎

执行引擎(execution
engine)是获取行源生成器的输出,并且使用它生成结果集或者对表进行修改的进程。例如,通过使用以上最后生成的AUTOTRACE方案,执行引擎就可以读取整个EMP表。它会通过执行INDEX
UNIQUE
SCAN读取各行,在这个步骤中,Oracle会在DEPT_PK索引上搜索UNIQUE索引找到特定值。然后使用它所返回的值去寻找特定DEPTNO的ROWID(包含文件、数据文件、以及数据块片段的地址,可以利用这个地址找到数据行)。然后它就可以通过ROWID访问DEPT表。

执行引擎是整个进程的骨干,它是实际执行所生成的查询方案的部分。它会执行I/O,读取数据、排序数据、连接数据以及在需要的时候在临时表中存储数据。

1 SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

6.3     查询全过程

现在,我们来讨论Oracle处理查询的全过程。为了展示Oracle实现查询过程的方式,我们将要讨论2个非常简单,但是完全不同的查询。我们的示例要着重于开发者经常会问及的一个普通问题,也就是说:“从我的查询中将会返回多少行数据?”答案很简单,但是通常直到用户实际获取了最后一行数据,Oracle才知道返回了多少行。为了更好理解,我们将会讨论获取离最后一行很远的数据行的查询,以及一个必须等待许多(或者所有)行已经处理之后,可以返回记录的查询。

对于这个讨论,我们将要使用2个查询:

SELECT * FROM ONE_MILLION_ROW_TABLE;

以及

SELECT * FROM ONE_MILLION_ROW_TABLE ORDER BY C1;

在这里,假定ONE_MILLION_ROW_TABLE是我们放入了100行的表,并且在这个表上没有索引,它没有采用任何方式排序,所以我们第二个查询中的ORDYER
BY要有很多工作去做。

第一个查询SELECT * FROM
ONE_MILLION_ROW_TABLE将会生成一个非常简单的方案,它只有一个步骤:

TABLE ACCESS(FULL) OF ONE_MILLION_ROW_TABLE

这就是说Oracle将要访问数据库,从磁盘或者缓存读取表的所有数据块。在掌击的环境中(没有并行查询,没有表分区),将会按照从第一个盘区到它的最后一个盘区读取表。幸运的是,我们立即就可以从这个查询中获取返回数据。只要Oracle能够读取信息,我们的客户应用就可以获取数据行。这就是我们不能在获得最后一行之前,确定查询将会返回多少行的原因之一—甚至Oracle也不知道要返回多少行。当Oracle开始处理这个查询的时候,它所知道的就是构成这个表的盘区,它并不知道这些盘区中的实际行数(它能够基于统计进行猜测,但是它不知道)。在这里,我们不必等待最后一行接受处理,就可以获取第一行,因此我们只有实际完成之后才能够精确的行数量。

第二个查询会有一些不同。在大多数环境中,它都会分为2个步骤进行。首先是一个ONE_MILLION_ROW_TABLE的TABLE
ACCESS(FULL)步骤,它人将结果反馈到SORT(ORDER
BY)步骤(通过列C1排序数据库)。在这里,我们将要等候一段时间才可以获得第一行,因为在获取数据行之前必须要读取、处理并且排序所有的100万行。所以这一次我们不能很快获得第一行,而是要等待所有的行都被处理之后才行,结果可能要存储在数据库中的一些临时段中(根据我们的SORT_AREA_SIZE系统/会话参数)。当我们要获取结果时,它们将会来自于这些临时空间。

总而言之,如果给定查询约束,Oracle就会尽可能快地返回答案。在以上的示例中,如果在C1上有索引,而且C1定义为NOT
NULL,那么Oracle就可以使用这个索引读取表(不必进行排序)。这就可以尽可能快地响应我们的查询,为我们提供第一行。然后,使用这种过程获得最后一行就比较慢,因为从索引中读取100万行会相当慢(FULL
SCAN和SORT可能会更有效率)。所以,所选方案会依赖于所使用的优化器(如果存在索引,RBO总会倾向于选择使用索引)和优化目标。例如,运行在默认模式CHOOSE中,或者使用ALL_ROWS模式的CBO将使用完全搜索和排序,而运行于FIRST_ROWS优化模式的CBO将可能要使用索引。

用其它相同功能的操作运算代替,

6.2     怎样执行语句

相对于查询和DML语句,DDL更像是Oracle的一个内部命令。它不是在一些表上生成的查询,而是完成一些工作的命令。例如,如果用户使用:

Create table t(x int primary key, y date);

然而有意思的是,CREATE TABLE语句也可以在其中包含SELECT。我们可以使用:

Create table t as select * from scott.emp;

就像DML可以包含查询一样,DDL也可以这样做。当DDL包含查询的时候,查询部分会像任何其它查询一样接受处理。Oracle执行这些语句的4个步骤,它们是:

  • 解析
  • 优化
  • 行源生成
  • 执行语句

对于DDL,通常实际上只会应用第一个和最后一个步骤,它将会解析语句,然后执行它。“优化”CREATE语句毫无意义(只有一种方法可以建立内容),也不需要建立一般的方案(建立表的过程众所周知,已经在Oracle中直接编码)。应该注意到,如果CREATE语句包含了查询,那么就会按照处理其它查询的方式处理这个查询——采用以上所有步骤。

强列推荐不使用的,因为它不能应用表的索引。 用NOT
EXISTS 或(外连接+判断为空)方案代替

高效:

使用TOAD查看explain plan:

9、用TRUNCATE 替代DELETE删除全表记录:

删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE 时,回滚段不再存放任何可被恢复的信息.
当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE 只在删除全表适用,TRUNCATE是DDL
不是DML)

1 SELECT
DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS
  (SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

高效:

1 SELECT
DEPT_CODE FROM DEPT
ORDER BY DEPT_TYPE

(低效):

1 DELETE FROM EMP E WHERE E.ROWID >
  (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

21、总是使用索引的第一个列:
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where 子句引用时, 优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的第二个列时, 优化器使用了全表扫描而忽略了索引
用UNION-ALL 替换UNION ( 如果有可能的话):
当SQL
语句需要UNION 两个查询结果集合时,这两个结果集合会以UNION-ALL 的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL 替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL 的可行性. UNION 将对结果集合排序, 这个操作会使用到SORT_AREA_SIZE 这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL 可以用来查询排序的消耗量
低效:

16、用索引提高效率:
索引是表的一个概念部分,用来提高检索数据的效率,ORACLE 使用了一个复杂的自平衡B-tree 结构.
通常,通过索引查询数据比全表扫描要快. 当ORACLE 找出执行查询和Update 语句的最佳路径时, ORACLE 优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG 或LONGRAW 数据类型, 你可以索引几乎所有的列. 通常,
在大型表中使用索引特别有效. 当然,
你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE 将为此多付出4 , 5次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.:

的SQL
语句会启动SQL 引擎执行耗费资源的排序(SORT)功能.
DISTINCT 需要一次排序操作, 而其他的至少需要执行两次排序. 通常,
带有UNION, MINUS , INTERSECT 的SQL
语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE 调配得好, 使用UNION , MINUS, INTERSECT 也是可以考虑的, 毕竟它们的可读性很强
28、优化GROUP BY:

建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)。

1 @?/sqlplus/admin/plustrce.sql

DISTINCT,UNION,MINUS,INTERSECT,ORDER BY

高效:

  1. DBA在db中创建plustrace 角色:运行

高效:
(使用索引)

1 SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM
table2)

高效:

然后录入sql语句回车即可查看执行计划—推荐;
或者用如下命令行:

1 SELECT
ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95′
2 UNION
3 SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE
= ’31-DEC-95′

高效:

图片 6

1 SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

1 —-在init<SID>.ora文件中设定OPTIMIZER_MODE;

1 SELECT … FROM EMP
WHERETO_NUMBER(EMP_TYPE)=123

然后查看用户自己的plan_table

ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询 外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分 组统计等方面的SQL就不能转换了。 在业务密集的SQL当中尽量不采用IN操作符。

可替换为:

通过内部函数提高SQL 效率.:
复杂的SQL 往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
使用表的别名(Alias):
当在SQL 语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column 上.这样一来, 就可以减少解析的时间并减少那些由Column 歧义引起的语法错误.
15、识别’低效执行’的SQL
语句:
虽然目前各种关于SQL 优化的图形化工具层出不穷,但是写出自己的SQL 工具来解决问题始终是一个最好的方法:

1 ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

1 SELECT * FROM EMP
(基础表) WHERE EMPNO > 0 AND DEPTNO IN
  (SELECT DEP TNO FROM DEPT WHERE LOC =’MELB’)

优化sql时,经常碰到使用in的语句,一定要用exists把它给换掉,因为Oracle在处理In时是按Or的方式做的,即使使用了索引也会很慢。

Session级别:

(低效):

24、避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE 自动对列进行简单的类型转换. 假设EMPNO 是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123’
实际上,经过ORACLE 类型转换, 语句转化为:

低效:

因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE 对你的SQL 进行隐式 的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE 会优先
转换数值类型到字符类型
25、需要当心的WHERE 子句:
某些SELECT 语句中的WHERE 子句不使用索引. 这里有一些例子. 在下面的例子里, (1)’!=’ 将不使用索引. 记住,
索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||’是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
26、a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
27、避免使用耗费资源的操作:带有

1 SELECT
TAB_NAME FROM TABLES
WHERE
(TAB_NAME,DB_VER) =
  (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

1 Explain plan set statement_id=’myplan1′ for Your sql-statement;

例子:
(高效):

3、IS
NULL 或IS NOT NULL操作(判断字段是否为空)

a<>” 改为
a>”

大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因 为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
用>=替代>

1 SELECT * FROM EMP WHERE DEPTNO >3

1 SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

最近做查询时,写的一条查询语句用了两个IN,导致tuexdo服务积压了不少,用户没骂就不错了。最后经过技术经理的点拨,sql语句性能提升了大约10倍,主要用了表连接、建索引、exists。这才感叹SQL性能优化的重要性啊,网上搜了半天,找到一篇令我非常满意的日志,忍不住分享之:

这个语句被ORACLE 转换为:

避免在索引列上使用IS NULL 和IS
NOT NULL 避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在 此记录.如果至少有一个列不为空,则记录存在于索引中.举例: 如果唯一性索引建立在表的A 列和B
列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE 将不接受下一 条具有相同A,B 值(123,null)的记录(插入).然而如果所有的索引列都为空,ORACLE 将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE 子句中对索引列进行空值比较将使ORACLE 停用该索引.

4、>
及 < 操作符(大于或小于操作符)

1 SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123‘)

高效:
(索引有效)

1 SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

Oracle优化器(Optimizer)是Oracle在执行SQL之前分析语句的工具。
Oracle的优化器有两种优化方式:基于规则的(RBO)和基于代价的(CBO)。
RBO:优化器遵循Oracle内部预定的规则。
CBO:依据语句执行的代价,主要指对CPU和内存的占用。优化器在判断是否使用CBO时,要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。Oracle8及以后版本,推荐用CBO方式。
Oracle优化器的优化模式主要有四种:
Rule:基于规则;
Choose:默认模式。根据表或索引的统计信息,如果有统计信息,则使用CBO方式;如果没有统计信息,相应列有索引,则使用RBO方式。
First rows:与Choose类似。不同的是如果表有统计信息,它将以最快的方式返回查询的前几行,以获得最佳响应时间。
All rows:即完全基于Cost的模式。当一个表有统计信息时,以最快方式返回表所有行,以获得最大吞吐量。没有统计信息则使用RBO方式。
设定优化模式的方式
Instance级别:

Oracle优化器
Sql优化工具的介绍:
–Autotrace使用方法:
sqlexpert;toad;explain-table;PL/SQL;OEM等
掌握一种,熟练使用即可。
看执行计划用sqlplus 的autotrace,优化用sql expert。

1 SQL> ALTER SESSION SET OPTIMIZER_MODE=;—-来设定。

语句级别:通过SQL> SELECT /*+ALL+_ROWS*/
……;来设定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等。
要注意的是,如果表有统计信息,则可能造成语句不走索引的结果。可以用SQL>ANALYZE TABLE table_name DELETE
STATISTICS; 删除索引。
对列和索引更新统计信息的SQL:

12、减少对表的查询:
在含有子查询的SQL 语句中,要特别注意减少对表的查询.例子:

1 SELECT … FROM DEPT WHERE SAL > 25000/12;

  1. 用户sqlplus连接数据库,对会话进行如下设置:
  1. 用户创建自己的plan_table:运行

1 SELECT … FROM EMP WHERE EMP_TYPE = 123

(高效):

1 DEPT_CODE PK NOT NULL
2 DEPT_DESC NOT NULL
3 DEPT_TYPE NULL

10、尽量多使用COMMIT:
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT 所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 ,c.
redo log buffer 中的空间 ;d.
ORACLE 为管理上述3种资源中的内部花费
11、用Where 子句替换HAVING 子句:
避免使用HAVING 子句,
HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having 这三个都可以加条件的子句中,on是最先执行,where 次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的, where也应该比having 快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样 的,只是where 可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having 就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时, on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由 此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里

7、用UNION 替换OR
(适用于索引列)
通常情况下, 用UNION 替换WHERE 子句中的OR 将会起到较好的效果. 对索引列使用OR 将造成全表扫描. 注意,以上规则只针对多个索引列有效. 如果有column 没有被索引, 查询效率可能会因为你没有选择OR 而降低. 在下面的例子中, LOC_ID和REGION 上都建有索引.
(高效):

不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。

低效:
(索引不被使用)

23、用WHERE 替代ORDER BY:
ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY 中所有的列必须定义为非空. WHERE 子句使用的索引和ORDER BY 子句中所使用的索引不能并列.
例如:
表DEPT
包含以下列:

1 @?/rdbms/admin/utlxplan.sql。—-以上是第一次使用时需要进行的必要操作。

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
8、用IN 来替换OR
这是一条简单易记的规则,但是实际的执行效果还须检验,在ORACLE8i 下,两者的执行路径似乎是相同的.
低效:

用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:

如:
用EXISTS 替代IN、用NOT EXISTS 替代NOT IN:
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT
EXISTS)通常将提高查询的效率. 在子查询中,NOT IN 子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

1 SELECT col1,col2,col3 FROM table1 a WHERE not exists
  (SELECT ‘x’ FROM table2 b WHERE a.col1=b.col1)

a is not null 改为
a>0 或a>”等。

1 SELECT
ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95′
2 UNION ALL
3 SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE
= ’31-DEC-95′

二、SQL语句结构优化
1、选择最有效率的表名顺序(只在基于规则的优化器中有效):
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM 子句中写在最后的表(基础表driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
2、WHERE 子句中的连接顺序:
ORACLE 采用自下而上的顺序解析WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾.
3、SELECT 子句中避免使用’ * ‘:
ORACLE 在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
4、减少访问数据库的次数:
ORACLE 在内部执行了许多工作: 解析SQL 语句,
估算索引的利用率, 绑定变量, 读数据块等;
5、在SQL*Plus , SQL*Forms 和Pro*C 中重新设置ARRAYSIZE 参数,
可以增加每次数据库访问的检索数据量,建议值为200
6、使用DECODE 函数来减少处理时间:使用DECODE 函数可以避免重复扫描相同记录或重复连接相同的表.
7、 整合简单,无关联的数据库访问: 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
8、删除重复记录:
最高效的删除重复记录方法( 因为使用了ROWID)例子:

1 SELECT … FROM DEPT WHERE SAL * 12 > 25000;

1、IN
操作符

1 Set autotrace
—–off/on/trace[only]——explain/statistics,

比如:

判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。

17、sql
语句用大写的;因为oracle 总是先解析sql 语句,把小写的字母转换成大写的再执行。
18、在java 代码中尽量少用连接符”+”连接字符串!
19、避免在索引列上使用NOT 通常,
我们要避免在索引列上使用NOT, NOT 会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
避免在索引列上使用计算.
WHERE 子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
举例:
低效:

a<>0 改为 a>0 or
a<0

  1. DBA给用户赋予角色:

1 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

6、用EXISTS 替换DISTINCT:
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT 子句中使用DISTINCT. 一般可以考虑用EXIST 替换,
EXISTS 使查询更为迅速,因为RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结果.
例子:
(低效):

低效:
(索引失效)