- 浏览: 142313 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
yizishou:
很详细,不错
Oracle 索引的五种类型 -
xinyoulinglei:
学习了 不错的文章 要是里面在有一些案例的说明就更好了
oracle,Cannot SET AUTOTRACE解决问题 -
xiaolobster:
我想问一下 上面这个函数,如果字符串超长了如何调整?拼成的字 ...
拼字符串 将多行拼成一行 -
DataBird:
非常感谢你无私的奉献,这个函数太棒了!!!
拿来可以直接用。
...
拼字符串 将多行拼成一行
1. SELECT子句中避免使用 “*” 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 2.使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例如: Sql代码 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%’; 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函数高效地得到相同结果: Sql代码 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%’; 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子句中. 3.删除重复记录 最高效的删除重复记录方法 ( 因为使用了ROWID) Sql代码 DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 4. 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下,回滚段(rollback segments ) 用来存放可以被恢复的信息,如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况),而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 5.计算记录条数 和一般的观点相反, count(*) 比count(1)稍快 ,当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO) 6.用Where子句替换HAVING子句 避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、总计等操作,如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销, 例如: Sql代码 --低效 SELECT REGION,AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ --高效 SELECT REGION,AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION --低效 SELECT REGION,AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ --高效 SELECT REGION,AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION 7. 用EXISTS替代IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. Sql代码 --低效 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 DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’) --低效 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 DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’) 8.用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’); Sql代码 --为了提高效率改写为: (方法一: 高效) SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’ -- (方法二: 最高效) SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); --为了提高效率改写为: (方法一: 高效) SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’ -- (方法二: 最高效) SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); 9.用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换 例如: Sql代码 --低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO --高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); --EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. --低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO --高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); --EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 10. 用索引提高效率 索引是表的一个概念部分,用来提高检索数据的效率,实际上ORACLE使用了一个复杂的自平衡B-tree结构,通常通过索引查询数据比全表扫描要快,当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引, 同样在联结多个表时使用索引也可以提高效率,另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证,除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率,虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改,这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O, 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢 注:定期的重构索引是有必要的. 11. 避免在索引列上使用计算 WHERE子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描. 举例: Sql代码 --低效: SELECT …FROM DEPT WHERE SAL * 12 > 25000; --高效: SELECT … FROM DEPT WHERE SAL > 25000/12; --低效: SELECT …FROM DEPT WHERE SAL * 12 > 25000; --高效: SELECT … FROM DEPT WHERE SAL > 25000/12; 12. 用>=替代> Sql代码 --如果DEPTNO上有一个索引 --高效: SELECT * FROM EMP WHERE DEPTNO >=4 --低效: SELECT * FROM EMP WHERE DEPTNO >3 --如果DEPTNO上有一个索引 --高效: SELECT * FROM EMP WHERE DEPTNO >=4 --低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
发表评论
-
联机事务处理(OLTP)和联机分析处理(OLAP)的不同
2012-02-28 15:07 1244联机事务处理(OLTP)和联机分析处理(OLAP) ... -
SQL Server 和 Oracle 数据类型对应关系
2012-02-02 08:40 1029SQL Server 和 Oracle 数据类型对应 ... -
SQL的硬解析和软解析
2011-12-13 08:57 1270我们都知道在Oracle中每条SQL语句在执行之前都需要经 ... -
Optimizer CBO RBO
2011-12-12 16:17 820Oracle 数据库中优化器(Optimizer)是SQL ... -
ORA-01555
2011-12-12 15:59 812ORA-01555 快照过旧,是数据库中很常见的一个错误, ... -
Oracle 分区表
2011-12-12 15:36 870一. 分区表理论知识 Oracle提供了分区技 ... -
DB 迁移到Data Guard 实施方案
2011-12-08 13:35 688原DB是 Linux Heartbeat 的HA 系统。 ... -
exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
2011-12-08 11:31 1181关于exp/imp 和 expdp/impdp, 之前整理 ... -
限制IP 访问 Oracle 的方法
2011-12-08 10:48 945一. 修改sqlnet.ora 文件 sqlnet.o ... -
ORACLE 百万行数据查询优化
2011-12-01 09:36 7121.对查询进行优化,应尽量避免全表扫描,首先应考虑在 wher ... -
如何恢复一个被误drop的存储过程
2011-12-01 09:37 648这种恢复是非常容易的,原理就是利用了oracle里所有的存储过 ... -
Oracle密码文件
2011-12-05 11:49 738创建Oracle密码文件的命令 orapwd fil ... -
Oracle误删除表数据后的恢复详解
2011-12-05 11:49 753在过去,如果用户误删/更新了数据后,作为用户并没有什么直 ... -
Oracle10g新特性:临时表空间组(temporary tablespace group)
2011-12-05 11:49 884Oracle10g新特性:临时表空间组(temporar ... -
sql中 in 、not in 、exists、not exists 用法和差别
2011-12-01 09:37 689exists (sql 返回结果集为真) not e ... -
表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系
2011-12-01 09:36 592Oracle表空间(tableSpace)、段 (se ... -
Oracle bitand()和trunc()函数使用方法
2011-12-01 09:36 867Oracle bitand()和trunc()函数使用方 ... -
关系数据库设计范式
2011-11-30 11:10 577关系数据库设计范式 ... -
SQLSERVER ORACLE MYSQL高效分页查询
2011-11-30 11:08 673SQLSERVER ORACLE MYSQL高效分页查询 ... -
Oracle对大对象类型操作:blob,clob,nclob,bfile
2011-11-30 11:05 661Oracle对大对象类型操 ...
相关推荐
Oracle 常用SQL技巧经典收藏 Oracle 常用SQL技巧经典收藏
oracle常用操作技巧,包括oracle安装步骤、网络配制、EM、备份、RMAN的备份与恢复、逻辑备份、闪回、ASM(管理存储软件)、数据迁移、安全控制、oracle安装及常规操作、SQL语句、函数、SQL语句、权限、体系结构、空间...
常用SQL书写技巧,包括sqlserver、oracle等
Oracle sql性能优化技巧~~~~~~~~~~~~~~
常用SQL操作技巧,ORACLE,SQL SERVER
作者通过总结各自多年的软件开发和教学培训经验,与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在,内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧:...
《Oracle查询优化改写技巧与案例》不讲具体语法,只是以案例的形式介绍各种查询语句的用法。第1~4章是基础部分,讲述了常用的各种基础语句,以及常见的错误和正确语句的写法。这部分的内容应熟练掌握,因为日常查询...
平时常用一些sql 包裹的 表的 创建授权 批量插入数据 更新数据 等 复杂查询
《Oracle查询优化改写技巧与案例》不讲具体语法,只是以案例的形式介绍各种查询语句的用法。第1~4章是基础部分,讲述了常用的各种基础语句,以及常见的错误和正确语句的写法。这部分的内容应熟练掌握,因为日常查询...
针对oracle10g整理的OCP常用SQL语句,偏使用,不包含RMAN,主要是对常用SQL语句,关键字进行整理汇总,还有一些小技巧和一些基本概念在里面
13. Oracle常用数据字典 14. ORACLE回滚段管理(上) 15. ORACLE回滚段管理(下) 16. Oracle基于Client-Server的性能调整 17. Oracle数据库的安全策略 18. ORACLE数据库简介 19. Oracle数据库密码文件的使用...
ppt文件,介绍oracle优化的一些常用的技巧之类的。
南京的死锁问题今日点评优化小技巧 Oracle的死锁优化小技巧 几个常用的与锁相关的脚本5月20日 凌晨的邮件通知短信今日点评优化小技巧 /10028事件优化小技巧 PL/SQL 优化工具profiler5月22日 ODS系统和RAC优化小技巧 ...
ORACLE SQL语句编写与调优 ORACLE交流群48949977 内容简介 第一部分 SQL基础 SQL简介 发展历史 SQL语句分类(DDL、DML、DQL、DCL) 内容简介 第五部分 SQL优化 优化器 SQL语句执行过程 SQL优化术语 第一...
常用技巧 Oracle PL/SQL基础 PL/SQL 块结构和组成元素 PL/SQL 处理流程 光标的使用 错误处理 存储过程和函数 创建包和使用包 触发器 外部存储过程 会话间通信 数据库作业和文件I/O 在PL/SQL 使用SQL语句 PL/SQL程序...
各种复杂题型sql语句 ,oracle常用技巧讲解
以下对TOAD有关疑问解答和技巧描述都是基于Toad for Oracle Xpert(Version 11.6.1.6,Toad for Oracle Xpert是Toad调优版,包括了专业版全部功能。额外增加了SQL调优功能。),如果与大家在实际工作中遇到情况有所...
sqlplus在启动时会自动运行两个脚本:glogin.sql、login.sql这两个文件 ...可以在login.sql文件中加入一些常用设置使用SQLPLUS时更便捷,以下试验在ORACLE 11.2.0.1.0上进行 默认$ORACLE_HOME/sqlplus/admin路径下存在gl
7.5.2 PL/SQL中常用的变量类型: 114 7.5.3 变量声明 114 7.5.4 简单变量赋值 114 7.5.5 %type属性 114 7.5.6 %rowtype属性 114 7.5.7 SQL语句在PL/SQL中的运用 114 7.6 选择结构 114 7.6.1 格式1: IF_THEN_ELSE语句...