数据库操作手册样本

ORACLE数据库操作手册2.0中华人民共和国通信集团公司安徽有限公司计费业务部２００7年11月修改记录版本日期编辑者编辑内容1.0/见春蕾形成草稿1.1/8/31见春蕾依照实际状况修订某些章节2.0/11/05见春蕾一、在第一章（数据库使用注意事项）中增长了关于批量更新数据大事务分次提交规定、客户端配备办法、修改密码办法；二、在第二章（SQL编写注意事项）中增长了关于提示(Hints)使用。三、增长了第四章（跟踪SQL执行筹划），阐述了有关理论知识和SQL执行筹划跟踪办法。目录ORACLE数据库操作手册2.0 1第一章数据库使用注意事项 5二、不使用数据库时请及时关闭数据库连接，但是也不能频繁连接和断开 5三、执行了DML操作，请按业务规则，不要忘掉执行COMMIT或ROLLBACK。 5四、如果是查询和记录不涉及到当天业务时，不要在生产环境里操作，在BCV库中操作。BCV每天晚上12点同步一次，数据和顾客口令、密码和生产环境相似。 5五、关联表都很大查询和记录也尽量用BCV库。 6六、生产环境营业时间（特别是营业高峰时间，当前是上午8:00-10:00，下午3:00-4:00）禁止做大数据量查询和记录，每个查询执行时间要控制在１分钟内。 6七、不要执行索引和表信息收集。 6八、编写程序时候，注意ＳＱＬ语句规范，尽量使用变量绑定，减少共享池使用。 6九、按照原则规定编写pl/sql等程序，注意事务提交、回滚和对各种异常状况解决。 6十、要查看表字段名或随机少量数据时候，使用desc、也可以使用where1=2或者rowcount<n来查看，而尽量不要直接执行select*fromtablename，然后kill会话。 6十一、尽量使用索引，避免浮现全表扫描，性能影响比单机更大。 6十二、对分区表建立索引时，使用local选项。 6十三、不要在事务中引入Trigger，建议在事务中实现。 6十四、批量更新数据大事务分次提交。 6十五、客户端配备。 7十六、修改密码。 7第二章SQL编写注意事项 9一、SELECT子句中避免使用* 9二、查找总记录数时，尽量不要用count(*)，而要指定一种有索引字段。 9三、将大历史表创立为分区表，便于数据转储和删除。 9四、使用分区表进行查询时，尽量把分区键作为查询条件第一种条件。 9五、Sequence采用cache/noorder，如果在使用sequence上列建索引，建议加大cache值。 9六、在FROM子句中包括各种表状况下，选取记录条数至少表作为基本表，放在FROM子句最背面。 9七、WHERE子句中连接顺序 10八、在需要无条件删除表中数据时，用truncate代替delete。 11九、语句中尽量使用表索引字段，避免做大表全表扫描。 11十、带通配符（%）like语句 11十一、用EXISTS代替IN 11十二、用NOTEXISTS代替NOTIN 12十三、尽量用UNION-ALL替代UNION 12十四、Orderby语句建议 13十五、避免使用NOT 13十六、使用DECODE函数来减少解决时间 14十七、删除重复记录 14十八、如果可以使用where条件，尽量不要在having中限制数据 14十九、尽量不要使数据排序 14二十、使用提示(Hints) 15第三章oracle和sybaseSQL区别 15一、大小写 15二、限制记录数量 15三、列选取 16四、连接 16五、字符串函数 16六、日期函数 16七、数据类型转换函数: 17八、空值代替函数: 17九、sybasewhere语句执行[]正则符号，但是oracle9i不支持。 17十、数字取舍 17第四章跟踪SQL执行筹划 18一、理论 18（一）ORACLE优化器 18（二）访问TABLE方式 18（三）索引访问方式 19二、SETTRACE跟踪sql执行筹划 19第一章数据库使用注意事项一、对BOSS1.5营帐库，营业网址严格按照规定进行配备，不可随意更换。营业网址规定按照下面方式进行分派配备，如果随意更换，会增长营业主机间数据交互，影响数据库性能，减少营业工作效率。合肥、六安、阜阳、宿州、亳州、淮北、黄山、铜陵配备：:7001/WebRoot/login.jsp或者芜湖、蚌埠、淮南、马鞍山、安庆、滁州、宣城、巢湖、池州，配备如下::8001/WebRoot/login.jsp或者二、不使用数据库时请及时关闭数据库连接，但是也不能频繁连接和断开数据库连接也是数据库宝贵资源，数据库支持数据库连接有限，当不需要使用数据库时，请“优雅”退出数据库吧，如果能正常退出，请别“结束任务”或KILL-9。如果正在执行SQL时候突然异常终端，请联系数据库管理员检查解决，以防止数据库始终占用该SQL有关资源。三、执行了DML操作，请按业务规则，不要忘掉执行COMMIT或ROLLBACK。不要只执行语句，而不控制事务。当你执行一条DML语句时，数据库会为你分派锁、回滚段、REDOLOGBUFFER等资源。事务结束后，这些资源才干得以释放。四、如果是查询和记录不涉及到当天业务时，不要在生产环境里操作，在BCV库中操作。BCV每天晚上12点同步一次，数据和顾客口令、密码和生产环境相似。bcv是一种节点数据库，所有地市查询连接配备是同一种，如下：YZDBBCV=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=5)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=yzdb)))五、关联表都很大查询和记录也尽量用BCV库。六、生产环境营业时间（特别是营业高峰时间，当前是上午8:00-10:00，下午3:00-4:00）禁止做大数据量查询和记录，每个查询执行时间要控制在１分钟内。七、不要执行索引和表信息收集。八、编写程序时候，注意ＳＱＬ语句规范，尽量使用变量绑定，减少共享池使用。九、按照原则规定编写pl/sql等程序，注意事务提交、回滚和对各种异常状况解决。十、要查看表字段名或随机少量数据时候，使用desc、也可以使用where1=2或者rowcount<n来查看，而尽量不要直接执行select*fromtablename，然后kill会话。十一、尽量使用索引，避免浮现全表扫描，性能影响比单机更大。十二、对分区表建立索引时，使用local选项。十三、不要在事务中引入Trigger，建议在事务中实现。十四、批量更新数据大事务分次提交。在营帐数据库系统繁忙时候，不不大于300万数据刷新，建议分次提交，减少异常发生。系统空闲时，不不大于800万数据刷新，建议分次提交。十五、客户端配备。办法一、直接修改tnsnames.oraYZDBBCV=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=5)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=yzdb)))办法二、使用客户端工具十六、修改密码。用sql*plus使用账号连接数据库可以从菜单进入也可以从命令行窗口进入办法一、使用password命令办法二、使用alter命令Alteruser账号identifiedby密码密码规则问题：有字符、数字、特殊字符要六位以上和此前密码不能有三个以上相似字符第二章SQL编写注意事项一、SELECT子句中避免使用*在SELECT子句中列出所有列时，使用*很以便，但是效率低。由于ORACLE在解析过程中，会查询数据字典，将*依次转换成所有列名。因此，直接在SELECT子句中写出想要显示列。二、查找总记录数时，尽量不要用count(*)，而要指定一种有索引字段。例如索引列为index，使用count(index)，这样能运用索引。三、将大历史表创立为分区表，便于数据转储和删除。四、使用分区表进行查询时，尽量把分区键作为查询条件第一种条件。五、Sequence采用cache/noorder，如果在使用sequence上列建索引，建议加大cache值。六、在FROM子句中包括各种表状况下，选取记录条数至少表作为基本表，放在FROM子句最背面。由于在基于规则优化器中，ORACLE解析器按照从右到左顺序解决FROM子句中表名。FROM子句中写在最后表将被最先解决。例如：表TAB116,384条记录表TAB25条记录选取TAB2作为基本表(最佳办法)selectcount(*)fromtab1,tab2...执行时间0.96秒选取TAB1作为基本表(不佳办法)selectcount(*)fromtab2,tab1...执行时间26.09秒如果有3个以上表连接查询，那就需要选取交叉表作为基本表，交叉表是指那个被其她表所引用表例如:EMP表描述了LOCATION表和CATEGORY表交集SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000ANDANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN将比下列SQL更有效率SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND七、WHERE子句中连接顺序ORACLE采用自下而上顺序解析WHERE子句。依照这个原理，表之间连接必要写在其她WHERE条件之前，那些可以过滤掉最大数量记录条件必要写在WHERE子句末尾。例如：(低效,执行时间156.3秒)SELECT*FROMEMPEWHERESAL>50000ANDJOB='MANAGER'AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT*FROMEMPEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL>50000ANDJOB='MANAGER';八、在需要无条件删除表中数据时，用truncate代替delete。九、语句中尽量使用表索引字段，避免做大表全表扫描。例如Where子句中有联接列，虽然最后联接值为一种静态值，也不会使用索引。select*fromemployeewherefirst_name||''||last_name='BeillCliton'；这条语句没有使用基于last_name创立索引。当采用下面这种SQL语句编写，Oracle系统就可以采用基于last_name创立索引。Select*fromemployeewherefirst_name='Beill'andlast_name='Cliton'；十、带通配符（%）like语句

例如SQL语句：

select*fromemployeewherelast_namelike'%cliton%'；

由于通配符（%）在搜寻词首浮现，因此Oracle系统不使用last_name索引。通配符如此使用会减少查询速度。当通配符出当前字符串其她位置时，优化器就能运用索引。在下面查询中索引得到了使用：

select*fromemployeewherelast_namelike'c%';十一、用EXISTS代替IN在许多基于基本表查询中,为了满足一种条件,往往需要对另一种表进行联接.在这种状况下，使用EXISTS(或NOTEXISTS)普通将提高查询效率.低效：SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC='MELB')高效：SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDEXISTS(SELECT'X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')十二、用NOTEXISTS代替NOTIN在子查询中,NOTIN子句将执行一种内部排序和合并.无论在哪种状况下,NOTIN都是最低效(由于它对子查询中表执行了一种全表遍历).为了避免使用NOTIN,咱们可以把它改写成外连接(OuterJoins)或NOTEXISTS.例如：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A')；为了提高效率.改写为：(办法一：高效)SELECT….FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)='A'(办法二：最高效)SELECT….FROMEMPEWHERENOTEXISTS(SELECT'X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT='A');

十三、尽量用UNION-ALL替代UNION当SQL语句需要UNION两个查询成果集合时,这两个成果集合会以UNION-ALL方式被合并，然后在输出最后成果迈进行排序.如果用UNIONALL代替UNION，这样排序就不是必要了.效率就会因而得到提高.举例：低效：SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE='31-DEC-95'UNIONSELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE='31-DEC-95'高效：SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE='31-DEC-95'UNIONALLSELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE='31-DEC-95'十四、Orderby语句建议ORDERBY语句决定了Oracle如何将返回查询成果排序。Orderby语句对要排序列没有什么特别限制，也可以将函数加入列中（象联接或者附加等）。任何在Orderby语句非索引项或者有计算表达式都将减少查询速度。仔细检查orderby语句以找出非索引项或者表达式，它们会减少性能。解决这个问题办法就是重写orderby语句以使用索引，也可觉得所使用列建立此外一种索引，同步应绝对避免在orderby子句中使用表达式。十五、避免使用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)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKE'SMITH%'；SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKE'SMITH%'；可以用DECODE函数高效地得到相似成果SELECTCOUNT(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_SALFROMEMPWHEREENAMELIKE'SMITH%'；类似,DECODE函数也可以运用于GROUPBY和ORDERBY子句中.十七、删除重复记录DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO)；十八、如果可以使用where条件，尽量不要在having中限制数据十九、尽量不要使数据排序引起排序条件-Orderby-Groupby-Union,intersect,minus-Distinct二十、使用提示(Hints)对于表访问,可以使用两种Hints：FULL和ROWIDFULLhint告诉ORACLE使用全表扫描方式访问指定表.例如：SELECT/*+FULL(EMP)*/*FROMEMPWHEREEMPNO=7893；ROWIDhint告诉ORACLE使用TABLEACCESSBYROWID操作访问表.普通，你需要采用TABLEACCESSBYROWID方式特别是当访问大表时候，使用这种方式，你需要懂得ROIWD值或者使用索引。如果一种大表没有被设定为缓存(CACHED)表而你但愿它数据在查询结束是依然停留在SGA中,你就可以使用CACHEhint来告诉优化器把数据保存在SGA中。普通CACHEhint和FULLhint一起使用。例如：SELECT/*+FULL(WORKER)CACHE(WORKER)*/*FROMWORK；索引hint告诉ORACLE使用基于索引扫描方式.你不必阐明详细索引名称例如：SELECT/*+INDEX(aindex_name)*/LODGINGFROMLODGINGaWHEREMANAGER=‘BILLGATES'；ORACLEhints还涉及ALL_ROWS，FIRST_ROWS，RULE,USE_NL，USE_MERGE，USE_HASH等等。可以依照详细状况详细使用。第三章oracle和sybaseSQL区别一、大小写SYBASESQL中数据库名、表名和列名分大小写，应遵循定义时写法；ORACLE并不区别。二、限制记录数量在SYBASESQL中限制纪录数量,需要用EXECSQLSETROWCOUNTn，用完需要执行EXECSQLSETROWCOUNT0恢复；而ORACLE中只需要在SQL中用SELECT*FROMtbl_namewhererowcount<n来限制即可。三、列选取ORACLE执行数据查询时候，SELECT语句必要选取针对数据表。在Oracle数据库内有一种特殊表DUAL。从DUAL表选取数据常被用来通过SELECT语句计算常数表达式，由于DUAL只有一行数据，因此常数只返回一次。Oracle下DUAL查询如下所示：SELECT'x'FROMdual在sybase中，查询则是下面这个样子：SELECT'x'四、连接Oracle用||符号作为连接符，而sybase连接符是加号：+。Oracle查询如下所示：Select'Name'||'LastName'FromtableName相应sybase查询如下所示：Select'Name'+'LastName'五、字符串函数返回字符串长度函数sybase：char_length(string)或datalength(string)oracle：length(string)LENGTH和LENsybase：SELECTLEN('SQLMAG')"Lengthincharacters"oracle：SELECTLENGTH('SQLMAG')"Lengthincharacters"FROMDUAL;六、日期函数取当前系统日期时间sybase：getdate()返回datetimeeg:selectgetdate()oracle: sysdate返回dateeg:selectsysdatefromdual; 日期加法sybase:selectdateadd(mm,12,getdate())oracle:selectadd_months(sysdate,12)fromdual日期减法

sybase:SELECTdatediff(dd，GetDate(),dateadd(mm,12,getdate()))oracle:SELECTsysdate-add_months(sysdate,12)FROMdual七、数据类型转换函数:sybase中转换函数为convert(datatype,expression[,style]))在oracle中不可用,应用如下转换函数:日期转换字符to_char(date)例如:selectto_char(sysdate,’yyyy/mm/ddhh24:mi:ss’)fromdual;成果：1999/09/0816:25:30 selectto_char(sysdate,’yyyymmdd’)fromdual;成果：19990908数字转换字符to_char(numbers)字符转换日期to_date(string)例如:selectto_date(‘1999/09/0816:25:30’,’yyyy/mm/ddhh24:mi:ss’)fromdual;字符转换数字to_number(string)八、空值代替函数:sybase中用 isnull(expr1,expr2)oracle 中不能用isnull(),只能用nvl(expr1,expr2)例如:sybase：selectisnull(pro_table_status，'0')fromduct；oracle： selectnvl(pro_table_status，'0')fromduct；九、sybasewhere语句执行[]正则符号，但是oracle9i不支持。十、数字取舍Oracle数据库内有一种TRUNC函数，该函数返回m位十进制数n位；如果省略m则n就是0位。m值可觉得负，表达截去小数点左边m位数字。在Sybase下可以用Round或者Floor。如下是Oracle查询：SELECTTRUNC(15.79,1)"Truncate"FROMDUAL;下面是同类查询sybase版本：SELECTROUND(15.79，0)rounded，ROUND(15.79，0,1)truncatedSELECTFLOOR(ROUND(15.79，0))，FLOOR(ROUND(15.79，0,1))第四章跟踪SQL执行筹划一、理论（一）ORACLE优化器Oracle优化器有3种基于规则RULE基于成本COST基于选取CHOOSE设立缺省优化器,可以通过对init.ora文献中OPTIMIZER_MODE参数设立，也可以在会话(session)级对其进行覆盖.如果OPTIMZER_MODE=RULE，则激活基于规则优化器(RBO)。基于规则优化器按照一系列语法规则来推测也许执行途径和比较可替代执行途径。如果OPTIMZER_MODE=COST，则激活基于成本优化器(CBO)。它使用ANALYZE语句来生成数据库对象记录数据。这些记录数据涉及表行数、平均长度及索引中不同核心字数等。基于这些记录数据，成本优化器可以计算出可获得执行途径成本。并选取具备最小成本执行途径。在CBO模式下，需要经常运营ANALYZE命令来保证数据精确性。如果OPTIMZER_MODE=CHOOSE，实际优化器模式将和与否运营过analyze命令关于.如果table已经被analyze过，优化器模式将自动成为CBO，反之，数据库将采用RULE形式优化器。（二）访问TABLE方式ORACLE采用两种访问表中记录方式:全表扫描

全表扫描就是顺序地访问表中每条记录.

ORACLE采用一次读入各种数据块方式优化全表扫描。ROWID定为访问

ORACLE采用索引实现了数据和存储数据物理位置(ROWID)之间联系。普通索引提供了迅速访问ROWID办法,因而那些基于索引列查询就可以得到性能上提高。（三）索引访问方式Oracle有两种索引访问方式索引唯一扫描

(

INDEX

UNIQUE

SCAN)如:表LOADING有两个索引

:

建立在LOADING列上唯一性索引LOADING_PK和建立在MANAGER列上非唯一性索引IDX_MANAGER。

SELECT

*FROM

LOADINGWHERE

LOADING

=

‘ROSE

HILL’;

在内部，上述SQL将被提成两步执行，一方面，LOADING_PK

索引将通过索引唯一扫描方式被访问，获得相相应ROWID，通过ROWID访问表方式执行下一步检索。

如果被检索返回列涉及INDEX列中，ORACLE将不执行第二步解决(通过ROWID访问表)。

由于检索数据保存在索引中，单单访问索引就可以完全满足查询成果。

SELECT

LOADING

FROM

LOADINGWHERE

LOADING

=

‘ROSE

HILL’;2、索引范畴查询(INDEX

RANGE

SCAN)合用于两种状况:1、

基于一种范畴检索2、

基于非唯一性索引检索

例1:

SELECT

LOADING

FROM

LOADINGWHERE

LOADING

LIKE

‘M%’;WHERE子句条件涉及一系列值，

ORACLE