ORACLESQL编写规范详细解释.docx
- 文档编号:23258177
- 上传时间:2023-05-15
- 格式:DOCX
- 页数:31
- 大小:102.02KB
ORACLESQL编写规范详细解释.docx
《ORACLESQL编写规范详细解释.docx》由会员分享,可在线阅读,更多相关《ORACLESQL编写规范详细解释.docx(31页珍藏版)》请在冰豆网上搜索。
ORACLESQL编写规范详细解释
数据库SQL优化原则
1.SQL语句的执行过程
2.选用适合的ORACLE优化器
ORACLE的优化器共有3种:
a.RULE(基于规则)
b.COST(基于成本)
c.CHOOSE(选择性)
可以通过对init.ora文件中OPTIMIZER_MODE参数的设置来设置缺省的优化器,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS。
也可以在SQL句级或是会话(session)级对其进行覆盖。
为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),需要经常运行analyze命令,以保证数据库中的对象统计信息(objectstatistics)的准确性。
如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。
如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.
在缺省情况下,ORACLE采用CHOOSE优化器。
●使用提示:
可以使数据库采用CHOOSE优化器,由数据库管理员定期运行analyze命令进行表分析,保证数据库中的对象统计信息的准确性。
与数据库管理员密切相关。
3.访问Table的方式
ORACLE对于表的访问采用如下两种方式:
a.全表扫描
全表扫描就是顺序地访问表中每条记录。
ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描。
b.通过ROWID访问表
采用基于ROWID的访问方式会提高访问表的效率,ROWID包含了表中记录的物理位置信息。
ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。
通常索引提供了快速访问ROWID的方法,因此基于索引列的查询可以得到性能上的提高。
●使用提示:
应尽量减少表扫描。
与开发人员密切相关。
4.使用相同的(共享)SQL语句
当向ORACLE提交一个SQL语句,ORACLE会首先在共享池中查找相同的语句。
需要说明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格、换行等)。
5.选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理。
在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。
当ORACLE处理多个表时,会运用排序及合并的方式连接它们。
首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如:
表TAB116,384条记录
表TAB21条记录
选择TAB2作为基础表(最好的方法)
selectcount(*)fromtab1,tab2执行时间0.96秒
选择TAB1作为基础表(不佳的方法)
selectcount(*)fromtab2,tab1执行时间26.09秒
如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。
例如:
EMP表的EMP_NO和CAT_NO列分别在LOCATION表和CATEGORY表中出现,EMP表是LOCATION表和CATEGORY表的交叉表。
高效语句:
SELECT*
FROMLOCATIONL,
CATEGORYC,
EMPE
WHEREE.EMP_NOBETWEEN1000AND2000
ANDE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
低效语句:
SELECT*
FROMEMPE,
LOCATIONL,
CATEGORYC
WHEREE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
ANDE.EMP_NOBETWEEN1000AND2000
●使用提示:
在进行多表连接查询时必须遵守本例。
6.WHERE子句中的连接顺序.
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:
SQL
效率
SELECT…
FROMEMPE
WHERESAL>50000
ANDJOB=‘MANAGER'
AND25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO);
低效,执行时间156.3秒
SELECT…
FROMEMPE
WHERE25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO)
ANDSAL>50000
ANDJOB=‘MANAGER';
高效,执行时间10.6秒
●使用提示:
在进行多表连接查询时必须遵守本例。
7.SELECT子句中避免使用‘*‘
在SELECT子句中列出所有的COLUMN时,使用*是一个方便的方法。
但这是一个非常低效的方法。
因为ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,将耗费更多的时间。
8.减少访问数据库的次数
当执行每条SQL语句时,ORACLE在内部执行了许多工作:
解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.
例如:
以下有三种方法可以检索出雇员号等于0342或0291的职员.
方法1(最低效)
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=342;
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=291;
方法2(次低效)
DECLARE
CURSORC1(E_NONUMBER)IS
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=E_NO;
BEGIN
OPENC1(342);
FETCHC1INTO…,..,..;
OPENC1(291);
FETCHC1INTO…,..,..;
CLOSEC1;
END;
方法3(高效)
SELECTA.EMP_NAME,A.SALARY,A.GRADE,
B.EMP_NAME,B.SALARY,B.GRADE
FROMEMPA,EMPB
WHEREA.EMP_NO=342
ANDB.EMP_NO=291;
●使用提示:
可视具体情况使用。
9.用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息。
如果执行了ROLLBACK操作,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。
而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息。
当命令运行后,数据不能被恢复。
因此很少的资源被调用,执行时间也会很短。
●使用提示:
TRUNCATE仅适用于全表删除。
10.尽量可能的多使用COMMIT
只要有可能,在程序中应尽量多使用COMMIT,这样程序的性能会得到提高,对于资源的需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redologbuffer中的空间
d.ORACLE为管理上述3种资源中的内部花费
在COMMIT时必须要考虑事务的完整性,现实中效率和事务完整性往往不可兼得的。
禁止无任何意义的开启事务。
●使用提示:
尽量少使用事务,能不用尽量不用。
11.用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。
这个处理需要排序,总计等操作。
尽量通过WHERE子句来限制记录的数目,达到减少这方面开销的目的。
例如:
SQL
效率
SELECTREGION,AVG(LOG_SIZE)
FROMLOCATION
GROUPBYREGION
HAVINGREGIONREGION!
=‘SYDNEY'
ANDREGION!
=‘PERTH'
低效
SELECTREGION,AVG(LOG_SIZE)
FROMLOCATION
WHEREREGIONREGION!
=‘SYDNEY'
ANDREGION!
=‘PERTH'
GROUPBYREGION
高效
●使用提示:
HAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等。
除此而外,限制条件必须写在WHERE子句中。
12.减少对表的查询(尽量减少子查询数量)
在含有子查询的SQL语句中,要特别注意减少对表的查询.
SQL
效率
SELECTTAB_NAME
FROMTABLES
WHERETAB_NAME=(SELECTTAB_NAME
FROMTAB_COLUMNS
WHEREVERSION=604)
AND DB_VER=(SELECTDB_VER
FROMTAB_COLUMNS
WHEREVERSION=604)
低效
SELECTTAB_NAME
FROMTABLES
WHERE(TAB_NAME,DB_VER)
=(SELECTTAB_NAME,DB_VER)
FROMTAB_COLUMNS
WHEREVERSION=604)
高效
UPDATEEMP
SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),
SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
低效
UPDATEEMP
SET(EMP_CAT,SAL_RANGE)
=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)
FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
高效
13.使用表的别名(Alias)
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上。
可以减少解析的时间并减少那些由Column歧义引起的语法错误。
Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属。
14.用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.
例:
SQL
效率
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDDEPTNOIN(SELECTDEPTNO
FROMDEPT
WHERELOC=‘MELB')
低效
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB')
高效
●使用提示:
在不导致语义发生变化情况下,必须使用EXISTS。
15.用NOTEXISTS替代NOTIN
在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.
SQL
效率
SELECT…
FROMEMP
WHEREDEPT_NONOTIN(SELECTDEPT_NO
FROMDEPT
WHEREDEPT_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');
高效
●使用提示:
在不导致语义发生变化情况下,必须使用NOTEXISTS。
16.表连接和EXISTS需要进行比较
通常来说,在同样可以达到目的时,需要比较表连接方式和EXISTS方式。
SQL
效率
SELECTENAME
FROMEMPE
WHEREEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A');
需要比较
SELECTENAME
FROMDEPTD,EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A';
需要比较
●使用提示:
在不导致语义发生变化情况下,必须使用尝试上述两种写法,选最优的。
17.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。
一般可以考虑用EXIST替换,因为EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
SQL
效率
SELECTDISTINCTDEPT_NO,DEPT_NAME
FROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
低效
SELECTDEPT_NO,DEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECT‘X'
FROMEMPE
WHEREE.DEPT_NO=D.DEPT_NO);
高效
●使用提示:
在不导致语义发生变化情况下,必须使用尝试上述两种写法,选最优的。
18.识别'低效执行'的SQL语句
用下列SQL语句找出低效SQL:
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
●使用提示:
可供使用的其他工具还有很多,这个语句可以参考。
19.用EXPLAINPLAN分析SQL语句
EXPLAINPLAN是一个很好的分析SQL语句的工具,可以在不执行SQL的情况下分析语句。
通过分析,可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。
按照从里到外,从上到下的次序解读分析的结果。
EXPLAINPLAN分析的结果是用缩进的格式排列的,最内部的操作将被最先解读,如果两个操作处于同一层中,带有最小操作号的将被首先执行。
NESTEDLOOP是少数不按照上述规则处理的操作,正确的执行路径是检查对NESTEDLOOP提供数据的操作,其中操作号最小的将被最先处理。
许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQLAnalyze都提供了方便的EXPLAINPLAN工具。
●使用提示:
推荐使用sqlplus中的SQLtrace工具。
例如:
SELECT*
FROMdept,emp
WHEREemp.deptno=dept.deptno
SQL>setautotracetraceonly/*traceonly可以不显示执行结果*/
SQL>/
14rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10NESTEDLOOPS
21TABLEACCESS(FULL)OF'EMP'
31TABLEACCESS(BYINDEXROWID)OF'DEPT'
43INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
2dbblockgets
30consistentgets
0physicalreads
0redosize
2598bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
14rowsprocessed
通过以上分析,可以得出实际的执行步骤是:
1.TABLEACCESS(FULL)OF'EMP'
2.INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)
3.TABLEACCESS(BYINDEXROWID)OF'DEPT'
4.NESTEDLOOPS(JOINING1AND3)
20.用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率。
实际上,ORACLE使用了一个复杂的自平衡B-tree结构。
通常,通过索引查询数据比全表扫描要快。
当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引。
同样在联结多个表时使用索引也可以提高效率。
另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证。
除了那些LONG或LONGRAW数据类型,可以索引几乎所有的列。
通常,在大型表中使用索引特别有效。
当然,在扫描小表时,使用索引同样能提高效率。
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。
索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改。
这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。
因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。
重建索引需要如下语句:
ALTERINDEX
●使用提示:
需要定期重建索引。
21.索引的操作
ORACLE对索引有两种访问模式.
●索引唯一扫描(INDEXUNIQUESCAN)
大多数情况下,优化器通过WHERE子句访问INDEX。
例如:
表LODGING有两个索引:
建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER。
SELECT*
FROMLODGING
WHERELODGING=‘ROSEHILL';
在ORACLE内部,上述SQL将被分成两步执行,首先,LODGING_PK索引将通过索引唯一扫描的方式被访问,获得相对应的ROWID,通过ROWID访问表的方式执行下一步检索。
如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表)。
因为检索数据保存在索引中,单单访问索引就可以完全满足查询结果。
下面SQL只需要INDEXUNIQUESCAN操作.
SELECTLODGING
FROMLODGING
WHERELODGING=‘ROSEHILL';
●索引范围查询(INDEXRANGESCAN)
适用于两种情况:
1.基于一个范围的检索
2.基于非唯一性索引的检索
例1:
SELECTLODGING
FROMLODGING
WHERELODGINGLIKE‘M%';
WHERE子句条件包括一系列值,ORACLE将通过索引范围查询的方式查询LODGING_PK。
由于索引范围查询将返回一组值,它的效率就要比索引唯一扫描低一些。
例2:
SELECTLODGING
FROMLODGING
WHEREMANAGER=‘BILLGATES';
这个SQL的执行分两步,LODGING$MANAGER的索引范围查询(得到所有符合条件记录的ROWID)和下一步同过ROWID访问表得到LODGING列的值。
由于LODGING$MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描。
由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中,所以在索引范围查询后会执行一个通过ROWID访问表的操作。
WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用。
例:
SELECTLODGING
FROMLODGING
WHEREMANAGERLIKE‘%HANMAN';
在这种情况下,ORACLE将使用全表扫描。
●使用提示:
视实际情况
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLESQL 编写 规范 详细 解释