ORACLE数据库操作手册

ORACLE 数据库操作手册4.1

中国通信集团公司安徽有限公司

信息系统部

2010年7月

修改记录

版本

1.0

1.1

日期

2006/2/10

2007/8/31

编辑者

见春蕾

见春蕾

见春蕾

形成初稿

根据实际情况修订部分章节

一、在第一章（数据库使用注意事项）中增加了关于批量更新数据的大事务分次提交的要求、客户端的配置方法、修改密码方法；

二、在第二章（SQL编写注意事项）中增加了关于提示(Hints)的使用；

三、增加了第四章（跟踪SQL执行计划），阐述了相关的理论知识和SQL执行计划的跟踪方法。

编辑内容

2.0 2007/11/05

3.0 2009/3/5

尹宁、王永波、一、根据实际情况修订相关章涂定宏等

节。

二、补充了绑定变量使用规范以及SQL语句优化的基本规则。

一、根据NG现状修改访问地址说明。

二、去除不适用ORACLE 10G的内容，增加相关内容

王永波、尹宁等

三、去除数据库设计规范及集成规范涉及的内容

四、刷新SQL语句编写规则

五、调整部分章节顺序

一、根据NG现状修改访问地址说明。

常玉龙、尹宁等

二、刷新sql注意事项

4.0 2010/7/12

4.1 2012/9/11

目录

第一章 数据库使用注意事项....................................................................................................... 6

第一章 数据库使用注意事项....................................................................................................... 6

一、营业前台使用统一地址，后台业务严格区别业务区，不可随意访问 ........... 6

二、不涉及当天业务的查询和统计在BCV库中操作 ............................ 6

三、前台营业时间禁止在生产环境进行大数据量的查询和统计操作 ............... 6

四、关联表均很大的查询和统计尽量用BCV库 ................................ 6

五、按照业务规则进行DML操作，DML不要忘记执行COMMIT或ROLLBACK .. 7

六、大批量更新数据的事务分次提交 ......................................... 7

七、数据库DDL操作由数据库管理员根据业务规则进行 ........................ 7

八、数据库使用结束及时断开连接，但也不要频繁的连接和断开 ................. 7

九、客户端配置与客户端第三方工具使用 ..................................... 7

十、不要使用工具进行可视化数据修改操作 ................................... 7

十一、合理使用帐号，妥善保管密码 ......................................... 7

十二、严禁在生产库进行业务开发、调试工作 ................................. 8

十三、单条SQL语句的长度最好不要超过1000字节 ........................... 8

十四、数据导出导入使用expdp/impdp,不要使用exp/imp ........................ 8

十五、PL/SQL程序应尽量符合第二章要求，注意事务的提交、回滚及异常情况处理 8

十六、一定不能在循环体内部创建数据库的连接，包括通过JDBC连接数据库。 ... 8

十七、SQL语句在执行前需要对语句逻辑进行检查，避免形成隐式笛卡尔积，占用大量临时表空间和降低语句效率。 ............................................. 8

第二章 SQL编写注意事项.......................................................................................................... 9

一、查看表字段名或随机少量数据时，不要使用SELECT * FROM TABLENAME ... 9

二、SELECT 子句中避免使用* ............................................. 9

三、查询总记录数时，尽量不要用COUNT(*)，而要指定一个有索引的字段。 ..... 9

四、对分区表进行查询时，尽量把分区键作为查询条件的第一个条件 ............. 9

五、无条件删除表中数据时，用TRUNCATE代替DELETE ..................... 9

六、查询语句中尽量使用表的索引字段，避免做大表的全表扫描 ................. 9

七、带通配符（%）的LIKE语句 .......................................... 10

八、用EXISTS替代IN ................................................... 10

九、用NOT EXISTS替代NOT IN .......................................... 10

十、尽可能用UNION ALL替换UNION ..................................... 11

十一、ORDER BY语句建议 ............................................... 11

十二、避免使用NOT ..................................................... 11

十三、使用DECODE函数减少处理时间 ..................................... 12

十四、删除重复记录 ...................................................... 12

十五、如果可以使用WHERE条件，尽量不要在HAVING中限制数据 ........... 12

十六、尽量不要使数据排序 ................................................ 12

十七、避免改变索引列类型 ................................................ 13

十八、避免在索引列上使用计算 ............................................ 13

十九、避免在索引列上使用IS NULL和IS NOT NULL ........................ 13

二十、子查询改写成表连接 ................................................ 13

二十一、使用索引的第一个列 .............................................. 14

二十二、减少对表的查询 .................................................. 14

二十三、SQL语句中：用>=替代> ........................................... 14

如果在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比

语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。 .......................................................... 15

二十四、使用提示(hints) .................................................. 15

二十五、大批量数据导入 .................................................. 15

二十六、DBLINK使用 .................................................... 15

二十七、绑定变量使用建议 ................................................ 16

第三章 跟踪SQL执行计划......................................................................................................... 18

一、相关理论 ............................................................ 18

1、ORACLE优化器 ...................................................... 18

2、访问TABLE的方式 ................................................... 18

3、索引访问方式 ......................................................... 18

二、SET TRACE跟踪SQL执行计划 ........................................ 19

第一章 数据库使用注意事项

一、营业前台使用统一地址，后台业务严格区别业务区，不可随意访问

营业网址需要通过4A系统统一使用10.153.176.6通过四层交换机访问，禁止访问指定的主机。 后台业务（包含后台脚本及维护人员业务操作）须严格按照业务区使用中间件及数据库实例，以避免影响数据库性能。

A1：合肥、淮北

中间件:10.153.171.125 crmtux1/3（主）、10.153.171.126 crmtux1（备）

10.153.171.125 bosstux1/3（主）、10.153.171.126 bosstux1（备）

数据库:WCRMA1、WACCTA1、WRES1

A2：阜阳、宿州、亳州、池州

中间件:10.153.171.126 crmtux2/3（主）、10.153.171.125 crmtux2（备）

10.153.171.126 bosstux2/3（主）、10.153.171.125 bosstux2（备）

数据库:WCRMA2、WACCTA2、WRES2

B1：蚌埠、马鞍山、安庆、黄山、滁州

中间件:10.153.171.135 crmtux1/3（主）、10.153.171.136 crmtux1（备）

10.153.171.135 bosstux1/3（主）、10.153.171.136 bosstux1（备）

数据库:WCRMB1、WACCTB1、WRES1

B2：芜湖、淮南、铜陵、六安、宣城

中间件:10.153.171.136 crmtux2/3（主）、10.153.171.135 crmtux2（备）

10.153.171.136 bosstux2/3（主）、10.153.171.135 bosstux2（备）

数据库:WCRMB2、WACCTB2、WRES2

二、不涉及当天业务的查询和统计在BCV库中操作

BCV库每天0时同步一次（历时约1小时），数据、用户及密码与生产环境相同。BCV库是单节点数据库，共有WCRMABCV、WCRMBBCV、WACCTABCV、WACCTBBCV和WRESBCV等五个库。BCV库的TBS_PERSON表空间是可写表空间。

三、前台营业时间禁止在生产环境进行大数据量的查询和统计操作

前台营业时间一般为8:00-18:00（高峰时间8:30-10:30、15:00-16:30），在此期间不要在生产环境做大数据量的查询和统计，每个查询的执行时间控制在１分钟内。

四、关联表均很大的查询和统计尽量用BCV库

大表关联查询操作一般会占用较大的临时表空间和较高CPU，对数据库影响较大，尤其是多个大表关联且涉及排序、分组等操作时。

五、按照业务规则进行DML操作，DML不要忘记执行COMMIT或ROLLBACK

不要只执行语句，而不控制事务。当执行一条DML语句时，数据库会分配相应锁、回滚段、REDO LOG BUFFER等资源。语句执行后应尽快的提交或回滚释放分配的资源。

六、大批量更新数据的事务分次提交

在营业忙时，大于10万的数据刷新需分次提交。大于100万的数据刷新尽量在系统空闲时进行，并分次提交，以减少异常发生。

七、数据库DDL操作由数据库管理员根据业务规则进行

DDL操作有可能涉及到存储分配、表分析、数据生命周期规划等，须数据库管理员执行，业务人员不能使用预设帐号执行DDL语句。

八、数据库使用结束及时断开连接，但也不要频繁的连接和断开

数据库连接是数据库的宝贵资源，数据库支持的连接有限，不需要使用数据库时断开连接。如果能正常退出，别用“结束任务”或kill -9。如果正在执行SQL时发生终端异常，联系数据库管理员检查处理，以防止数据库一直占用该SQL相关资源。

九、客户端配置与客户端第三方工具使用

Oracle客户端版本要求在10.2.0.4及以上，以尽量避免出现兼容性问题。第三方工具PL/SQL Developer版本应在8.0及以上，Toad for Oracle版本应在10.0及以上，DBArtisan版本应在8.5及以上。

十、不要使用工具进行可视化数据修改操作

使用工具可视化修改数据时可能会加长数据库锁时间、增加误操作风险，因此数据修改操作须直接用SQL语句进行。

十一、合理使用帐号，妥善保管密码

个人帐号能进行的操作不要使用应用帐号执行，少量数据修改的操作应通过问题知识库对个人帐号授权后进行。帐号密码应妥善保管，不得借给他人使用，每三个月需修改一次个人帐号密码，密码应不低于8位，并含有字母、数字和特殊字符，且不能同于前五次密码。不要非法获取未经授权的系统预设（应用）帐号密码。不要使用未经授权的帐号登陆数据库。帐号被锁定时联系数据库管理员解决。

十二、严禁在生产库进行业务开发、调试工作

十三、单条SQL语句的长度最好不要超过1000字节

1000字节对应1000个英文字符或500个中文字符，需要更长语句的操作考虑使用语句段、存储过程或临时表解决。

十四、数据导出导入使用expdp/impdp,不要使用exp/imp

十五、PL/SQL程序应尽量符合第二章要求，注意事务的提交、回滚及异常情况处理

十六、一定不能在循环体内部创建数据库的连接，包括通过JDBC连接数据库。

十七、SQL语句在执行前需要对语句逻辑进行检查，避免形成隐式笛卡尔积，占用大量临时表空间和降低语句效率。

第二章 SQL编写注意事项

一、查看表字段名或随机少量数据时，不要使用SELECT * FROM

TABLENAME

用DESC TABLENAME或SELECT * FROM TABLENAME WHERE 1 = 2、SELECT *

FROM TABLENAME WHERE ROWNUM < 1等命令查看表结构信息，尽量不要直接执行SELECT * FROM TABLENAME，然后kill会话。

二、SELECT 子句中避免使用*

在SELECT子句中列出所有的列时，使用*很方便，但是效率低。因为ORACLE在解析过程中会查询数据字典，将*依次转换成所有的列名。所以，直接在SELECT子句中写出想要显示的列。

三、查询总记录数时，尽量不要用COUNT(*)，而要指定一个有索引的字段。

例如主键列为INDEX，使用COUNT(INDEX)能利用索引。

四、对分区表进行查询时，尽量把分区键作为查询条件的第一个条件

五、无条件删除表中数据时，用TRUNCATE代替DELETE

使用DELETE删除表中记录未提交时，系统会用回滚段存放被删除信息。而TRUNCATE命令不使用回滚段，当命令执行后，数据不能被恢复，因此很少的资源被调用，执行时间也会更短。

六、查询语句中尽量使用表的索引字段，避免做大表的全表扫描

例如：WHERE子句中有联接的列，即使最后的联接值为一个静态值，也不会使用索引。

SELECT *

FROM EMPLOYEE

WHERE FIRST_NAME || '' || LAST_NAME = 'Beill Cliton';

这条语句没有使用基于LAST_NAME创建的索引。当采用下面这种SQL语句的编写，ORACLE系统就可以采用基于LAST_NAME创建的索引。

SELECT *

FROM EMPLOYEE

WHERE FIRST_NAME = 'Beill'

AND LAST_NAME = 'Cliton';

七、带通配符（%）的LIKE语句

例如：SQL语句：

SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE '%cliton%';

由于通配符（%）在词首出现，所以Oracle系统不使用LAST_NAME的索引。如此使用通配符会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。例如：

SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE 'c%';

八、用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 =

AND LOC = 'MELB');

九、用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 _NO = (+)

AND _NO IS NULL

AND _CAT(+) = 'A';

(方法二：最高效)

SELECT *

FROM EMP E

WHERE NOT EXISTS (SELECT 'X'

FROM DEPT D

WHERE _NO = _NO

AND DEPT_CAT = 'A');

十、尽可能用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';

十一、ORDER BY语句建议

ORDER BY语句决定了ORACLE如何将返回的查询结果排序。ORDER BY语句对要排序的列没有特别限制，也可以将函数加入列中。在ORDER BY语句中使用非索引项或有计算表达式都将降低查询速度。当ORDER BY中所有的列定义为非空时会用到索引，例如：

T1表的ID列存在索引，且非空。则以下查询用到索引：

SELECT * FROM T1 ORDER BY ID;

十二、避免使用NOT

在查询时经常在WHERE子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用AND（与）、OR（或）以及NOT（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

... WHERE NOT (STATUS ='VALID')

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询WHERE子句中显式地加入NOT词，NOT仍在运算符中，见下例：

... WHERE STATUS <>'INVALID';

再看下面这个例子：

SELECT * FROM EMPLOYEE WHERE SALARY <> 3000;

对这个查询，可以改写为不使用NOT：

SELECT * FROM EMPLOYEE WHERE SALARY < 3000 OR SALARY > 3000;

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许ORACLE对SALARY列使用索引，而第一种查询则不能使用索引。

十三、使用DECODE函数减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如：

SELECT COUNT(*) ，SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE 'SMITH%';

SELECT COUNT(*) ，SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE 'SMITH%';

可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO, 0020, 'X', NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO, 0030, 'X', NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL

FROM EMP

WHERE ENAME LIKE 'SMITH%';

类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

十四、删除重复记录

DELETE FROM EMP E

WHERE > (SELECT MIN() FROM EMP X WHERE _NO

= _NO);

十五、如果可以使用WHERE条件，尽量不要在HAVING中限制数据

十六、尽量不要使数据排序

带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、GROUP BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至

少需要执行两次排序，影响查询的效率。

十七、避免改变索引列类型

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。假设EMPNO是一个数值类型的索引列。

SELECT * FROM EMP WHERE EMPNO = ‘123’;

实际上，经过ORACLE类型转换，语句转化为：

SELECT * FROM EMP WHERE EMPNO = TO_NUMBER(‘123');

但是类型转换没有发生在索引列上，索引的用途没有被改变。如果，EMP_TYPE是一个字符类型的索引列。

SELECT * FROM EMP WHERE EMP_TYPE = 123;

这个语句被ORACLE转换为：

SELECT * FROM EMP WHERE TO_NUMBER(EMP_TYPE) = 123;

因为内部发生的类型转换，索引将不会被用到。

十八、避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：

低效：

SELECT * FROM DEPT WHERE SAL * 12 > 25000;

高效：

SELECT * FROM DEPT WHERE SAL > 25000 / 12;

十九、避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。例如：

如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为(123，null) ，

ORACLE将不接受下一条具有相同A，B值（123，null）的记录(插入)。然而如果所有的索引列都为空，ORACLE将认为整个键值为空，而空不等于空。因此可以无限条空记录。

因空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。例如：

低效：(索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效：(索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

二十、子查询改写成表连接

通常来说，采用表连接的方式比子查询更有效率，但并不是所有的子查询都可以改写成表连接的形式。只有当连接字段存在唯一性时才可以进行改写。否则重复字段会产生笛卡尔积。

例如：

T1表存在以下数据：

ID ID2

1 1

2 2

2 3

T2表存在以下数据：

ID ID2

1 1

2 2

3 2

4 2

2 3

则以下查询结果不同：

子查询：

SELECT COUNT(*) FROM T1 WHERE IN (SELECT ID FROM T2); 返回值3

表连接：

SELECT COUNT(*) FROM T1, T2 WHERE = ; 返回值5

二十一、使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列(leading column)被WHERE子句引用时，优化器才会选择使用该索引。

二十二、减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。例如：

低效：

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE

VERSION = 604)

AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION =

604);

高效：

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME, DB_VER) =

(SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION

= 604);

二十三、SQL语句中：用>=替代>

如果在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。

二十四、使用提示(hints)

在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。hints是ORACLE提供的一种机制，用来告诉优化器按照指定方式生成执行计划。可以用hints实现：

1、使用优化器的类型；

2、基于代价优化器的优化目标，是ALL_ROWS还是FIRST_ROWS；

3、表的访问路径，是全表扫描，还是索引扫描，还是直接利用ROWID；

4、表之间的连接类型；

5、表之间的连接顺序；

6、语句的并行程度

hints只应用在它们所在SQL语句块(由SELECT、UPDATE、DELETE关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如对于使用UNION操作的2个SQL语句，如果只在一个SQL语句上有hints，则该hints不会影响另一个SQL语句。可以使用注释(COMMENT)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT、UPDATE、DELETE关键字的后面

使用hints的语法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

或者

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解：

1、DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。

2、“+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。

3、如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。

4、text 是其它说明hint的注释性文本。

如果没有正确的指定hints，ORACLE将忽略该hints，并且不会给出任何错误。

二十五、大批量数据导入

大批量数据导入可能会导致UNDO表空间和REDOLOG满，对数据库的影响较大。

可以采用分批提交的方法避免UNDO表空间满的问题，REDOLOG满的问题可添加/*+

append */提示避免，示例语句如下：

ALTER TABLE TAB1 NOLOGGING;

INSERT /*+ APPEND */ INTO TAB1 SELECT * FROM TAB2;

COMMIT;

ALTER TABLE TAB1 LOGGING;

二十六、DBLINK使用

使用DBLINK进行查询，当查询包含了本地表和远端表时，ORACLE一般先把远端表的数据通过网络传送到SQL发起端，再跟本地表进行关联得到最终结果，如果远端表返回的数据较多，则会影响查询的速度。例如：

在YZDB3上执行以下语句：

SELECT *

FROM INFO.T_CI_CUSTINFO_A@YZDBBA

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E

WHERE SVCNUM = '139XXXXXXXX');

执行时间：165.078秒。若改成从A库中取数据，速度会提高很多：

SELECT *

FROM INFO.T_CI_CUSTINFO_A

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E@YZDBAB

WHERE SVCNUM = '139XXXXXXXX');

执行时间：0.297秒。或通过driving_site强制指定主驱动表，即以所指定的表为主要表，将其它表作为从表提取到驱动表所在的库进行关联运算。例如：

SELECT /*+ DRIVING_SITE(A) */ *

FROM INFO.T_CI_CUSTINFO_A@YZDBBA A

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E

WHERE SVCNUM = '139XXXXXXXX');

执行时间：0.031秒。

另外，DBLINK中应尽量避免调用远程的存储过程。

二十七、绑定变量使用建议

1、 Pro*C或OCI编程使用动态SQL时，必须采用绑定变量方式，已避免引起数据库性能问题。

示例：

A、非绑定变量方式的动态SQL

//处理传入的参数，构造动态SQL语句

sprintf(sztmp1,

"SELECT

TO_CHAR(apply_date, 'yyyymmddhh24miss'),

state,

FROM user_smscall

WHERE

(gsm_user_id = '%s' ) ",//非绑定变量方式的动态SQL语句

ora_gsm_user_id);

B、绑定变量方式的动态SQL

//处理传入的参数，构造动态SQL语句

sprintf(sztmp1,

"SELECT

TO_CHAR(apply_date, 'yyyymmddhh24miss'),

state,

FROM user_smscall

WHERE

(gsm_user_id = :v1 ) ");//绑定变量方式的动态SQL语句

EXEC SQL PREPARE sql FROM :sqlstmt; //Prepare

//定义游标

EXEC SQL declare user_smscall_cu_1 cursor for sql;

EXEC SQL OPEN user_smscall_cu_1 USING :ora_gsm_user_id;//传变量值

2、在cursor_sharing参数均设置为similar时，绑定变量的使用时应注意以下几点：

(1) 使用绑定变量，以动态SQL替代静态SQL；

(2) 该对象不能进行频繁的DDL操作；

(3) 相同SQL的绑定变量值的类型，类型的长度定义需要完全一致；

(4) 语句中对在收集了统计信息的列进行等于操作，不会使用绑定变量；

(5) 通过DBLINK操作远程的对象时：

SELECT语句必须使用绑定变量，能够避免较高的version_count；

INSERT语句降低高version_count的2种解决办法：

1)SQL语句中使用绑定变量，对于INSERT远程对象加上NOAPPEND的hint。

2)将应用程序中的INSERT INTO … TABLE@REMOTE ...语句拆分为2条。

例如：

A、首先将SELECT的相关字段的值放入绑定变量。如：

SELECT ,...,

INTO :v_b1, ,:v_b2 ...... ,:v_bn

FROM

B、然后直接使用绑定变量的值对表进行插入操作。如：

INSERT INTO @（DBLINK）

(,,......)

VALUES (:v_b1,,v_bn)

第三章 跟踪SQL执行计划

一、相关理论

1、ORACLE优化器

1）Oracle的优化器有3种：

基于规则 RULE

基于成本 COST

基于选择 CHOOSE

2）设置缺省的优化器，可以通过对文件中OPTIMIZER_MODE参数设置，也可以在会话(session)级对其进行覆盖。

3）如果OPTIMZER_MODE=RULE，则激活基于规则的优化器(RBO)。基于规则的优化器按照一系列的语法规则来推测可能执行路径和比较可替换的执行路径。

4）如果OPTIMZER_MODE=COST，则激活基于成本的优化器(CBO)。它使用ANALYZE语句来生成数据库对象的统计数据。这些统计数据包括表的行数、平均长度及索引中不同的关键字数等。基于这些统计数据，成本优化器可以计算出可获得的执行路径的成本。并选择具有最小的成本执行路径。在CBO模式下，需要经常运行ANALYZE 命令来确保数据的准确性。

5）如果OPTIMZER_MODE=CHOOSE，实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO，反之数据库将采用RULE形式的优化器。

2、访问TABLE的方式

ORACLE 采用两种访问表中记录的方式：

1）全表扫描

全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块的方式优化全表扫描。

2）ROWID定位访问

ORACLE采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此基于索引列的查询可以得到性能上的提高。

3、索引访问方式

Oracle有两种索引访问方式

1）索引唯一扫描 ( INDEX UNIQUE SCAN)

如：表LOADING有两个索引：建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER。

SELECT * FROM LOADING WHERE LOADING = ‘ROSE HILL’;

在内部，上述SQL将被分成两步执行，首先LOADING_PK索引将通过索引唯一扫描的方式被访问，获得相对应的ROWID，通过ROWID访问表的方式执行下一步检索。

如果被检索返回的列包括INDEX列中，ORACLE将不执行第二步的处理(通过ROWID访问表)。因为检索数据保存在索引中，单单访问索引就可以完全满足查询结果。

SELECT LOADING FROM LOADING WHERE LOADING = ‘ROSE HILL’;

2)索引范围查询(INDEX RANGE SCAN)

适用于两种情况：

A、基于一个范围的检索

B、基于非唯一性索引的检索

例1：

SELECT LOADING FROM LOADING WHERE LOADING LIKE ‘M%’;

WHERE子句条件包括一系列值， ORACLE将通过索引范围查询的方式查询LODGING_PK 。 由于索引范围查询将返回一组值， 它的效率就要比索引唯一扫描低一些。

例2：

SELECT LOADING FROM LOADING WHERE MANAGER = ‘BILL GATES’;

这个SQL的执行分两步，IDX_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值。由于IDX_MANAGER是一个非唯一性的索引，数据库不能对它执行索引唯一扫描。由于SQL返回LOADING列，而它并不存在于IDX_MANAGER索引中，所以在索引范围查询后会执行一个通过ROWID访问表的操作。WHERE子句中，如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始，索引将不被采用。

SELECT LOADING FROM LOADING WHERE MANAGER LIKE‘%HANMAN’;

在这种情况下，ORACLE将使用全表扫描。

二、SET TRACE跟踪SQL执行计划

例如：

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE =

SQL> set autotrace traceonly

/*traceonly 可以不显示执行结果*/

SQL> /

14 rows selected.

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 'PK_DEPT' (UNIQUE)

Statistics

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

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

通过以上分析，可以得出实际的执行步骤是：

1、TABLE ACCESS (FULL) OF 'EMP'

2、INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

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

4、NESTED LOOPS (JOINING 1 AND 3)

注：目前许多第三方工具如TOAD、PL/SQL Developer、DBArtisan和ORACLE提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具。