Oracle数据库中SQL语句的优化技巧

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法:

1. /*+ALL_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

例如:

SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2. /*+FIRST_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

例如:

SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/

表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;

表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

例如:

SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/

表明对语句块选择基于规则的优化方法.

例如:

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/

表明对表选择全局扫描的方法.

例如:

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/

提示明确表明对指定表根据ROWID进行访问.

例如:

SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/

提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

例如:

SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/

表明对表选择索引的扫描方法.

例如:

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明对表选择索引升序的扫描方法.

例如:

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

10. /*+INDEX_COMBINE*/

为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

例如:

SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL< 5000000 AND HIREDATE< SYSDATE;

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

提示明确命令优化器使用索引作为访问路径.

例如:

SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL< 60000;

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明对表选择索引降序的扫描方法.

例如:

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/

对指定的表执行快速全索引扫描,而不是全表扫描的办法.

例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

提示明确进行执行规划的选择,将几个单列索引的扫描合起来.

例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

15. /*+USE_CONCAT*/

对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.

例如:

SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

16. /*+NO_EXPAND*/

对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.

例如:

SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

17. /*+NOWRITE*/

禁止对查询块的查询重写操作.

18. /*+REWRITE*/

可以将视图作为参数.

19. /*+MERGE(TABLE)*/

能够对视图的各个查询进行相应的合并.
例如:

SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/

对于有可合并的视图不再合并.

例如:

SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/

根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.

例如:

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

例如:

SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/

将指定的表与其他行源通过合并排序连接方式连接起来.

例如:

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/

将指定的表与其他行源通过哈希连接方式连接起来.

例如:

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/

强制与ORACLE所选择的位置不同的表进行查询执行.

例如:

SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/

将指定的表作为连接次序中的首表.

27. /*+CACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/

直接插入到表的最后,可以提高速度.

insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/

通过在插入语句生存期内停止并行模式来启动常规插入.

insert /*+noappend*/ into test1 select * from test4 ;

以上所述是小编给大家介绍的Oracle数据库中SQL语句的优化技巧,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • Oracle SQL性能优化系列学习二

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习二.  4. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(

  • Oracle SQL性能优化系列学习三

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习三.8. 使用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 'SM

  • ORACLE SQL-UPDATE、DELETE、INSERT优化和使用技巧分享

    UPDATE  1.先备份数据(安全.提高性能). 2.分批更新,小批量提交,防止锁表. 3.如果被更新的自动有索引,更新的数据量很大,先取消索引,再重新创建. 4.全表数据更新,如果表非常大,建议以创建新表的形式替代更新. DELETE 1.分批提交.减少锁表时间.减少回滚段压力. 2.大批量数据删除加上rownum<1000. 3.大批量删除,禁止中途ctrl+c,或kill调session. 4.大量数据删除后最好重建索引,分析表. INSERT 1.关闭redo log(ALTER TA

  • Oracle SQL tuning 数据库优化步骤分享(图文教程)

    SQL Turning 是Quest公司出品的Quest Central软件中的一个工具.Quest Central是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理 Oracle.DB2 和 SQL server 数据库. 一.SQL Tuning for SQL Server简介 SQL语句的优化对发挥数据库的最佳性能非常关键.然而不幸的是,应用优化通常由于时间和资源的因素而被忽略.SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能化的

  • oracle数据库sql的优化总结

    一:使用where少使用having; 二:查两张以上表时,把记录少的放在右边: 三:减少对表的访问次数: 四:有where子查询时,子查询放在最前: 五:select语句中尽量避免使用*(执行时会把*依次转换为列名): 六:尽量多的使用commit: 七:Decode可以避免重复扫描相同的记录或重复连接相同的表: 八:通过内部函数也可提高sql效率: 九:连接多个表时,使用别名并把别名前缀于每个字段上: 十:用exists代替in 十一:not exists代替 not in(not in 字

  • oracle数据库优化辅助SQL语句

    具体详情请看下文代码分析. --查询正在执行的SQL语句 SELECT OSUSER 电脑登录身份, PROGRAM 发起请求的程序, USERNAME 登录系统的用户名, SCHEMANAME, B.Cpu_Time 花费cpu的时间, STATUS, B.SQL_TEXT 执行的sql, B. FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE

  • Oracle SQL性能优化系列学习一

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习一.1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,

  • oracle下一条SQL语句的优化过程(比较详细)

    原来的语句是这样的: select sum(sl0000) from xstfxps2 where dhao00 in ( select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate) and khdm00='500000003913'); 已用时间: 00: 02: 49.04 Execution Plan ---------------------------------------------------------- 0 S

  • Oracle数据库中SQL语句的优化技巧

    在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

  • oracle数据库中sql%notfound的用法详解

    SQL%NOTFOUND 是一个布尔值.与最近的sql语句(update,insert,delete,select)发生交互,当最近的一条sql语句没有涉及任何行的时候,则返回true.否则返回false.这样的语句在实际应用中,是非常有用的.例如要update一行数据时,如果没有找到,就可以作相应操作.如: begin update table_name set salary = 10000 where emp_id = 10; if sql%notfound then insert into

  • Oracle数据库中SQL开窗函数的使用

    开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成.为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决.目前在 MSSQLServer.Oracle.DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持. 开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值

  • JavaScript中条件语句的优化技巧总结

    对多个条件使用 Array.includes function test(fruit) { if (fruit == 'apple' || fruit == 'strawberry') { console.log('red'); } } 上面的例子看起来不错.然而,如果还有更多红颜色的水果需要判断呢,比如樱桃和小红莓,我们要用更多的 ||来扩展这个表述吗? 我们可以用 Array.includes 重写上面的条件! function test(fruit) { const redFruits =

  • Oracle在Mybatis中SQL语句的配置方法

    数据库中有下划线的字段在实体中应采用驼峰命名法,如P_NAME对应pName,实例如下: 1.XML文件中SQL语句配置(Geteway.xml文件) <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-m

  • 从Oracle数据库中读取数据自动生成INSERT语句的方法

    Oracle INSERT 语句 方法1 我估计有点 SQL 基础的人都会写 INSERT 语句.下面是 SQL 标准写法. INSERT INTO employees (employee_id, name) VALUES (1, 'Zhangsan'); INSERT INTO employees VALUES (1, 'Shangbo'); 方法2 其实, Oracle 还支持下面的写法,作用和上面的语句完全相同. INSERT INTO (SELECT employee_id, name

  • Oracle如何在SQL语句中对时间操作、运算

    目录 0.date与timestamp 1.获取系统当前时间 2.ORACLE里获取一个时间的年.季.月.周.日的函数: 3.日期操作 4.常用的时间戳 5.查询某时间范围 总结 0.date与timestamp 1)区别 date精确到年月日时分秒,timestamp更精确一些: 但这个不重要,重要的是,实践中我从Oracle数据库取date类型字段,前端展示时分秒都是0,网上说数据库类型是date取到前端就是这样,只能精确到日,后面都是默认填0:我给字段换成timestamp确实问题解决了,

  • Oracle中SQL语句连接字符串的符号使用介绍

    Oracle中SQL语句连接字符串的符号为|| 复制代码 代码如下: select catstr(tcdm) || (',') from T_YWCJ_RWCJR where cjrjh='009846' and rwid='12050' and jsdm='CJY' 拼接成一条数据并连接一个","

  • Oracle中sql语句(+)符号代表连接的使用讲解

    oracle中sql语句(+)符号代表连接 (+)在=前边为右连接 (+)在=后边为左连接 SELECT a.*, b.* from a(+) = b就是一个右连接,等同于select a.*, b.* from a right join b SELECT a.*, b.* from a = b(+)就是一个左连接,等同于select a.*, b.* from a left join b 内连接 常用的连接运算符=.<.> 使用比较运算符根据每个表共有的列的值匹配两个表中的行 外连接 左连接

  • 详解Oracle数据库中自带的所有表结构(sql代码)

    目录 一.tb_emp(员工表) 1.建表 2.导入数据 3.查表 二.tb_dept(部门表) 1.建表 2.导入数据 3.查表 三.tb_bonus(奖金表) 1.建表 2.导入数据 3.查表 四.tb_salgrade(工资等级表) 1.建表 2.导入数据 3.查表 五.tb_users(用户表) 1.建表 2.导入数据 3.查表 六.tb_saldetail(工资详细表) 1.建表 2.导入数据 3.查表 一.tb_emp(员工表) 1.建表 CREATE TABLE "TEST&quo

随机推荐

其他