精华内容
下载资源
问答
  • oracle 查询优化

    千次阅读 2019-01-10 14:45:21
    oracle查询优化(一) 1、IN 操作符 用 IN 写出来的 SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用 IN 的 SQL 性能总是比较低的,从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的...
    oracle查询优化(一)
    1、IN 操作符
    用 IN 写出来的 SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。
    
    但是用 IN 的 SQL 性能总是比较低的,从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的 SQL有以下区别:
    
    ORACLE 试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN 的 SQL 至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的 SQL 就不能转换了。
    
    2、NOT IN 操作符
    此操作是强列推荐不使用的,因为它不能应用表的索引。
    
    推荐方案:用NOT EXISTS 或(外连接+ 判断为空)方案代替
    
    3、<> 操作符(不等于)
    不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。
    
    推荐方案:用其它相同功能的操作运算代替,如
    
    a<>0 改为 a>0 or a<0
    
    a<>'' 改为 a>''
    
    4、> 及 < 操作符(大于或小于操作符)
    大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段 A, 30 万记录的A=0 , 30 万记录的 A=1 , 39 万记录的 A=2 ,1万记录的 A=3 。那么执行A>2 与 A>=3的效果就有很大的区别了,因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较,而 A>=3 时 ORACLE 则直接找到 =3 的记录索引。
    
    5、IS NULL 或 IS NOT NULL 操作(判断字段是否为空)
    判断字段是否为空一般是不会应用索引的,因为B 树索引是不索引空值的。
    
    推荐方案:
    
    用其它相同功能的操作运算代替,如
    
    a is not null 改为 a>0 或 a>'' 等。
    
    不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。
    
    建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)
    
    6、UNION 操作符
    UNION 在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION 。如:
    
    select * from gc_dfys union select * fromls_jg_dfys
    
    这个 SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
    
    推荐方案:采用UNION ALL 操作符替代UNION ,因为 UNION ALL操作只是简单的将两个结果合并后就返回。
    
    7、WHERE 后面的条件顺序影响
    WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
    
    Select * from zl_yhjbqk where dy_dj =‘1KV以下‘ and xh_bz=1
    
    Select * from zl_yhjbqk where xh_bz=1 and dy_dj =‘1KV以下‘
    
    以上两个SQL 中 dy_dj (电压等级)及 xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL 的 dy_dj = ‘1KV以下‘ 条件在记录集内比率为 99% ,而 xh_bz=1 的比率只为 0.5% ,在进行第一条 SQL 的时候 99% 条记录都进行 dy_dj及xh_bz 的比较,而在进行第二条 SQL 的时候 0.5% 条记录都进行 dy_dj及xh_bz 的比较,以此可以得出第二条 SQL 的 CPU 占用率明显比第一条低。
    
    8、目标方面的提示:
    COST (按成本优化)
    
    RULE (按规则优化)
    
    CHOOSE (缺省)(ORACLE自动选择成本或规则进行优化)
    
    ALL_ROWS (所有的行尽快返回)
    
    FIRST_ROWS (第一行数据尽快返回)
    
    9、执行方法的提示:
    USE_NL (使用 NESTED LOOPS 方式联合)
    
    USE_MERGE (使用 MERGE JOIN 方式联合)
    
    USE_HASH (使用 HASH JOIN 方式联合)
    
    10、索引提示:
    INDEX ( TABLE INDEX)(使用提示的表索引进行查询)
    
    11、其它高级提示(如并行处理等等)
    ORACLE 的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑 ORACLE也可能不会按提示进行。根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE 在 SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。
    
    12、IN和EXISTS
    有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。
    
    第一种格式是使用IN操作符:
    
    ... where column in(select * from ... where...);
    
    第二种格式是使用EXIST操作符:
    
    ... where exists (select 'X' from ...where...);
    
    我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
    
    第二种格式中,子查询以'select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。
    
    通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。
    
    同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。
    
    任何在where子句中使用is null或is notnull的语句优化器是不允许使用索引的。
    
    17、order by语句
    ORDER BY语句决定了Oracle如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。
    仔细检查orderby语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在orderby子句中使用表达式。
    18、NOT
    我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
    ... where not (status ='VALID')
    如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:
    ... where status <>'INVALID';
    再看下面这个例子:
    select * from employee where salary<>3000;
    对这个查询,可以改写为不使用NOT:
    select * from employee where salary<3000 orsalary>3000;
    虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。
    全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描。
    19、使用DECODE函数来减少处理时间
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如:
    SELECT COUNT(*),SUM(SAL)
    FROM EMP
    WHERE DEPT_NO = 0020
    AND ENAME LIKE ‘SMITH%’;
      你可以用DECODE函数高效地得到相同结果.
    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子句中.
    20、用Where子句替换HAVING子句
    避免使用HAVING子句, HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:
      低效:
    SELECT REGION,AVG(LOG_SIZE)
    FROM LOCATION
    GROUP BY REGION
    HAVING REGION REGION != ‘SYDNEY’
    AND REGION != ‘PERTH’
      高效:
    SELECT REGION,AVG(LOG_SIZE)
    FROM LOCATION
    WHERE REGION REGION != ‘SYDNEY’
    AND REGION != ‘PERTH’
    GROUP BY REGION
    (译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT()等等. 除此而外,一般的条件应该写在WHERE子句中)
    21、减少对表的查询
    在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:
      低效:
    SELECT TAB_NAME
    FROM TABLES
    WHERE TAB_NAME = ( SELECT TAB_NAME
    FROM TAB_COLUMNS
    WHERE VERSION = 604)
    AND DB_VER= ( SELECT DB_VER
    FROM TAB_COLUMNS
    WHERE VERSION = 604)
      高效:
    SELECT TAB_NAME
    FROM TABLES
    WHERE (TAB_NAME,DB_VER)
    = ( SELECT TAB_NAME,DB_VER)
    FROM TAB_COLUMNS
    WHERE VERSION = 604)
    Update 多个Column 例子:
      低效:
    UPDATE EMP
    SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
    SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
    WHERE EMP_DEPT = 0020;
      高效:
    UPDATE EMP
    SET (EMP_CAT, SAL_RANGE)
    = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
    FROM EMP_CATEGORIES)
    WHERE EMP_DEPT = 0020;
    22、通过内部函数提高SQL效率.
    SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
    FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
    WHERE H.EMPNO = E.EMPNO
    AND H.HIST_TYPE = T.HIST_TYPE
    GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
    通过调用下面的函数可以提高效率.
    FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
    AS
    TDESC VARCHAR2(30);
    CURSOR C1 IS
    SELECT TYPE_DESC
    FROM HISTORY_TYPE
    WHERE HIST_TYPE = TYP;
    BEGIN
    OPEN C1;
    FETCH C1 INTO TDESC;
    CLOSE C1;
    RETURN (NVL(TDESC,’?’));
    END;
    FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
    AS
    ENAME VARCHAR2(30);
    CURSOR C1 IS
    SELECT ENAME
    FROM EMP
    WHERE EMPNO=EMP;
    BEGIN
    OPEN C1;
    FETCH C1 INTO ENAME;
    CLOSE C1;
    RETURN (NVL(ENAME,’?’));
    END;
    SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
    H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
    FROM EMP_HISTORY H
    GROUP BY H.EMPNO , H.HIST_TYPE;

     

    展开全文
  • oracle学习分组查询子查询

    千次阅读 2016-09-01 22:53:58
    oracle学习总结 group by 与order by运用 having运用与where区别 ----------------------------------------------------...group by用于对查询结果分组统计,  having子句用于限制分组显示结果。  问题:如何
    oracle学习总结
    
    group by 与order by运用
    having运用与where区别
    -------------------------------------------------------------------------
    group by 和 having子句 
    group by用于对查询的结果分组统计, 
    having子句用于限制分组显示结果。 
    问题:如何显示每个部门的平均工资和最高工资? 
    SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; -----先分组,再聚合。
    (注意:这里暗藏了一点,如果你要分组查询的话,分组的字段deptno一定要出现在查询的列表里面,否则会报错。因为分组的字段都不出现的话,就没办法分组了) 

    问题:显示每个部门的每种岗位的平均工资和最低工资? 
    SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 
    问题:显示平均工资低于2000的部门号和它的平均工资? 
    SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; --很容易错

    对数据分组的总结 
    1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中) 
    2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by 
    3 在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错。 

    SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 
    这里deptno就一定要出现在group by 中 
    多表查询 
    说明
    多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中,查询单个表可能不能满足你的需求,(如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表) 
    问题:显示部门号为10的部门名、员工名和工资? 
    SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 
    问题:显示各个员工的姓名,工资及工资的级别? 
    先看salgrade的表结构和记录 
    SQL>select * from salgrade; 
     GRADE              LOSAL             HISAL 
    -------------  -------------     ------------ 
        1                      700                1200 
        2                      1201              1400 
        3                      1401              2000 
        4                      2001              3000 
        5                      3001              9999 
    SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 

    扩展要求: 
    问题:显示雇员名,雇员工资及所在部门的名字,并按部门排序? 
    SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; 
    (注意:如果用group by,一定要把e.deptno放到查询列里面) 
    自连接
    自连接是指在同一张表的连接查询 
    问题:显示某个员工的上级领导的姓名? 
    比如显示员工‘FORD’的上级 
    SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';      
    子查询 
    --------------------------------------------------------------------------------------------
    什么是子查询
    子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询。 
    单行子查询 
    单行子查询是指只返回一行数据的子查询语句 
    请思考:显示与SMITH同部门的所有员工? 
    思路:
    1 查询出SMITH的部门号 
    select deptno from emp WHERE ename = 'SMITH'; 
    2 显示 
    SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 
    数据库在执行sql 是从左到右扫描的, 如果有括号的话,括号里面的先被优先执行。 

    多行子查询
    多行子查询指返回多行数据的子查询 

    请思考:如何查询部门编号为10的工作相同的雇员的名字、岗位、工资、部门号 
    SELECT DISTINCT job FROM emp WHERE deptno = 10; 
    SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); 
    (注意:不能用job=..,因为等号=是一对一的) 
     在多行子查询中使用all操作符
    问题:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号? 
    SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 
    扩展要求: 
    大家想想还有没有别的查询方法。 
    SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
    执行效率上, 函数高得多 

    在多行子查询中使用any操作符
    问题:如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号? 
    SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 
    扩展要求: 
    大家想想还有没有别的查询方法。 
    SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

    多列子查询
    单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询是指查询返回多个列数据的子查询语句。 
    请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。 
    SELECT deptno, job FROM emp WHERE ename = 'SMITH'; 
    SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 
    在from子句中使用子查询
    请思考:如何显示高于自己部门平均工资的员工的信息 
    思路: 
    1. 查出各个部门的平均工资和部门号 
    SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 
    2. 把上面的查询结果看做是一张子表 
    SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
    如何衡量一个程序员的水平? 
    网络处理能力, 数据库, 程序代码的优化程序的效率要很高 
    小总结: 
    在这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。 
    注意:别名不能用as,如:SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
    在ds前不能加as,否则会报错? (给表取别名的时候,不能加as;但是给列取别名,是可以加as的) 
    2.外键
    插入表的时候,如果插入的是外键,那么这个外键必须先定义(也就是先插入外键所在表的主键值),否则报什么父对象异常;
    删除的时候相反,如果要删除一个表的主键,然后才可以删除该主键对应的外键。
    create table t_test1(id number primary key,subID number);
    create table t_test2(id number primary key);
    alter table t_test1
      add constraint FK_test1_subID foreign key (subID)
      references T_test2 (id);

    7.数据库,表的导出(plsql developer)
    ok

    10.rownum 
    要使用必须用排序的子查询,而且必须使用rownum<n才行,如果使用rownum>n查出是无序的
       (n,m)
    select   t2.*  from(t1.* ,rownum rn from (

    ) t1 where rownum<m) t2 ) where rn >n;

    11.序列的运用(深入讲下序列运用)
    insert into t_car(carno,carType,DISPLACEMENT,PRICE,PRODUCTDATE) values(seq_student.nextval,'mini',1.2,100000,sysdate);
    select seq_student.currval from dual; --当启动系统时候只有运行一下next才可以调用

    解锁scott用户,必须system用户
    alter user scott account unlock;
    设置密码
    password scott/XXXX;

    展开全文
  • Oracle分页查询优化

    2019-03-05 11:35:53
    优化在查询中有子查询优化效果明显 耗时(s) 不分页 未处理前分页 处理后分页 提升 未排序 0.5 13.9 0.25 98% 排序 0.5 14.25 0.296 97.9% 不分页SQL -- 耗时0.5s SELECT ROWNUM ROWINDEX, ...

    分页优化

    在程序中涉及分页SQL的一些性能问题,这里总结一下:

    1. 优化在查询列中有子查询时优化效果明显
    耗时(s)不分页未处理前分页处理后分页提升
    未排序0.513.90.2598%
    排序0.514.250.29697.9%
    • 不分页SQL
    -- 耗时0.5s
    SELECT ROWNUM ROWINDEX,              
           (select tbzt
              from t_dw_sbbg_bgsblb
             where bgsbid = t.obj_id
               and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
      from T_SB_ZWYC_JJ T
     WHERE 1 = 1
       AND exists (select bgsbid
              from t_dw_sbbg_bgsblb
             where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
               and bgsbid = t.obj_id)
    
    • 未处理前的分页
    -- 耗时13.9s
    select *
      from (SELECT ROWNUM ROWINDEX,              
                   (select tbzt
                      from t_dw_sbbg_bgsblb
                     where bgsbid = t.obj_id
                       and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
              from T_SB_ZWYC_JJ T
             WHERE 1 = 1
               AND exists
             (select bgsbid
                      from t_dw_sbbg_bgsblb
                     where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                       and bgsbid = t.obj_id))
     WHERE ROWINDEX > 20
       AND ROWINDEX <= 40
    
    
    • 处理后的分页
    --耗时 0.25
    select *
      from (SELECT ROWNUM ROWINDEX,               
                   (select tbzt
                      from t_dw_sbbg_bgsblb
                     where bgsbid = t.obj_id
                       and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
              from T_SB_ZWYC_JJ T
             WHERE 1 = 1
               AND exists
             (select bgsbid
                      from t_dw_sbbg_bgsblb
                     where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                       and bgsbid = t.obj_id) and ROWNUM<= 40 )
     WHERE ROWINDEX > 20
    
    

    排序

    • 不分页
    -- 0.5
    SELECT ROWNUM ROWINDEX,              
           (select tbzt
              from t_dw_sbbg_bgsblb
             where bgsbid = t.obj_id
               and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
      from T_SB_ZWYC_JJ T
     WHERE 1 = 1
       AND exists (select bgsbid
              from t_dw_sbbg_bgsblb
             where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
               and bgsbid = t.obj_id)
               order by obj_id
    
    • 未处理
    --14.25s
    select *
      from (select ROWNUM ROWINDEX, tbzt from (SELECT               
                   (select tbzt
                      from t_dw_sbbg_bgsblb
                     where bgsbid = t.obj_id
                       and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
              from T_SB_ZWYC_JJ T
             WHERE 1 = 1
               AND exists
             (select bgsbid
                      from t_dw_sbbg_bgsblb
                     where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                       and bgsbid = t.obj_id) order by OBJ_ID))
     WHERE ROWINDEX > 20
       AND ROWINDEX <= 40
    
    • 处理后
    --0.296s
    select *
      from ( select ROWNUM ROWINDEX, tbzt from (SELECT               
                   (select tbzt
                      from t_dw_sbbg_bgsblb
                     where bgsbid = t.obj_id
                       and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
              from T_SB_ZWYC_JJ T
             WHERE 1 = 1
               AND exists
             (select bgsbid
                      from t_dw_sbbg_bgsblb
                     where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                       and bgsbid = t.obj_id)) where 1=1 and ROWNUM<= 40 )
     WHERE ROWINDEX > 20
    

    如果对你有帮助请点赞哦

    展开全文
  • Oracle 查询优化

    千次阅读 2015-05-21 21:07:57
    1、SELECT 子句避免使用‘*’ :ORACLE在解析过程中通过查询字典将‘*’一次转换成所有的列名。 2、WHERE子句中的链接顺序 :ORACLE采用自右向左顺序解析,过滤大数据量记录的条件卸载WHERE子句末尾,CPU占用率会...

    1、SELECT 子句避免使用‘*’ :ORACLE在解析过程中通过查询字典将‘*’一次转换成所有的列名。

    2、WHERE子句中的链接顺序 :ORACLE采用自右向左顺序解析,过滤大数据量记录的条件卸载WHERE子句末尾,CPU占用率会降低很多。

    3、选择最有效的表名顺序 :把记录少的表放在FROM子句的最后面一个表(这是符合ORACLE机制的,因为如果对表进行了统计分析,
    ORACLE会自动先进小表的链接,再进行大表的链接)

    4、使用表的别名 :当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

    5、减少访问数据库的次数 : 当执行每条SQL语句时ORACLE在内部解析SQL语句,估算索引的利用率, 绑定变量 , 读数据块等等。

    这就涉及到SQL书写带来的影响,同一条SQL得到的结果集相同,但是写法不同就会增加对数据库的访问次数,产生多次硬解析,提高了成本,降低了性能(关于软解析与硬解析的区别以后会详细分析,这里就不具体说明了)

    6、用TRUNCATE替代DELETE :

    DELETE删除数据时,回滚段里存放了可恢复的信息,若没有执行COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况

    TRUNCATE操作之后数据无法恢复,因为没有在回滚段交互,执行时间很短(TRUNCATE是DDL不是DML)

    7、尽可能多的使用COMMIT,以释放资源提高性能(这些资源包括:回滚段上用于恢复数据的信息;背程序语句获得的锁;redo log buffer中的空间;ORACLE内部CUP的消耗)

    8、用WHERE 子句替换HAVING子句 :where是用于过滤行的,而having是用来过滤组的,因为行被分组后,having 才能过滤组,所以尽量用户 WHERE 过滤

    9、用表连接替换EXISTS     用EXISTS替代IN . NOT EXISTS替代NOT IN :

    Exists 只检查行的存在,而 IN检查实际值

    对于用 IN 的 SQL 语句 ORACLE 总是试图将其转换成多个表的连接,如果转换不成功则先执行 IN里面的子查询,再查询外层的表记录
    如果转换成功就转换成多个表的连接。因此 用 IN 的 SQL 语句总是多了 一个转换的过程。

    NOT IN是不走索引的

    10、用EXISTS替换DISTINCT :EXISTS 使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后立刻返回结果.,DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行。  例:

    1.低效:SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO ;
    2.高效: SELECT DEPT_NO,DEPT_NAME  FROM DEPT D  WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

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

    12、在进行多表关联时,多用 Where 语句把单个表的结果集最小化,多用聚合函数汇总结果集后再与其它表做关联,以使结果集数据量最小化。

    13、在两张表进行关联时,应考虑可否使用右连接。以提高查询速度。

    14、LIKE操作符 :遇到 需要用到 LIKE 过滤的SQL语句,完全可以用 instr 代替。处理速度将显著提高。

    15、where子句避免在索引列上使用计算或函数:

    substr(no,1,4)   -->  no like '5400%'

    16、尽量使用package :第一次调用时将整个包load进内存,提高性能。

    尽量使用cached sequences  生成primary key : 提高主键生成速度和使用的性能。

    17、 用索引提高效率 :通常,通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时,,ORACLE优化器将使用索引,
    除了那些LONG或LONG RAW数据类型,你可以索引几乎所有的列,在大型表中使用索引特别有效。
         虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。索引需要空间来 存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改,这意味着每条记录的INSERT 、DELETE 、UPDATE将为此多付出4 、5 次的磁盘I/O ,因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

    定期的重构索引是有必要的 :ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>


    18、索引优化规则 :

    • like件中不要以通配符(WILDCARD)开始,否则索引将不被采用.
          例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘%HANMAN';
    • 避免在索引列上使用计算或改变索引列的类型或使用‘!=’及 <>
      例: SELECT … FROM DEPT WHERE SAL * 12 > 25000;
            SELECT … FROM EMP WHERE EMP_TYPE=to_char(123);
            select …. Where ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';
            select …where empno!=8888 
    • 避免在索引列上使用NOT .
    • 用>=替代> .
        高效: SELECT * FROM EMP WHERE DEPTNO >=4
        低效: SELECT * FROM EMP WHERE DEPTNO >3 
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
    • 用UNION替换OR (适用于索引列)
    通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.在下面的例子中, LOC_ID 和REGION上都建有索引. 
    高效: 
    SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 
    UNION 
    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” 
    如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面
    注意:WHERE KEY1 = 10 (返回最少记录)
    OR KEY2 = 20 (返回最多记录) 
    ORACLE 内部将以上转换为 
     WHERE KEY1 = 10 AND((NOT KEY1 = 10) AND KEY2 = 20)
    6.避免在索引列上使用IS NULL和IS NOT NULL
           避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引 .对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.
    (建议:可以给null值的字段设置一个默认值))

    7.如果索引是建立在多个列上,索引时段需要放在where 条件的第一个条件(Oracle8i之前),Oracle8i之后允许跳跃式索引.
     
    8. 可能的话)用UNION-ALL 替换UNION.
    UNION-ALL就是做简单的合并,不会进行排序, UNION先做简单的合并,然后做进行排序,最后去除重复的记录。
    9.避免使用耗费资源的操作
             带有DISTINCT,UNION ,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎.执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.


             
     

     
     

    展开全文
  • 其实最后得出的结论,就是关联查询速度快于子查询 写这篇的目的主要是把尝试过的方法给记录下,同时复习下有一段时间没有用过的oracle存储过程、自定义函数、包的写法 一、问题说明 为了说清楚问题的核心,我把...
  • oracle数据库查询优化

    千次阅读 2018-08-21 11:58:31
    oracle数据库查询优化 本Markdown编辑器使用StackEdit修改而来,用它写博客,将会带来全新的体验哦: Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 UML序列图和流程图 离线写...
  • Oracle查询优化

    2021-03-24 17:24:28
    查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的上建立索引。 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t...
  • ORACLE中的子查询

    千次阅读 2009-10-15 14:21:00
    子查询”就是查询中嵌套着另一个查询,也即通过SELECT语句的嵌套使用形成子查询。当我们不知道特定的查询条件时,可以用子查询来为父查询提供查询条件以获得查询结果ORACLE中常见的子查询有下面几类:A、单行...
  • Oracle数据库查询优化

    千次阅读 2011-12-22 16:23:14
    本文描述了Oracle查询优化程序,它是数据库的关键组件,能让Oracle 的用户获得极佳的执行性能。Oracle查询优化技术在功能上无与伦比,本文详细讨论了查询优化的所有重要领域。  简介  什么是查询优化...
  • Oracle 大数据量查询优化

    万次阅读 2018-01-18 22:31:51
    前言:平常写的SQL可能主要以实现查询结果为主,但如果数据量一大,就会突出SQL查询语句优化的性能独特之处.一般的数据库设计都会建索引查询,这样较全盘扫描查询的确快了不少.下面总结下SQL查询语句的几个优化效率的...
  • 各路大神,我这个SQL感觉查询出来数据速度特别慢啊,要全部查询出来要120多秒,然后想建立索引,但是发现建立索引并不能优化多少,有没有其他方法可以给到优化呢?感谢各位了!!! SELECT T1.*, c.COMPANY_NAME ...
  • Oracle大数据查询优化

    2018-11-12 18:35:59
    Oracle大数据查询优化 https://www.cnblogs.com/zhaojinhui/p/3881160.html 1、对于像状态之类的,不是很多的,就可以加位图索引,对于唯一的,就加唯一索引,其余的创建普通索引。 2、尽量不要使用select * ...
  • Oracle大数据常见优化查询

    万次阅读 2017-07-23 15:29:55
    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select ...
  • Oracle 子查询展开(subquery unnesting)

    千次阅读 2017-07-10 15:04:52
    Oracle 子查询展开(subquery unnesting)
  • ORACLE多表查询优化

    千次阅读 2009-10-28 17:39:00
    ORACLE多表查询优化这里提供的是执行性能的优化,而不是后台数据库优化器资料:参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引等优化方案太过简单就不列入了,嘿嘿):执行路径:ORACLE的这个...
  • 灵活应用ROWNUM虚拟  Rownum并不是表中实际存在的物理,而是一个可以...但是如果不了解Rownum的特性就草率的使用它,则有可能无法输出期望的结果,同时也无法按照设想的数据扫描方式运行。现在讨论一下如何利用R
  • Oracle 查询技巧与优化(二) 多表查询

    万次阅读 多人点赞 2016-08-05 08:59:59
    关于Oracle多表查询的一些技巧和优化~
  •  通过Oracle提供的PL/SQL编程语言,可以实现创建过程化SQL函数。在函数中不仅可以存在一个以上的SQL,而且还可以存在多样化的运算、条件处理、循环执行等语句。它依据外部所接受的值或固定的常量来执行必要的数据...
  • Oracle查询优化-03操作多个表

    千次阅读 2017-02-07 20:41:36
    因为子查询的join(emp2.ename ,emp2.job ,emp2.sal)没有重复行,说这个查询可以直接改写为inner join SQL> explain plan for 2 select a.ename, a.job, a.sal, a.deptno 3 from emp a join emp2 b on...
  • oracle 子查询走hash内连接的问题

    千次阅读 2018-08-26 12:28:02
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production “CORE 11.2.0.1.0 Production” TNS for 64-bit Windows: Version 11.2.0.1.0 -...
  • 本文将总结一些Oracle查询优化方法与改写技巧,通过一些案例说明用法,本文为观看《Oracle查询优化改写技巧与案例》一书观后的一些总结,大家有空的话可以去阅读一下,下面直接分点总结一些比较有用的优化技巧。...
  • 4.1 插入新记录问题向表中插入一条新的...讨论作为一种简便方式,在insert语句中,可以省略字段列表,然而,如果语句中没有出要插入行中的目标字段,则必须要插入表中的所有,需要注意的,在插入值列表中所出的值
  • 文章目录SQL语句优化概述SQL语句优化是提高性能的重要环节SQL语句优化的一般性原则常见SQL优化方法共享SQL语句减少访问数据库的次数SELECT语句中避免使用`*`WHERE子句中的连接顺序利用DECODE函数来减少处理时间删除...
  • ORACLE数据库(四)----子查询

    千次阅读 2021-03-15 21:23:12
    在一个查询语句中,嵌入一条查询语句,该条嵌入的语句的查询结果成为外层SQL的一部分,即子查询 文章目录一、子查询作为条件二、子查询作为数据源三、子查询作为常量四、子查询进行增删改五、子查询分类1 语法2 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 38,251
精华内容 15,300
关键字:

oracle结果列使用子查询优化