ORACLE数据库操作手册

ORACLE数据库操作手册2[1]0

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

计费业务部

2007年11月

修改记录

版本日期编辑者编辑内容

1.02006/2/10见春蕾形成初稿

1.12007/8/31见春蕾根据实际情况修订部分

章节

2.02007/11/05见春蕾一、在第一章（数据库使

用注意事项）中增加了关

于批量更新数据的大事

务分次提交的要求、客户

端的配置方法、修改密码

方法；

二、在第二章（SQL编写

注意事项）中增加了关于

提示（Hints）的使用。

三、增加了第四章（跟踪

SQL执行计划），阐述了有

关的理论知识与SQL执行

计划的跟踪方法。

目录

ORACLE数据库操作手册2.0.......................................错误!未定义书签。

笫一章数据库使用注意事项.......................................................3

二、不使用数据库时请及时关闭数据库连接，但是也不能频繁的连接与断开.......3

三、执行了DML操作，请按业务规则,不要不记得执行COMMIT或者ROLLBACK。........3

四、假如是查询与统计不涉及到当天的业务时，不要在生产环境里操作，在BCV库中操

作。BCV每天晚上12点同步一次，数据与用户口令、密码与生产环境相同。......3

五、关联表都很大的查询与统计也尽量用BCV库。...............................4

六、生产环境营业时间（特别是营业高峰时间，目前是上午8:00-10:00,下午3:00-4:00）

禁止做大数据量的查询与统计，每个查询的执行时间要操纵在1分钟内。.........4

七、不要执行索引与表的信息的收集。.........................................4

八、编写程序的时候，注意SQL语句规范，尽量使用变量绑定，减少共享池的使用。

................................................................4

九、按照标准要求编写pl/sql等程序，注意事务的提交、回滚与对各类特殊情况的处

理。..........................................................................4

十、要查看表字段名或者随机的少量数据时候，使用desc、也能够使用wherel=2或者

者rowcountXn来查看，而尽量不要直接执行select*fromtablename,然后kill会

话。..........................................................................4

十一、尽量使用索引，避免出现全表扫描，性能影响比单机更大。................4

十二、对分区表建立索引时，使用local选项。.................................4

十三、不要在事务中引入Trigger,建议在事务中实现............................4

十四、批量更新数据的大的事务分次提交。.....................................4

十五、客户端的配置，.........................................................5

十六、修改密码。.............................................................5

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

一、SELECT子句中避免使用*..................................................7

二、查找总记录数时，尽量不要用count(*),而要指定一个有索引的字段。.......7

三、将大的历史表创建为分区表，便于数据转储与删除。.........................7

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

五、Sequence使用cache/noorder,假如在使用sequence上的列建索弓I,建议加大cache

值。..........................................................................7

六、在FROM子句中包含多个表的情况下，选择记录条数最少的表作为基础表，放在FROM

子句的最后面。...............................................................7

七、WHERE子句中的连接顺序...................................................8

八、在需要无条件删除表中数据时，用truncate代替delete。...................8

九、语句中尽量使用表的索引字段，避免做大表的全表扫描。....................8

十、带通配符(%)的like语句...............................................9

H-一、用EXISTS替代1N..............................................9

十一、用NOTEXISTS替代NOTIN.......................................9

十三、尽可能的用UNION-ALL替换UNION................................10

十四、Orderby语句建议.....................................................10

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

十六、使用DECODE函数来减少处理时间........................................11

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

十八、假如能够使用where条件，尽量不要在having中限制数据................12

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

二十、使用提示(Hints)..............................................12

第三章oracle与Sybase的SQL区另1.......................................13

一、大小写...................................................................13

二、限制记录数量............................................................13

三、列的选择.................................................................13

四、连接.....................................................................13

五、字符串函数..............................................................13

六、日期函数.................................................................14

七、数据类型转换函数：.......................................................14

八、空值替代函数：...........................................................14

九、Sybase的where语句执行［］正则符号,但是oracle9i不支持。.............14

十、数字取舍.................................................................14

第四章跟踪SQL执行计划.........................................................15

一、理论.....................................................................15

（一）ORACLE优化器..........................................................15

（二）访问TABLE的方式......................................................15

（三）索引访问方式.........................................................16

二、SETTRACE跟踪sql执行计划..............................................16

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

一、对BOSSL5营帐库，营业网址严格按照要求进行配置，不可随意更换。

营业网址要求按照下面方式进行分配配置，假如随意更换，会增加营业主机间的数据交互,

影响数据库性能，降低营业工作效率。

合肥、六安、阜阳、宿州、亳州、淮北、黄山、铜陵配置：

或者者

芜湖、蚌埠、淮南、马鞍山、安庆、滁州、宣城、巢湖、池州，配置如下：

或者者

二、不使用数据库时请及时关闭数据库连接，但是也不能频繁的连接与断开

数据库连接也是数据库的宝贵资源，数据库支持的数据库连接有限，当不需要使用数据库时，

请“优雅”的退出数据库吧，假如能正常退出，请别“结其任务”或者KILL-9。假如正在执行

SQL的时候突然特殊终端，请联系数据库管理员检叁处理，以防止数据库一直占用该SQL有关资

源。

三、执行了DML操作，请按业务规则，不要不记得执行COMMIT或者ROLLBACK。

不要只执行语句，而不操纵事务。当你执行一条DML语句时，数据库会为你分配锁、回滚段、

REDOLOGBUFFER等资源。事务结束后，这些资源才能得以释放。

四、假如是查询与统计不涉及到当天的业务时，不要在生产环境里操作，在BCV

库中操作。BCV每天晚上12点同步一次，数据与用户口令、密码与生产环境相

同。

bcv是一个节点的数据库，所有的地市的查询的连接配置是同一个，如下：

YZDBBCV=

（DESCRIPTION=

（ADDRESSLIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.153.192.45)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=yzdb)

)

)

五、关联表都很大的查询与统计也尽量用BCV库。

六、生产环境营业时间(特别是营业高峰时间，目前是上午8：00-10：00,下午

3:00-4:00)禁止做大数据量的查询与统计，每个查询的执行时间要操纵在1分

钟内。

七、不要执行索引与表的信息的收集。

八、编写程序的时候，注意SQL语句规范，尽量使用变量绑定，减少共享池

的使用。

九、按照标准要求编写pl/sql等程序，注意事务的提交、回滚与对各类特殊情

况的处理。

十、要查看表字段名或者随机的少量数据时候，使用desc、也能够使用wherel=2

或者者rowcountVn来查看，而尽量不要直接执行select*fromtablename»然

后kill会话。

十一、尽量使用索引，避免出现全表扫描，性能影响比单机更大。

十二、对分区表建立索引时，使用local选项。

十三、不要在事务中引入Trigger,建议在事务中实现。

十四、批量更新数据的大的事务分次提交。

在营帐数据库系统繁忙时候，大于300万的数据刷新，建议分次提交，减少特殊发生。

系统空闲时，大于800万的数据刷新，建议分次提交。

十五、客户端的配置。

方法一、直接修改tnsnames.ora

YZDBBCV=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.147.192.45)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=yzdb)

)

)

方法二、使用客户端工具

»adsin.3，X

文年Q)夕辑11)

o后退­」

WtQ)|&D:W>cl<vflHBJ

文悴要修改日期

HOMTlClt2005-10C3ie31

£口UB»«・1"”毋Off>c2002&73823

sgk每ca二芦2OO7P7-231954

♦口•■】or”必2002&73623

S。7ORA文怦2007-01-101034

♦gaORA文传2006-12-20OH

SQMORA文件2WP7-231954

62002-01-26901

ORA文M2WP7P1954

・latera«(Ei»l<

回・Aer0“C0Hie«

jDbArlkitn

*Oracle0r«2Ga«92■(JWhc.ScaD.val-xZ

国・ier。“八

・CcAfafwation5dT«<lt>AdBinistrationf«rYinds««MT

Q««XlhCA

(3Ejtttrj<>s*hctaD«t«b«s«C«oficurggAssisttfkt

9OrtOLDB

Q・97«7QtttckTouriD*ld>ai«V>sr&Ais>it*nl

争力加

OI»t«Crat»dTodtEnteij«i«•・My”Cootfi(vrationAvtttta^t

。Ortel*KIT?Strvtr—Buildtf

COMO1«I»cr9*»ftODXAdMaMrZ”

4t)R・1““DysLtigM»tCc»tic«r«tion

用在方用比）

d-rtet1。d\89—\or35nU・Meh3

：3开始aORMZLESDR?q>>'41951

十六、修改密码。

用sql*plus使用账号连接数据库

能够从菜单进入

也能够从命令行窗口进入

方法一、使用password命令

方法一、使用alter命令

Alteruser账号identifiedby密码

密码规则问题：

有字符、数字、特殊字符

要六位以上

与往常密码不能有三个以上相同的字符

第二章SQL编写注意事项

一、SELECT子句中避免使用*

在SELECT子句中列内所有的列时，使用*很方便，但是效率低。由于ORACLE在解析的过

程中，会查询数据字典，将*依次转换成所有的列名。因此，直接在SELECT子句中写出想要显示

的列。

二、查找总记录数时，尽量不要用count(*),而要指定一个有索引的字段c

比如索引列为index,使用count(index),这样能利月索引。

三、将大的历史表创建为分区表，便于数据转储与删除C

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

五、Sequence使用cache/noorder,假如在使用sequence上的列建索引，建议

加大cache值。

六、在FROM子句中包含多个表的情况下，选择记录条数最少的表作为基础表，

放在FROM子句的最后面。

由于在基于规则的优化器中,ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名。

FROM子句中写在最后的表将被最先处理。

比如：

表TAB116,384条记录

表TAB25条记录

选择TAB2作为基础表(最好的方法)

selectcount(*)fromtabi,tab2...执行时间0.96秒

选择TABI作为基础表(不佳的方法)

selectcount(*)fromtab2,tabi...执行时间26.09秒

假如有3个以上的表连接查询，那就需要选择交叉表作为基础表，交叉表是指那个被其他表

所引用的表

比如：

EMP表描述了LOCATION表与CATEGORY表的交集

SELECT*

FROMLOCATIONL,

CATEGORYC,

EMPE

WHEREE.EMPNOBETWEEN1000AND2000

ANDE.CAT_NO=C.CAT_NO

ANDE.LOCN=L.LOCN

将比下列SQL更有效率

SELECT*

FROMEMPE,

LOCATIONL,

CATEGORYC

WHEREE.CAT_NO=C.CATNO

ANDE.LOCN=L.LOCN

ANDE.EMP.NOBETWEEN1000AND2000

七、WHERE子句中的连接顺序

ORACLE使用自下而上的顺序解析叩IERE子句。根据这个原理，表之间的连接务必写在其他

WHERE条件之前，那些能够过滤掉最大数量记录的条件务必写在WHERE子句的末尾。

比如：

(低效，执行时间156.3秒)

SELECT*

FROMEMPE

WHERESAL>50000

ANDJOB='MANAGER1

AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)；

(高效，执行时间10.6W

SELECT*

FROMEMPE

WHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB='MANAGER1；

八、在需要无条件删除表中数据时，用truncate代替delete。

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

比如Where子句中有联接的列，即使最后的联接值为一个静态值，也不可能使用索引。

select*fromemployee

wherefirst_nameII''Ilast_name='BeillCliton'；

这条语句没有使用基于lastname创建的索引.

当使用下面这种SQL语句的编写，Oracle系统就能够使用基于lastjame创建的索引。

Select*fromemployee

wherefirst_namc='3ciirandlast_namc='Clitcn'；

十、带通配符（%）的like语句

比如SQL语句：

select*fromemployeewherelast_namelike'%cliton%'；

由于通配符（%）在搜寻词首出现，因此Oracle系统不使用last_namc的索引。通配符如

此使用会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查

询中索引得到了使用：

select*fromemployeewherelastnameliketc%'；

H"一、用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接在这种情

况下，使用EXISTS（或者NOTEXISTS）通常将提高查询的效率.

低效：

SELECT*

FROMEMP（基础表）

WHEREEMPNO>0

ANDDEPTNOIN（SELECTDEPTNO

FROMDEPT

WHERELOC='MELB'）

高效：

SELECT*

FROMEMP（基础表）

WHEREEMPNO>0

ANDEXISTS（SELECT'X'

FROMDEPT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC='MELB'）

十二、用NOTEXISTS替代NOTIN

在子查询中，NOTIN子句将执行一个内部的排序与合并.不管在哪种情况下,NOTIN都是最

低效的（由于它对子查询中的表执行了一个全表遍历）.为了避免使用NOTIN,我们能够把它改

写成外连接（OuterJoins）或者NOTEXISTS.

比如：

SELECT…

FROMEMP

WHEREDEPT_NONOTIN（SELECTDEPT_NO

FROMDEPT

WHEREF)EPT_CAT=,A,)：

为了提高效率.改写为：

(方法一：高效)

SELECT

FROMEMPA,DEPTB

WHEREA.DEPT_NO=B.DEPT(+)

ANDB.DEPT_NOISNULL

ANDB.DEPT_CAT(+)='A'

(方法二：最高效)

SELECT….

FROMEMPE

WHERENOTEXISTS(SELECT'X'

FROMDEPTD

WHERED.DEPT_NO=E.DEPT.NO

ANDDEPT_CAT='A')；

十三、尽可能的用UNION-ALL替换UNION

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并,

然后在输出最终结果前进行排序.

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

举例：

低效：

SELECTACCT_NUM,BALANCE_AMT

FROMDEBIT_TRANSACTTONS

WHERETRAN_DATE='31-DEC-951

UNION

SELECTACCT_NUM,BALANCE_AMT

FROMDEBIT_TRANSACTIONS

WHERETRAN_DATE='31-DEC-95'

高效：

SELECTACCT_NUM,BALANCE_AMT

FROMDEBITTRANSACTIONS

WHERETRANDATE='31-DEC-95'

UNIONALL

SELECTACCTNUM,BALANCE.AMT

FROMDEBIT_TRANSACTIONS

WHERETRAN_DATE='31-DEC-95'

十四、Orderby语句建议

ORDERBY语句决定了Oracle如何将返回的查询结果排，孑。Orderby语句对要排序的列没有

什么特别的限制，也能够将函数加入列中(象联接或者者附加等)。任何在Orderby语句的非索

引项或者者有计算表达式都将降低查询速度。

认真检查。rderby语句以找出非索引项或者者表达式，它们会降低性能。解决这个问题的办

法就是重写。rderby语句以使用索引，也能够为所使用的列建立另外一个索引，同时应绝对避

免在。rderby子句中使用表达式。

十五、避免使用NOT

在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于与不等于等等，也能

够使用and(与)、or(或者)与not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个

NOT子句的例子：

...wherenot(status='VALID')

假如要使用NOT,则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运

算符包含在另外一个逻辑运算符中，这就是不等于(◊)运算符。换句话说，即使不在查询where

子句中显式地加入NOT词，NOT仍在运算符中，见下例：

...wherestatus<>INVALID,；

再看下面这个例子：

select*fromemployeewheresalary<>3000；

对这个查询，能够改写为不使用NOT：

select*fromemployeewheresalary<3000orsalary>3000：

尽管这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询

同意Oracle对salary列使用索引，而第一种查询则不能使用索引。

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

使用DECODE函数能够配免重复扫描相同记录或者重复连接相同的表.

比如：

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREDEPT_NO=0020

ANDENAMELIKE'SMITH%'；

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREDEPT_NO=0030

ANDENAMELIKE'SMITH%';

能够用DECODE函数高效地得到相同结果

SELECTCOUNT(DECODE(DEPT_NO,0020,rXr,NULL))D0020_COUNT,

C0UNT(DECODE(DEPT_N0,0030,'X1,NULL))D0030_C0UNT,

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

SUM(DECODE(DEPTNO,0030,SAL,NULL))D0030SAL

FROMEMPWHEREENAMELIKE'SMITH%'；

类似的,DECODE函数也能够运用于GROUPBY与ORDERBY子句中.

十七、删除重复记录

DELETEFROMEMPE

WHEREE.ROWID>(SELECTMIN(X.ROWID)

FROMEMPX

WHEREX.EMP_NO=E.EMP一NO)；

十八、假如能够使用where条件，尽量不要在having中限制数据

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

引起排序的条件

-Orderby

-Groupby

-Union,intersect,minus

-Distinct

二十、使用提示(Hints)

关于表的访问，能够使用两种Hints：FULL与ROWID

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

比如：

SELECT/*+FULL(EMP)*/*

FROMEMP

WHEREEMPNO=7893；

•ROWIDhint告诉ORACLE使用TABLEACCESSBYROWID的操作访问表.

通常，你需要使用TABLEACCESSBYROWID的方式特别是当访问大表的时候，使用这种方

式，你需要明白R0IM)的值或者者使用索引。

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

SGA中，你就能够使用CACHEhint来告诉优化器把数据保留在SGA中。通常CACHEhir.t与

FULLhint一起使用。

比如：

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

FROMWORK；

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

比如：

SELECT/*+INDEX(aindexname)*/LODGING

FROMLODGINGa

WHEREMANAGER='BILLGATES'；

ORACLEhints还包含ALL.ROWS,FIRST_ROWS,RULE,USE_NL,USE_MERGE,USE_HASH等

等。能够根据具体情况具体使用。

第三章oracle与Sybase的SQL区别

一、大小写

SYBASE的SQL中数据库名、表名与列名分大小写，应遵循定义时的写法；ORACLE并不区分。

二、限制记录数量

在SYBASESQL中限制纪录的数量，需要用EXECSQLSETROWCOUNTn,用完需要执行EXEC

SQLSETROWCOUNT0恢复；而ORACLE中只需要在SQL中用SELECT*FROMwhere

rowcount<n来限制即可。

三、列的选择

ORACLE执行数据查询的时候，SELECT语句务必选择针对的数据表.在Oracle数据库内有一

种特殊的表DUAL。从DUAL表选择数据常被用来通过SELECT语句计算常数表达式，由于DUAL只

有一行数据，因此常数只返回一次。

Oracle下的DUAL查询如下所示：

SELECT'x'FROMdual

在Sybase中，查询则是下面这个样子：

SELECT'x'

四、连接

Oracle用||符号作为连接符，而Sybase的连接符是加号：+。

Oracle查询如下所示：

Select'Name'II'LastName'FromtabieName

对应的Sybase查询如下所示：

Select'Name'+'LastName'

五、字符串函数

返回字符串长度函数

Sybase：char_length(string)或者dataiength(string)

oracle：length(string)

LENGTH与LEN

Sybase：SELECTLEN(SQLMAG1)"Lengthincharacters"

oracle：SELECTLENGIHCSQLMAG')"Lengthincharacters"FROMDUAL；

六、日期函数

取当前系统日期时间

Sybase：getdateO返回datetimeeg：selectgetdateO

oracle：sysdate返回daleeg：selectsysdatefromdual；

日期的加法

Sybase：selectdatcadd(mm,12,getdateO)

oracle：selectaddmonths(sysdate,12)fromdual

日期的减法

Sybase：SELECTdatediff(dd,GetDateO.dateadd(mm,12,getdateO))

oracle：SELECTsysdate-add_months(sysdate,12)FROMdual

七、数据类型转换函数：

Sybase中转换函数为convert(datatype,expression[.style]))

在oracle中不可用，应用下列转换函数：

日期转换字符to_char(date)

比如：selecttochar(sysdate,'yyyy/mm/ddhh24：mi：ss')fromdual；

结果：1999/09/0816:25:30

selecttochar(sysdate,*yyyymmdd,)fromdual；

结果：19990908

数字转换字符tochar(numbers)

字符转换日期to_date(string)

比如：selectto_date(*1999/09/0816：25：30*Jyyyy/nun/ddhh24：mi：ss,)

fromdual;

字符转换数字to_number(string)

八、空值替代函数：

Sybase中用isnull(exprl,expr2)

oracle中不能用isnull(),只能用nvl(exprl,expr2)

比如：

Sybase：selectisnull(pro_table_status,'0')frompos.product；

oracle：selectnvl(pro_table_status,'0')frompos.product；

九、Sybase的where语句执行[]正则符号，但是oracle9i不支持。

十、数字取舍

Oracle数据库内有一个TRUNC函数，该函数返回m位十进制数的n位；假如省略m则n就

是0位。m的值能够为例.表示截去小数点左边m位数字.

在Sybase下能够用Round或者者Floor<)

T列是Oracle查询:

SELECTT1WNC(15.79.1)"Truncate"EROMDUAL；

下面是同类查询的Sybase版本：

SELECTROUND(15.79,0)rounded,ROUND(15.79,0,1)truncated

SELECTFLOOR(ROUND(15.79,0)),FLOOR(ROUND(15.79,0.1))

第四章跟踪SQL执行计划

一、理论

（一）ORACLE优化器

•Oracle的优化器有3种

基于规则RULE

基于成本COST

基于选择CHOOSE

•设置缺省的优化器，能够通过对init.ora文件中OPTIMIZERJIODE参数设置，也能够在会话

（session）级对其进行覆盖.

•假如OPTIMZER_MODE=RULE,则激活基于规则的优化器1RB0）。基于规则的优化器按照一系列

的语法规则来推测可能执行路径与比较可替换的执行路径。

•假如OPTIMZERJIODEXOST,则激活基于成本的优化器［CBO）。它使用ANALYZE语句来生成数

据库对象的统计数据。这些统计数据包含表的行数、平均长度及索引中不一致的关键字数

等。甚于这些统计数据，成本优化器能够计算出可获得的执行路径的成本。并选择具有最

小的成本执行路径。在CBO模式下，需要经常运行ANALYZE命令来确保数据的准确性。

•假如OPTIMZERMODEXHOOSE,实际的优化器模式将与是否运行过analyze命令有关.假如

table已经被analyze过，优化器模式将自动成为CBG,反之，数据库将使用RULE形式的

优化器。

（二）访问TABLE的方式

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

•全表扫描

全表扫描就是顺序地访问表中每条记录.ORACLE使用一次读入