精华内容
下载资源
问答
  • oraclesql优化技巧
    2021-05-08 20:13:25

    ORACLE SQL 优化的若干方法

    一、 先介绍一下oracle的SGA:数据库的系统全局区,SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区。 1、共享池又由两部分构成:共享SQL区和数据字典缓冲区。共享SQL区专门存放用户SQL命令,oracle使用最近最少使用等优先级算法来更新覆盖;数据字典缓冲区(library cache)存放数据库运行的动态信息。数据库运行一段时间后,DBA需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。通过执行下述语句查看:

    select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

    --查看共享SQL区的重用率,最好在90%以上,否则需要增加共享池的大小。

    select (sum(gets - getmisses - usage - fixED)) / sum(gets) "Row Cache" from v$rowcache;

    --查看数据字典缓冲区的命中率,最好在90%以上,否则需要增加共享池的大小。

    2、 数据缓冲区:存放sql运行结果抓取到的data block;

    SELECT name, value  from v$sysstat  WHERE name IN ('db block gets', 'consistent gets','physical reads');

    -- 查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。命中率应该在90%以上,否则需要增加数据缓冲区的大小。

    3、 日志缓冲区:存放数据库运行生成的日志。

    select name,value from v$sysstat where name in ('redo entries','redo log space requests');

    --查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率:申请失败率=requests/entries,申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加ORACLE数据库的日志缓冲区。

    二.Sql语句的执行步骤:

    了解sql的执行步骤有利于更好的优化它们,每条sql语句执行时都要经历以下几步:

    1. Create cursor ;

    2. Parse, if it is not already in the shared pool.;

    3. Any query in the statement is processed.

    4. Bind Variables

    5. Execute.

    6. If possible, the statement is parallelized.

    7. Rows to be returned are fetched. 其中,Parse—是最有优化潜力的阶段。

    Cursor创建后,oracle将在share pool中寻找是否该sql语句已经存在,若已经存在且已经被parsed,则不需要再执行parse,直接到下一步。若未在share pool中�

    相关文档:

    Oracle RAC学习笔记

    1. Introduction:

    Cluster - 对用户来说多台服务器好像是一台服务器一样。

    Oracle Clusterware - Oracle提供的集群解决方案

    Oracle RAC 多个实例对应一个数据库(文件)

    Oracle Cluster Architecture:  (Hardware)

    Oracle RAC 俩个组件:Voting Disk和OCR (Oracle Cluster Register)

    Voti ......

    select name from v$datafile;

    create dataspace tablespace datafile '/oracle/oradata/orcl/data01.dbf' size 1024m,'/oracle/oradata/orcl/data02.dbf' size 1024m autoextend on next 100m maxsize unlimited;

    alter tablespace dataspace add datafile '/oracle/oradata/orcl/da ......

    SQL字符串函数http://www.cnblogs.com/virusswb/archive/2008/09/10/1288576.html

    select语句中只能使用sql函数对字段进行操作(链接sql server),

    select 字段1 from 表1 where 字段1.IndexOf("云")=1;

    这条语句不对的原因是indexof()函数不是sql函数,改成sql对应的函数就可以了。

    left()是sql函数。

    select 字 ......

    Student(S#,Sname,Sage,Ssex) 学生表

    Course(C#,Cname,T#) 课程表

    SC(S#,C#,score) 成绩表

    Teacher(T#,Tname) 教师表

    问题:

    1、查询“001”课程比“002”课程成绩高的所有学生的学号;

    select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

    fr ......

    本文转自:http://industry.ccidnet.com/art/1106/20070514/1080519_1.html

    本文是SQL Server SQL语句优化系列文章的第一篇。该系列文章描述了在Micosoft’s SQLServer2000关系数据库管理系统中优化SELECT语句的基本技巧,我们讨论的技巧可在Microsoft's SQL Enterprise Manager或 Microsoft SQL Query Anal ......

    更多相关内容
  • oracle数据库的sql优化技巧,方便自己写出高效,简洁,实用的sql语句。
  • oracle SQL优化技巧

    2011-06-04 12:38:36
    oracleSQL语句优化及索引使用技巧
  • ORACLE SQL性能优化技巧

    2018-06-16 11:35:43
    书写高质量的oracle sql,用表连接替换EXISTS,索引的技巧等等
  • Oracle SQL优化技巧

    2009-11-23 16:07:13
    Oracle SQL优化技巧。。。。。。。。。。。
  • 大量优化实战方法:将主要SQL优化点一一剖析,分享大量SQL优化的实际工作经验 50余改写调优案例:覆盖大多数DBA日常工作场景,具有相当大的实用价值 技巧+案例:可以作为DBA的参考手册,也可以作为开发人员编写SQL...
  • Oracle数据库SQL优化详解

    千次阅读 2022-01-07 16:53:15
    Oracle SQL优化详解2.1 Oracle 查询阻塞2.2 Oracle 查询耗时 SQL2.3.Oracle 查看执行计划2.4.Oracle 查看收集统计信息2.5.Oracle 查询优化器 -- 改写查询语句2.6.Oracle 查询优化器 -- 访问路径2.7.Oracle 查询优化...

    1. Oracle SQL优化概述

    我们所执行的sql语句Oracle并不会直接去执行,而是经过数据库优化器优化以后再去执行。但是毕竟Oracle优化器也不是万能的,也有Oracle自身无法实现的优化语句,这就需要我们在书写sql语句的时候需要注意。注意SQL规范有助于避免SQL性能问题,也能避免不必要的异常。

    对于Oracle的sql语句优化也是有序可循的,按照步骤依次分析梳理,找出根源所在,针对性优化才有效果,而不是盲目来一通,以下是简单梳理优化步骤。

    Oracle 查询阻塞,查询耗时 SQL,查看执行计划,查看收集统计信息,查询优化器 – 改写查询语句,查询优化器 – 访问路径,查询优化器 – 表连接方法,索引,视图,减少数据库访问次数,面向对象,分开执行耗时操作,子程序内联,动态 SQL,避免在查询中使用函数,指定子程序 OUT 或 IN OUT 参数为引用传递,尽量少用循环语句,数据类型使用注意事项,字符串处理,短路评估,并发更新大表。

    2. Oracle SQL优化详解

    2.1 Oracle 查询阻塞

    如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。

    -- 如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。
    select 
      blocksession.sid        as block_session_sid,
      blocksession.serial#    as block_session_serial#,
      blocksession.username   as block_session_username,
      blocksession.osuser     as block_session_osuser,
      blocksession.machine    as block_session_machine,
      blocksession.status     as block_session_status,
      blockobject.object_name as blocked_table,
      waitsession.sid         as wait_session_sid,
      waitsession.serial#     as wait_session_serial#,
      waitsession.username    as wait_session_username,
      waitsession.osuser      as wait_session_osuser,
      waitsession.machine     as wait_session_machine,
      waitsession.status      as wait_session_status
    from 
      v$lock          blocklock,
      v$lock          waitlock,
      v$session       blocksession,
      v$session       waitsession,
      v$locked_object lockedobject,
      dba_objects     blockobject
    where 
      blocklock.block    = 1
      and blocklock.sid != waitlock.sid
      and blocklock.id1 = waitlock.id1
      and blocklock.id2 = waitlock.id2
      and blocklock.sid = blocksession.sid
      and waitlock.sid  = waitsession.sid
      and lockedobject.session_id = blocksession.sid
      and lockedobject.object_id  = blockobject.object_id;
      
    -- 如果上面的语句返回了结果,表明发生了阻塞,这个时候你可以把使用 blocksession 的程序停掉。
    -- 如果还是不能解决问题,那只能让 DBA 帮你把 blocksession kill 掉,如何 kill 呢? 试一试下面的语句吧。
    ALTER SYSTEM KILL SESSION '<block_session_sid>,<block_session_serial#>';
    ALTER SYSTEM KILL SESSION '113,55609';
      
    

    如果没有发生阻塞,系统就是很慢,该怎么办呢?在" Oracle 查询耗时 SQL"找答案吧。

    2.2 Oracle 查询耗时 SQL

    当你的系统变慢时,如何查询系统中最耗时的 SQL 呢?试一试下面的 SQL 吧。

    select * from (
    	select * from V$SQLSTATS
    	
    	-- 最耗时的 SQL
    	-- ELAPSED_TIME 指的是总耗时(毫秒),平均耗时 = ELAPSED_TIME/EXECUTIONS
    	-- order by ELAPSED_TIME DESC
    	
    	-- 查询执行次数最多的 SQL
    	-- order by EXECUTIONS DESC
    	
    	-- 读硬盘最多的 SQL
    	-- order by DISK_READS DESC
    	
    	-- 最费 CPU 的 SQL
    	-- order by BUFFER_GETS DESC
    ) where rownum <=50;
    

    一旦查询到耗时 SQL,你需要查看一下它们的执行计划才能知道它们为什么慢,不知道如何查询执行计划?看看这里吧 <Oracle 查看执行计划>。

    2.3.Oracle 查看执行计划

    我们可以通过 EXPLAIN PLAN 语句生成执行计划,该语句把执行计划保存到一个叫做 PLAN_TABLE 的表中,我们可以通过查询这个表来查看执行计划。下面是一个简单例子。
    查看执行计划的两种方式:1.EXPLAIN PLAN。2.autotrace。3.plsql查看执行计划菜单或者F5查看。

    1.EXPLAIN PLAN

    -- 生成执行计划
    EXPLAIN PLAN 
    SET STATEMENT_ID = 'test'
    FOR
    select * from employees where employee_id < 10;
     
    -- 由于 PLAN_TABLE 表非常复杂,Oracle 提供下面的方式察看执行计划
    SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'test', 'ALL'));
     
    -- 如果你想察看更多细节,你也可以直接查询表
    select * from plan_table where statement_id = 'test'
    

    2.autotrace
    如果你使用的是 sqlplus 工具,你还可以通过它提供了 autotrace 功能来查看执行计划,你只需要下面的两步,非常简单,下面是一个简单的例子。

    -- 第一步: 打开 autotrace
    SQL> set autotrace on
     
    -- 第二步: 执行 SQL 语句
    SQL> select * from test;
    

    3.plsql查看执行计划菜单或者F5
    在这里插入图片描述
    4.V$SQL_PLAN

    上面查看执行计划的方式有一个缺陷,它们必须由人触发,随着执行环境,时间,统计信息等的不同,执行计划有可能不同,有没有办法查看已经执行过 SQL 的执行计划呢?答案是肯定的,下面是一个简单的例子。

    -- 第一步: 通过下面的语句找到 SQL_ID 
    select SQL_ID,SQL_TEXT from v$sql 
    where sql_text like '%KAFKA_MSG_QUEUE%';
     
    -- 第二步: 通过下面的方式查看执行计划
    select * from V$SQL_PLAN where SQL_ID='g7b1uz8n2mdvf' 
    order by CHILD_NUMBER, id;
     
    -- 注意,上面的语句可以查询出该语句多次的执行计划,你可以加上时间来过滤
    select * from V$SQL_PLAN where SQL_ID='g7b1uz8n2mdvf' 
    and "TIMESTAMP"=TIMESTAMP '2015-08-20 19:38:06.000'
    order by CHILD_NUMBER, id
     
    -- 上面语句的结果可读性差,试一试下面的语句吧
    select '| Operation                         |Object Name                    |  Rows | Bytes|   Cost |'
    	as "Explain Plan in library cache:" from dual
    union all
    select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
           decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
           rpad(decode(id, 0, '----------------------------',
           substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
           ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,'  ',
           decode(sign(cardinality-1000), -1, cardinality||' ',
           decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
           decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
           trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
           lpad(decode(bytes,null,' ',
           decode(sign(bytes-1024), -1, bytes||' ',
           decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
           decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
           trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
           lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
           decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
           trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
    from v$sql_plan sp
    where sp.SQL_ID='g7b1uz8n2mdvf' 
    and "TIMESTAMP"=TIMESTAMP '2015-08-20 19:38:06.000';
    

    在这里插入图片描述

    2.4.Oracle 查看收集统计信息

    统计信息相当于情报,对 Oracle 至关重要,如果统计信息不准确,Oracle 就会做出错误的判断。那如何查看统计信息呢?试一试下面的 SQL 吧。

    -- 查看表统计信息
    select * from DBA_TABLES where OWNER = 'HR' and TABLE_NAME = 'TEST';
    select * from DBA_TAB_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
     
    -- 查看列统计信息
    select * from DBA_TAB_COL_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
     
    -- 查看索引统计信息
    select * from DBA_IND_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
    

    通常,Oracle 会在每天固定时间段自动维护统计信息。但是对于某些表,这是远远不够的,如:某些表每天都需要清空,然后重新导入。这个时候,我们需要手动收集统计信息。

    -- 方法1: 使用 DBMS_STATS.GATHER_TABLE_STATS 手动收集存储过程
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS');
     
    -- 方法2:删除并锁定统计信息,如果没有统计信息,Oracle 会动态收集统计信息
    BEGIN
    	DBMS_STATS.DELETE_TABLE_STATS('HR','ORDERS');
    	DBMS_STATS.LOCK_TABLE_STATS('HR','ORDERS');
    END;
    

    Oracle 推荐我们使用方法1。 除此之外,DBMS_STATS 包还提供了下面的存储过程用来收集统计信息。

    GATHER_INDEX_STATS       收集索引统计信息
    GATHER_TABLE_STATS       收集指定表,列,索引的统计信息
    GATHER_SCHEMA_STATS      收集指定模式下所有对象的统计信息
    GATHER_SYSTEM_STATS      收集系统统(I/O,CUP)计信息
    GATHER_DICTIONARY_STATS  收集 SYS, SYSTEM 模式下对象的统计信息
    GATHER_DATABASE_STATS    收集所有数据库对象的统计信息
    GATHER_FIXED_OBJECTS_STATS   收集 FIXED 对象的统计信息
    

    DBMS_STATS 包还提供许多子程序让我们可以对统计信息进行操作,如:查询,删除,锁定,恢复等,更多详情你可以参考 PL/SQL Packages and Types 手册。

    2.5.Oracle 查询优化器 – 改写查询语句

    当我们执行一条查询语句的时候,我们只告诉 Oracle 我们想要哪些数据,至于数据在哪里,怎么找,那是查询优化器的事情,优化器需要改写查询语句,决定访问路径(如:全表扫描,快速全索引扫描,索引扫描),决定表联接顺序等。至于选择哪种方式,优化器需要根据数据字典做出判断。

    那优化器如何改写查询语句呢?

    第一种方法叫合并视图,如果你的查询语句中引用了视图,那么优化器会把视图合并到查询中,下面是一个简单的例子,需要注意的是优化器也不是神,如果你的视图包含集合操作符,聚合函数,Group by 等,优化器也傻了,不知道如何合并了。

    -- 视图定义
    CREATE VIEW employees_50_vw AS
    	SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
    	FROM employees
    	WHERE department_id = 50;
     
    -- 查询语句
    SELECT employee_id
    FROM employees_50_vw
    WHERE employee_id > 150;
     
    -- 合并视图后的查询语句
    SELECT employee_id
    FROM employees
    WHERE department_id = 50
    AND employee_id > 150;
    

    第二种方法叫谓词推进(Predicate Pushing),对于那些无法执行合并视图的查询语句,Oracle 会把查询语句中的条件挪到视图中,下面是一个简单的例子。

    -- 视图定义
    CREATE VIEW all_employees_vw AS
    	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees)
    	UNION
    	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers);
     
    -- 查询语句的查询语句
    SELECT last_name
    FROM all_employees_vw
    WHERE department_id = 50;
     
    -- 谓词推进
    SELECT last_name
    FROM ( 
    	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees
    		WHERE department_id=50 -- 注意此处
    	UNION
    	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers
    		WHERE department_id=50 -- 注意此处
    );
    

    第三种方法是将非嵌套子查询转化为表连接。下面是一个简单的例子。需要注意的是,并不是所有的非嵌套子查询都能转化为表连接,对于下面的例子而言,如果 customers.cust_id 不是主键,转化后会产生笛卡尔集。

    -- 非嵌套子查询
    SELECT *
    FROM sales
    WHERE cust_id IN (SELECT cust_id FROM customers);
     
    -- 表连接
    SELECT sales.*
    FROM sales, customers
    WHERE sales.cust_id = customers.cust_id;
    

    第四种方法是使用物化视图改写查询,物化视图是将一个查询的结果集保存在一个表中,如果你的查询语句和某个物化视图兼容,那么 Oracle 就可以直接从物化视图中取得数据。

    2.6.Oracle 查询优化器 – 访问路径

    全表扫描(full table scans)
    不要以为全表扫描就一定慢, 全表扫描时, 由于数据块在磁盘中是连续的,Oracle 可以一次读取多个块来提高查询效率,至于多少个块,是由 DB_FILE_MULTIBLOCK_READ_COUNT 决定的。所以,如果你需要返回一个表的大部分数据,全表扫描要比索引扫描快。除此之外,Oracle 会自动对小表进行全表扫描,那什么是小表呢?就是语句块小于 DB_FILE_MULTIBLOCK_READ_COUNT 定义的值。
    如果你想让 Oracle 使用全表扫描,你也可以通过下面的方式指示 Oracle 使用全表扫描。

    select /* FULL(t)*/ * from test t where col = 'test';
    

    索引扫描(index scans)
    你有没有想过,我们通过索引扫描时,如何通过索引定位到表中的位置呢?答案是通过 Rowid, Rowid 是 Oracle 内部使用的,用来标示行存储地址,所以通过 Rowid 定位行记录是最快的。有一点特别需要注意,Oracle 读写磁盘的最小单位是块,一个块可能包含多行,所以全表扫描还是索引扫描取决于访问块的百分比,而不是行的百分比。假设我们现在需要访问 3 行,这 3 行可能在一个块中,也可能在两个块中,还可能在三个块中,很明显,最理想的情况是在一个块中,Oracle 使用索引聚簇因子(index clustering factor)来衡量这种特性。索引聚簇因子越高,表明 Oracle 通过 Rowid 访问行的代价就越高。此外,Oracle 会根据索引类型的不同,是否排序,采用不同的索引扫描方式。

    唯一索引扫描(Index Unique Scans)
    如果你的查询条件有等价操作符(=),且恰好能用到唯一索引,那么 Oracle 会采用唯一索引扫描,当然你也可以通过下面的方式建议 Oracle 采用哪个索引。

    select /* INDEX(t test_id_pk)*/ * from test t where col = 'test';
    

    索引范围扫描(Index Range Scans)
    如果你的查询条件有范围操作符(>,>=,<,<=,like ‘abc%’),且恰好能用到索引,那么 Oracle 会采用索引范围扫描,当然你也可以通过下面的方式建议 Oracle 采用哪个索引。

    select /* INDEX(t test_id_pk)*/ * from test t where col = 'test';
    

    索引降序范围扫描(Index Range Scans Descending)
    如果你的查询条件有范围操作符且要求用索引列排序,那么 Oracle 会采用索引降序范围扫描,这样做的好处是 Oracle 可以省略排序这个非常耗时的步骤。当然你也可以通过下面的方式建议 Oracle 采用索引降序范围扫描。

    如果你的索引是升序的。
    select /* INDEX_DESC(t test_id_pk)*/ * from test t where col = 'test';
    
    如果你的索引是降序的。
    select /* INDEX_ASC(t test_id_pk)*/ * from test t where col = 'test';
    

    索引跳跃扫描(Index Skip Scans)
    如果你的索引是复合索引,也就是索引包含多个列,如下所示。

    CREATE INDEX cust_idx ON customers (gender, email);
    

    全索引扫描(Full Scans)
    如果你的查询需要排序或分组,且排序或分组用到的列恰好是索引列,那么 Oracle 会采用全索引扫描,由于索引列是有序的,这样 Oracle 可以省略排序这个非常耗时的步骤。

    快速全索引扫描(Fast Full Index Scans)
    如果你要查询的所有列都包含在索引中,Oracle 就不需要访问表了,这样 Oracle 就可能通过并行和一次读取多个块来提高查询索引的效率。你也可以通过下面的方式建议 Oracle 采用快速全索引扫描。

    select /* INDEX_FFS(t test_id_pk)*/ * from test t where col = 'test';
    

    索引连接扫描(Index Joins)
    如果你的表有多个索引,恰好你要查询的所有列包含在这些索引中,Oracle 就不需要访问表了,Oracle 只需要把这些索引连接起来。你也可以通过下面的方式建议 Oracle 采用索引连接。

    select /* INDEX_JOIN(t test_id_idx test_name_idx)*/ * from test t where col = 'test';
    

    位图索引扫描(Bitmap Indexes)
    索引聚簇扫描(Indexed Cluster Access)
    如果你的表包含在了某个索引聚簇中,Oracle 会使用索引聚簇来执行查询。

    select /* INDEX_COMBINE(t test_idx1 test_idx2)*/ * from test t where col = 'test';
    

    Hash 聚簇扫描(Hash Cluster Access)
    如果你的表包含在了某个Hash 聚簇中,Oracle 会使用 Hash 聚簇来执行查询。

    采样扫描(Sample Table Scans)
    如果你的查询语句包含 SAMPLE 子句,那么 Oracle 会使用 采样扫描。

    2.7.Oracle 查询优化器 – 表连接方法

    循环嵌套连接,小表驱动大表,避免笛卡尔积的出现。

    循环嵌套连接(Nested Loop Joins)。

    SELECT e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    对于循环嵌套,你可以把表想象成数组,Oracle 会采用如下的方式执行查询。

    String[] departments = {};
    String[] employees = {};
     
    // 外层循环
    for(String dep: departments) {
    	// 内层循环
    	for(String emp: employees) {
    		
    	}
    }
    

    很明显,如果 employees 很大且没有索引,外层循环每执行一次都需要全表扫描 employees,这是不可接受的。所以循环嵌套表连接方式适合那些内层循环数据量少且有索引的情形。
    当然,你也可以通过下面的方式建议 Oracle 采用循环嵌套连接方式。

    -- USE_NL
    SELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
     
    --USE_NL_WITH_INDEX,指定 e 为内层循环表
    SELECT /*+ USE_NL_WITH_INDEX(e) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
     
    --USE_NL_WITH_INDEX,指定 e 为内层循环表,同时指定索引
    SELECT /*+ USE_NL_WITH_INDEX(e emp_dep_id_idx) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    你还可以通过下面的方式建议 Oracle 不要采用循环嵌套连接方式。

    SELECT /*+ NO_USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    2.8.Oracle 索引

    要正确使用索引,避免索引失效,创建适当的索引。

    不可否认,提高性能最直接有效的方式就是创建索引,正因为如此,好多人把它当做救命的良药,随意创建索引,殊不知维护索引的代价是非常大的。Oracle 官方给了一个大约的数字,维护一个索引所需要的代价大约是操作本身的 3 倍。另外,索引也有好多种类型,不同的索引适应的场景也不同。

    1.索引组织表(Index-Organized Tables)

    创建索引时,如何选择索引列呢?其实就是查询用到的列,包括 SELECT,WHERE,JOIN, ORDER 等用到的列,但是列的顺序是有讲究的,应该把那些重复值最少的列放在最前面。如果你创建一个索引包含一个表的所有列,那么你应该将该表创建为索引组织表(IOT),如果一个表的列比较少,这么做是可以的。对于列多的表,千万别这么干。因为索引组织表的记录存储在索引的叶子节点上,当我们向表中插入数据时,Oracle 为了维护索引需要移动数据,这会大大降低插入速度。下面是一个简单的例子。

    CREATE TABLE test
    (
      id    NUMBER(10),
      name  VARCHAR2(30),
      CONSTRAINT pk_test PRIMARY KEY (id)
    ) ORGANIZATION INDEX;
    

    2.B 树索引(B-Tree Indexes)

    B 树索引是默认的索引类型,特别适合主键,或重复值比较少的列或列的组合,如何判断重复值得多少呢?看看下面的公式吧,下面的值越高越好,主键是 1.0

    SELECT COUNT(DISTINCT COLUMN) / COUNT(*) FROM TEST;
    

    下面是一个简单的例子。

    --普通索引
    CREATE INDEX test_idx_name ON test (name);
     
    --唯一索引
    CREATE UNIQUE INDEX test_idx_id ON test (id);
    

    3.位图索引(Bitmap Indexes)

    位图索引和 B 树索引正好相反,非常适合重复值比较多的列,最好是只有几项,如:国籍,性别,省份等等,而且这些值基本上不会频繁更新。注意,频繁更新的列不适合位图索引,如,订单表有个列表示是否被处理,只有两个值,YES, NO。下面是一个简单的例子。

    --位图索引
    CREATE BITMAP INDEX test_idx_country ON test (country);
    CREATE BITMAP INDEX test_idx_gender ON test (gender);
    CREATE BITMAP INDEX test_idx_province ON test (province);
    

    4.基于函数的索引(Function-based Indexes)
    在索引字段上使用函数会使索引失效,有时候,我们可以通过把它转化为范围扫描来避免这个问题,但是,有时候,我们必须要使用函数,如:忽略大小写查询,这个时候,我们可以在创建基于函数的索引,如下是一个简单的例子。

    CREATE INDEX test_idx ON test(UPPER(NAME));
    

    5.分区索引(Partitioned Indexes)

    分区索引的思想是将大的索引分成多个小索引,这样索引扫描时就可以减少IO。对于普通表,我们可以创建下面两种类型的分区索引。

    -- 范围全局分区索引
    CREATE INDEX test_idx ON test (amount)
    GLOBAL PARTITION BY RANGE (amount)
    (
    	PARTITION p1 VALUES LESS THAN (1000),
    	PARTITION p2 VALUES LESS THAN (2500),
    	PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );
     
    -- 哈希(散列)全局分区索引
    CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
    GLOBAL PARTITION BY HASH (cust_last_name)
    PARTITIONS 4;
    

    对于分区表,除了可以创建上面两种类型的分区索引外,我们还可以给某个表分区或所有分区创建分区索引。

    -- 创建分区表
    CREATE TABLE test
    (
            id number,  
            year number,  
            month number  
    )
    PARTITION BY RANGE (year)    
    (
            PARTITION p1 VALUES LESS THAN (2013),   
            PARTITION p2 VALUES LESS THAN (2014),    
            PARTITION p3 VALUES LESS THAN (2015),    
            PARTITION p4 VALUES LESS THAN (3000)
    );
     
    -- 给所有表分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL;
     
    -- 给 p1分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL (PARTITION p1);
    

    6.反向索引(Reverse Key Indexes)

    反向索引就是将正常的键值头尾调换后再进行存储,比如原值是’abc’,将会以’cba’形式进行存储,为什么要这样做呢?原因是有些值是根据一定的规则生成的,如时间,序列等,当我们插入大量数据时,它们都需要同时插入到索引的某个区域,Oracle 称之为热点区域(hot spot),如果我们使用反向存储,可以有效的避免这个问题,但是有利就有弊,我们不能使用反向索引进行范围扫描,所以使用它要慎重。这里给我们一个非常重要的启示,在设计自动生成的值时,如果有可能,每两次生成的值范围要广。

    7.复合索引

    复合索引就是有多个列的索引,索引列的顺序很关键,如果索引包含 A,B,C 三列,而你的查询条件只包含 B,C 两列,Oracle 就无法使用索引。

    重建索引

    如果让你重建索引该怎么办呢?大多数人都会先删除索引,然后再创建新索引,其实通过下面的方式重建索引更快,因为 Oracle 可以利用现有的索引重建索引。

    ALTER INDEX ... REBUILD
    

    有关索引的视图
    如果让你查询一下某个表都定义了哪些索引该怎么办呢?呵呵,很简单,只需查询一下下面的视图即可。

    all_indexes
    all_ind_columns
    all_ind_expressions
    all_ind_partitions
    all_ind_subpartitions
    all_ind_statistics
    

    那如果让你查询一下某个索引是否被用到,该怎么办呢?首先,你需要让 Oracle 帮你监控一下索引,等过一段时间后,你可以查询下面的视图查看索引是否被用到。

    -- 让 Oracle 监控索引
    ALTER INDEX <indx name> MONITORING USAGE;
     
    -- 查询是否被用到
    select * from v$object_usage
    

    2.9.Oracle 视图

    视图有好多优点,如它可以简化开发。但是有一点特别需要注意,最好不要使用多个视图做联合查询,因为优化器将很难优化这样的查询。

    2.10.Oracle 减少数据库访问次数

    连接数据库是非常耗时的,虽然应用程序会采用连接池技术,但与数据库交互依然很耗时,这就要求我们尽量用一条语句干完所有的事,尤其要避免把 SQL 语句写在循环中,如果你遇到这样的人,应该毫不犹豫给他两个耳光

    2.11 Oracle 面向对象

    我们都知道,传统数据库都是关系型数据库,随着 Java 和 面向对象的流行,Oracle也与时俱进,加入了面向对象的特性,最典型的就是嵌套表,嵌套表使查询变得复杂,同时它的性能也不如传统表好。

    2.12 Oracle 分开执行耗时操作

    首先,我们看一个故事,联合利华引进了一条香皂包装生产线,结果发现这条生产线有个缺陷:常常会有盒子里没装入香皂。总不能把空盒子卖给顾客啊,他们只得请了一个学自动化的博士后设计一个方案来分拣空的香皂盒。博士后拉起了一个十几人的科研攻关小组,综合采用了机械、微电子、自动化、X射线探测等技术,花了几十万,成功解决了问题。每当生产线上有空香皂盒通过,两旁的探测器会检测到,并且驱动一只机械手把空皂盒推走。

    中国南方有个乡镇企业也买了同样的生产线,老板发现这个问题后大为发火,找了个小工来说:你他妈给老子把这个搞定,不然你给老子爬出去。小工很快想出了办法:他在生产线旁边放了台风扇猛吹,空皂盒自然会被吹走。

    还有一个故事,美国宇航局发现圆珠笔在失重环境下无法使用, 结果花了2千万美刀研制出了失重环境下可用的圆珠笔, 而苏联人一直用铅笔。

    这两个故事给我们一个很重要的启示,性能问题都是由于资源竞争导致的,所以,一个简单的想法就是尽量分开执行耗时的操作。这看似一个最简单不过的道理,但是随着软件变得越来越大,到最后可能没有人知道什么时候执行什么操作时合适的。

    2.13 Oracle 子程序内联

    如果子程序 A 调用 B,内联可以把 B 的代码合并到 A 中,从而减少子程序调用,提高性能,下面是一个简单的例子。

    -- 子程序 A
    PROCEDURE A 
    IS
    BEGIN
    	-- 指定下面的子程序 B 内联
    	PRAGMA INLINE (B, 'YES')
    	B(1);
    	
    	-- 注意此处的子程序不会内联
    	B(2);
    END A;  
    
    -- 子程序 B  
    PROCEDURE B (x PLS_INTEGER)  
    IS
    BEGIN
    	DBMS_OUTPUT.PUT_LINE(x);  
    END B;
    

    如果你觉得在每一个子程序调用前加上 PRAGMA INLINE 在麻烦,你可以将编译参数 PLSQL_OPTIMIZE_LEVEL 设置为 3 (默认值是2),这样 Oracle 会把每一个子程序调用都内联。

    2.14 Oracle 动态 SQL

    如果你还不知道什么是动态 SQL,请参考 PL/SQL 动态 SQL
    如果有可能,尽量不要使用动态 SQL,动态 SQL需要运行时编译,影响性能。如果一定要使用动态 SQL,Oracle 推荐我们优先使用 EXECUTE IMMEDIATE,它要比 DBMS_SQL 性能更好。

    2.15 Oracle 避免在查询中使用函数

    一个查询可能要搜索上百万行数据,在查询中使用函数就可能被调用上百万次,这会严重影响性能,下面是一个简单的例子。

    -- 创建表
    CREATE TABLE Department 
    (
    	Department_Id    NUMBER(9,0),
    	Department_Name  VARCHAR2(40)
    );
     
    CREATE TABLE Employee
    (
    	Employee_id    NUMBER(9,0),
    	Employee_Name  VARCHAR2(40),
    	Department_Id  NUMBER(9,0)
    );
     
     
    -- 定义函数
    CREATE OR REPLACE FUNCTION getDepartmentNameById(
    	DepartmentId    number   
    )  
    	RETURN varchar2  
    AS    
    	DepartmentName VARCHAR2(40);    
    BEGIN    
    	select Department_Name into DepartmentName from Department where Department_Id = DepartmentId;    
    	return DepartmentName;      
    END;
     
     
    -- 查询 SQL -- 使用函数
    SELECT getDepartmentNameById(Department_Id) DepartmentName, Employee_Name FROM Employee;
     
     
    -- 查询 SQL -- 使用表连接
    SELECT 
    	d.Department_Name, 
    	e.Employee_Name 
    FROM 
    	Department d,
    	Employee e
    WHERE
    	d.Department_Id = e.Department_Id;
    

    2.16 Oracle 指定子程序 OUT 或 IN OUT 参数为引用传递

    通常,子程序的 OUT 或 IN OUT 参数为值传递,为了防止程序可能发生的异常,Oracle 将它保存到临时变量中,当程序正常退出时,Oracle 把临时变量中值赋给实际参数,异常退出时,保持实际参数不变。当OUT 或 IN OUT 参数返回大批量数据时,由于使用了临时变量导致占用大量内存,这时我们可以在参数的后面加上 NOCOPY 来提示Oracle使用引用传递.

    PROCEDURE test (infor IN OUT NOCOPY Collection) IS
    BEGIN
    	NULL;
    END;
    

    2.17 Oracle 尽量少用循环语句

    下面是一个使用循环语句删除表记录的例子。

    DECLARE
      TYPE NumList IS TABLE OF NUMBER;
      emps NumList := NumList(10, 30, 70);
    BEGIN
      FOR i IN emps.FIRST..emps.LAST LOOP
        DELETE FROM employees WHERE employee_id = emps(i);
      END LOOP;
    END;
    

    在你的工作中,千万别写出上面的语句,否则应该毫不犹豫的给自己两个耳光,应该使用批处理的方式,如下:

    DECLARE
      TYPE NumList IS TABLE OF NUMBER;
      emps NumList := NumList(10, 30, 70);
    BEGIN
      -- FORALL 语句批量执行下面的语句
      FORALL i IN emps.FIRST..emps.LAST
        DELETE FROM employees WHERE employee_id = emps(i);
    END;
    

    或使用 TABLE 表达式,如下:

    CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER;
    
    DECLARE
      emps number_table := number_table(10, 30, 70);
    BEGIN
      DELETE FROM employees WHERE employee_id IN (SELECT COLUMN_VALUE FROM TABLE(emps));
    END;
    

    2.18 Oracle 数据类型使用注意事项

    Oracle 支持 NUMBER,BINARY_FLOAT, BINARY_DOUBLE 等数值数据类型,NUMBER 更精确,BINARY_FLOAT 或 BINARY_DOUBLE 更高效,所以如果有可能,尽量优先使用 BINARY_FLOAT 或 BINARY_DOUBLE

    此外,PL/SQL 还支持好多它们的子类型,有些子类型是由约束的,如不允许 NULL,尽量不要使用约束多的子类型,因为在运行时, Oracle 需要额外的检查,确保它们没有违反约束。

    在运行时,Oracle 会自动进行数据类型转换,如下面的语句把一个字符串赋值给一个数值类型的变量,Oracle 是不会抱错的.

    declare
      x PLS_INTEGER;
    begin
      x := '1';
    end;
    

    应该尽量避免这种情况,如果一个变量是从一个表中获取的,我们应该定义这个变量的类型为 TABLE_NAME.COLUMN_NAME%TYPE

    2.19 Oracle 字符串处理

    如果你需要处理复杂字符串,尽量不要自己编写函数,Oracle 提供了大量的字符串函数供我们使用。点击此处察看 Oracle 支持哪些函数

    如果还是不能满足你的要求,我们还可以使用正则表达式。可以说正则表达式几乎没有处理不了的字符串问题。如果你还不知道什么是正则表达式,点击此处(正则表达式精萃),如果你不知道如何在 Oracle 中使用正则表达式, 点击此处察看如何使用正则表达式

    2.20 Oracle 短路评估

    Oracle 按照从左到右的顺序评估条件表达式,一旦确定结果就停止后面的评估,所以我们应该尽量将轻量级的条件放在最左边。

    IF (x > 10) OR function(parameter) THEN
    

    2.21 Oracle 并发更新大表

    如果你有一个很大的表要更新,千万别想着一次搞定,如果你这么干了,你会发现需要很长时间,最后的结果也不一定成功,为什么呢? 第一,Oracle 需要锁定整个表,这个过程中极有可能发生死锁。第二,Oracle 需要更多的日志文件用于回滚。第三,一旦发生点小问题会导致一个老鼠害一锅汤。那该怎么办呢?答案是分段执行,少量多次并发执行,下面是一个简单的例子。

    DECLARE
      l_sql_stmt VARCHAR2(1000);
      
    BEGIN
      -- 第一步: 创建任务
      DBMS_PARALLEL_EXECUTE.CREATE_TASK ('task_test');
      
      -- 第二步: 根据 ROWID 切块, 每次 100 行
      DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('task_test', 'TRADE', 'EMPLOYEES', true, 100);
      
      -- 第三步: 并发执行下面的 SQL
      l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e SET e.salary = e.salary + 10 WHERE rowid BETWEEN :start_id AND :end_id';
      DBMS_PARALLEL_EXECUTE.RUN_TASK('task_test', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10);
      
      -- 第四步: 删除任务
      DBMS_PARALLEL_EXECUTE.DROP_TASK('task_test');
    END;
    

    另外多提一句,关于性能优化涉及面非常广,不仅仅是数据库SQL上的优化,设计架构高性能架构的类似秒杀系统需要多方面考虑,从前端静态页面,DNS加速,带宽,服务器性能,中间件性能,数据库性能,SQL只是其中一环。最后,感谢shangboerds老师,转载于shangboerds文章后验证补充。
    SQL优化规范
    sql优化规范
    Oracle SQL 优化精萃
    MySQL执行计划Explain详解
    MySQL数据库索引及失效场景

    展开全文
  • 下面列举一些工作中常常会碰到的OracleSQL语句优化方法: 1、SQL语句尽量用大写的; 因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。 2、选择最有效率的表名顺序(只在基于规则的优化器中有效...

    下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法:

    1、SQL语句尽量用大写的;

     因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。

    2、选择最有效率的表名顺序(只在基于规则的优化器中有效):

    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

    3、WHERE子句中的连接顺序: 

     ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他

    WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

    4、使用表的别名: 

     当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,

    就可以减少解析的时间并减少那些由列歧义引起的语法错误。

    5、SELECT子句中避免使用 ‘ * ‘:

    ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

    6、使用DECODE函数来减少处理时间:

    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

    7、整合简单无关联的数据库访问

    如果有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系),以减少多于的数据库IO开销。

    虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以还是要权衡之间的利弊。

    8、使用where而非having

    where语句是在group by 语句之前筛选出记录,而having是在各种记录都筛选之后再进行过滤,也就是说having子句是在数据库中提取数据之后再筛选。因此尽量在筛选之前将数据使用where子句进行过滤,因此执行的顺序应该如下

    1使用where子句查找符合条件的数据

    2使用group by子句对数据进行分组

    3在group by分组的基础上运行聚合函数计算每一组的值

    9、用(UNION)UNION ALL替换OR (适用于索引列) 
    通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 
    注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR

    而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 
    如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面. 

    代码如下:
    高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL 
    SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 
    低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 
    “MELBOURNE” 

    10、用UNION-ALL 替换UNION ( 如果有可能的话): 
    当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最
    终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需
    要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用
    UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块
    内存的优化也是相当重要的. 

    11、Order By语句加在索引列,最好是主键PK上。 

    代码如下:
    SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE(低效) 
    SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE (高效)

    12、避免使用耗费资源的操作: 
    带有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. 
    DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT
    的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, 
    INTERSECT也是可以考虑的, 毕竟它们的可读性很强 

    13、通常来说,如果语句能够避免子查询的 使用,就尽量不用子查询。因为子查询的开销是相当昂贵的

    14、用EXISTS替代IN

    在许多基于基础表的查询中,为了满足一个条件 ,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

    低效:

    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’)

    15.用NOT EXISTS替代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’)
    高效:

    SELECT ….
    FROM EMP E
    WHERE NOT EXISTS (SELECT ‘X’
    FROM DEPT D
    WHERE D.DEPT_NO = E.DEPT_NO
    AND DEPT_CAT = ‘A’)

    16. 用表连接替换EXISTS

    通常来说 ,采用表连接的方式比EXISTS更有效率 。

    低效:

    SELECT ENAME
    FROM EMP E
    WHERE EXISTS (SELECT ‘X’
    FROM DEPT
    WHERE DEPT_NO = E.DEPT_NO
    AND DEPT_CAT = ‘A’)
    高效:

    SELECT ENAME
    FROM DEPT D,EMP E
    WHERE E.DEPT_NO = D.DEPT_NO
    AND DEPT_CAT = ‘A’

    17.用EXISTS替换DISTINCT 

    当提交一个包含对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换。

    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;

    18.用索引提高效率:

    (1)特点

    优点: 提高效率 主键的唯一性验证

    代价: 需要空间存储 定期维护

    重构索引: 

    ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>;

    (2)Oracle对索引有两种访问模式

    索引唯一扫描 (Index Unique Scan)
    索引范围扫描 (index range scan)

    (3)基础表的选择

    基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。 根据优化器的不同,SQL语句中基础表的选择是不一样的。
    如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径。

    (oracle 10g 及以后)
    如果你用RBO (RULE BASED OPTIMIZER), 并且所有的连接条件都有索引对应,在这种情况下,基础表就是FROM 子句中列在最后的那个表。

    (oracle 10g以前)

    (4)多个平等的索引

    当SQL语句的执行路径可以使用分布在多个表上的多个索引时,ORACLE会同时使用多个索引并在运行时对它们的记录进行合并,检索出仅对全部索引有效的记录。
    在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较。这种子句在优化器中的等级是非常低的。
    如果不同表中两个相同等级的索引将被引用,FROM子句中表的顺序将决定哪个会被率先使用。 FROM子句中最后的表的索引将有最高的优先级。
    如果相同表中两个相同等级的索引将被引用,WHERE子句中最先被引用的索引将有最高的优先级。

     优先级要看CBO的要结婚表的数据来看。

    (5)等式比较优先于范围比较

    DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引。

    SELECT ENAME
    FROM EMP
    WHERE DEPTNO > 20
    AND EMP_CAT = ‘A’;
    这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下:

    TABLE ACCESS BY ROWID ON EMP

    INDEX RANGE SCAN ON CAT_IDX

    即使是唯一性索引,如果做范围比较,其优先级也低于非唯一性索引的等式比较。

    (6)相同的索引列不能互相比较,这将会启用全表扫描。

    不使用索引:

    SELECT ACCOUNT_NAME, AMOUNT
    FROM TRANSACTION
    WHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME)
    使用索引:

    SELECT ACCOUNT_NAME,AMOUNT
    from transaction
    WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, ’%’)

    19.在java代码中尽量少用连接符“+”连接字符串!

    20.避免在索引列上使用NOT、<>、!= 通常, 

    我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

    对索引列的使用最好就是 where 索引列 = 条件
    !=将不使用索引,记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.。

    21。避免在索引列上使用计算.

    WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
    举例:

    低效:
    SELECT … FROM DEPT WHERE SAL * 12 > 25000;

    高效:
    SELECT … FROM DEPT WHERE SAL > 25000/12;

    22. 用>=替代>

    高效:
    SELECT * FROM EMP WHERE DEPTNO >=4


    低效:
    SELECT * FROM EMP WHERE DEPTNO >3
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

    23识别'低效执行'的SQL语句:

    虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法:

    SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
    ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
    ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
    SQL_TEXT
    FROM V$SQLAREA
    WHERE EXECUTIONS>0
    AND BUFFER_GETS > 0
    AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

    --and PARSING_SCHEMA_NAME = 'YYII_S6MISM'
    ORDER BY 4 DESC;

    24.避免在索引列上使用IS NULL和IS NOT NULL

    避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

    低效: (索引失效)
    SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

    高效: (索引有效)
    SELECT  * FROM DEPARTMENT WHERE DEPT_CODE >=0;

    25.避免改变索引列的类型.:

    当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
    假设 EMPNO是一个数值类型的索引列.

    SELECT … FROM EMP WHERE EMPNO = ‘123'
    实际上,经过ORACLE类型转换, 语句转化为:

    SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
    幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
    现在,假设EMP_TYPE是一个字符类型的索引列.

    SELECT … FROM EMP WHERE EMP_TYPE = 123
    这个语句被ORACLE转换为:

    select … from EMP WHERETO_NUMBER(EMP_TYPE)=123
    因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

    26.如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. 

    b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

    27.用Explain Plan分析SQL语句

    EXPLAIN PLAN 是一个很好的分析SQL语句的工具, 它甚至可以在不执行SQL的情况下分析语句. 通过分析, 我们就可以知道ORACLE是怎么样连接表, 使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。

    28.对UNION的优化

    由于UNION会对查询结果进行排序,而且过滤重复记录,因此其执行效率没有UNION ALL高。 UNION操作会使用到SORT_AREA_SIZE内存块,因此对这块内存的优化也非常重要。

    可以使用下面的SQL来查询排序的消耗量 :

    select *
    from V$SYSSTAT
    where name like 'sort%'

    29.连接多个扫描

    如果对一个列和一组有限的值进行比较,优化器可能执行多次扫描并对结果进行合并连接。

    举例:

    SELECT *
    FROM LODGING
    WHERE MANAGER IN (‘BILL GATES’, ’KEN MULLER’)
    优化器可能将它转换成以下形式:

    SELECT *
    FROM LODGING
    WHERE MANAGER = ‘BILL GATES’
    OR MANAGER = ’KEN MULLER’

    30.使用日期

    当使用日期时,需要注意如果有超过5位小数加到日期上,这个日期会进到下一天!
    select TO_DATE('20010101','yyyymmdd')+0.99999
    from DUAL;
    Returns:
    02-1月 -01

    select TO_DATE('20010101','yyyymmdd')+0.999995
    from DUAL;
    RETURNS:
    02-1月 -01

    31.使用显示游标(CURSORS)

    使用隐式的游标,将会执行两次操作。第一次检索记录,第二次检查TOO MANY ROWS 这个exception。而显式游标不执行第二次操作。

    32.优化EXPORT和IMPORT

    使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度.

    ORACLE将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否则列值会被截断.

    译者按:

    可以肯定的是, 增加BUFFER会大大提高EXPORT , IMPORT的效率. (曾经碰到过一个CASE, 增加BUFFER后,IMPORT/EXPORT快了10倍!)

    作者可能犯了一个错误: “这个值至少要和表中最大的列相当,否则列值会被截断. “

    其中最大的列也许是指最大的记录大小.

    33.分离表和索引

    总是将你的表和索引建立在不同的表空间内(TABLESPACES)。
    决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。
    确保数据表空间和索引表空间置于不同的硬盘上。

    展开全文
  • Oracle SQL性能优化技巧大总结
  • 从程序员的角度分析并优化sql语句,对提高应用程序的效率颇有益处。
  • 教主Oracle SQL高级查询优化改写完美培训视频 2.0版,这个我参与培训的,包含视频、SQL文件、教学文档内容完整,分享给大家学习,共同努力进阶转型开发DBA,人称教主,做sql改写十多年了,sql改写功底很强!
  • 在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_...
  • oracle sql优化实战案例

    2016-02-20 11:16:57
    主要讲述oracle sql 的开发以及优化,对低效率的sql优化方法和诊断技巧
  • ORACLE_SQL性能优化技巧

    2019-01-27 12:49:28
    该文档列举了五十条基于ORACLESQL语句优化技巧,某些SQL也适用于其它关系型数据库。欢迎选择该文档
  • Oracle查询优化改写技巧与案例,基于OracleSQL优化,实例优化
  • 避免在索引列上使用IS NULL和IS NOT NULL,用UNION-ALL 替换UNION 等
  • UPDATE 1、先备份数据(安全、提高性能)。2、分批更新,小批量提交,防止锁表。3、如果被更新的自动有索引,更新的数据量很大,先取消索引,再重新创建。4、全表数据更新,如果表非常大,建议以创建新表的形式替代...
  • Oracle查询优化改写技巧与案例》不讲具体语法,只是以案例的形式介绍各种查询语句的用法。第1~4章是基础部分,讲述了常用的各种基础语句,以及常见的错误和正确语句的写法。这部分的内容应熟练掌握,因为日常查询...
  • 2、降低CPU计算 除了 IO 瓶颈之外,SQL优化中需要考虑的就是CPU运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到...
  • Oracle SQL 性能优化.ppt

    2020-04-01 16:49:33
    oracle性能优化 1、SQL语句的优化规则; 2、SQL语句优化的方法; 3、SQL语句优化技巧; 提升数据库性能
  •  但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:  ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功...
  • SQL优化技巧指南

    2020-09-10 12:55:20
    主要介绍了SQL优化的方方面面的技巧,以及应注意的地方,需要的朋友可以参考下
  • SQL性能优化技巧分享
  • OracleSQL性能优化技巧

    2011-05-09 15:33:11
    OracleSQL性能优化技巧 (1)调整数据结构的设计。 (2)调整应用程序结构设计。 (3)调整数据库SQL语句。 (4)调整服务器内存分配。 (5)调整硬盘I/O,这一步是在信息系统开发之前完成的。 (6)调整操作系统...
  • Oracle查询优化改写技巧与案例》不讲具体语法,只是以案例的形式介绍各种查询语句的用法。第1~4章是基础部分,讲述了常用的各种基础语句,以及常见的错误和正确语句的写法。这部分的内容应熟练掌握,因为日常查询...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 18,937
精华内容 7,574
关键字:

oraclesql优化技巧

友情链接: sortlistctrldemo.rar