ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 记一次sql优化

记一次sql优化

原创 Linux操作系统 作者:tian1982tian 时间:2012-07-26 15:32:13 0 删除 编辑
      今天开发有个应用sql跑了几十分钟也没出现结果,他的方法:
1:A和B表都有20多万行数据,开发用B表每次取出1000行做去重操作然后再和A表联合将需要的结果放到一个临时表中
2:两个20多万行的表数据直接联合取数据放到临时表
以上2种方法都不尽人意,以下是优化过程
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 26 14:30:07 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn test/test
Connected.
SQL> set timing on
SQL> set autot exp stat
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot trace exp stat
SQL>
SQL> SELECT
  2         b.id,b.x, b.y,b.z,a.m,a.l,a.h,a.i,a.xx,b.yy
  3  FROM   a, b
  4  WHERE  a.col1=b.col2
  5  AND    b.col2 is not null
  6  AND    a.col1 is not null;
12903 rows selected.
Elapsed: 00:00:19.26
Execution Plan
----------------------------------------------------------
Plan hash value: 4038516337
--------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   645K|    86M|       | 67511   (1)| 00:13:31 |
|*  1 |  HASH JOIN                     |         |   645K|    86M|31M| 67511   (1)| 00:13:31 |
|*  2 |   TABLE ACCESS FULL|         |   442K|    26M|       | 45086   (1)| 00:09:02 |
|*  3 |   TABLE ACCESS FULL|         |  2226K|   163M|       | 11463   (1)| 00:02:18 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."COL1"="B"."COL2")
   2 - filter("A"."COL1" IS NOT NULL)
   3 - filter("B"."COL2" IS NOT NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     256930  consistent gets
     138271  physical reads
          0  redo size
    1468518  bytes sent via SQL*Net to client
       9952  bytes received via SQL*Net from client
        862  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12903  rows processed
SQL>

create index idx_col1 on (col1)  tablespace xx_index;
create index idx_col2 on (col2)  tablespace xx_index;
drop table tmpyj;
CREATE TABLE tmpyj
AS
SELECT /*+ index(a,idx_col1) index(b,idx_col2) */
       b.id,b.x, b.y,b.z,a.m,a.l,a.h,a.i,a.xx,b.yy
FROM   a, b
WHERE  a.col1=b.col2
AND    b.col2 is not null
AND    a.col1 is not null;
 
SELECT
        id,x,y,z,m,l,h,i,xx,yy
FROM (
       SELECT   
             a.*,ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY  yy DESC) RN
       FROM tmpyj a
      )
WHERE rn=1;
 
SQL> SELECT /*+ index(a,idx_col1) index(b,idx_col2) */
  2         b.id,b.x, b.y,b.z,a.m,a.l,a.h,a.i,a.xx,b.yy
  3  FROM   a, b
  4  WHERE  a.col1=b.col2
  5  AND    b.col2 is not null
  6  AND    a.col1 is not null;
12903 rows selected.
Elapsed: 00:00:02.95
Execution Plan
----------------------------------------------------------
Plan hash value: 2104990369
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |   645K|    86M|  1686K  (1)| 05:37:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID  |                  |     1 |    77 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                  |   645K|    86M|  1686K  (1)| 05:37:14 |
|   3 |    TABLE ACCESS BY INDEX ROWID|                  |   442K|    26M|   355K  (1)| 01:11:07 |
|*  4 |     INDEX FULL SCAN           | idx_col1         |   437K|       |  1316   (1)| 00:00:16 |
|*  5 |    INDEX RANGE SCAN           | idx_col2         |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."COL1" IS NOT NULL)
   5 - access("A"."COL1"="B"."COL2")
       filter("B"."COL2" IS NOT NULL)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1245150  consistent gets
          0  physical reads
          0  redo size
    1092468  bytes sent via SQL*Net to client
       9952  bytes received via SQL*Net from client
        862  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12903  rows processed
SQL>

优化思路:
  优化前:sql返回结果时间是19.26秒,逻辑读和物理读都达到了10万多,全表扫描,两表之间是哈希连接
  优化后:添加了索引及sql hint使得执行计划走索引的路线,两表之间是循环嵌套连接,虽然逻辑读达到了百万,但是消除了从物理磁盘读取数据,且返回结果时间得到了明显的提升,在2.95秒内就可以返回结果;去重操作没有放在两个大表联合时候做,是因为在大表联合时候去去重使得sql变的更加复杂,写出高效的sql几率减小,故放在有2000行的小表里去重(这里用的是oracle 分析函数,按col2字段分区,然后按照更新时间倒序排序,然后再取出第一条数据就是我们所要的结果)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16400082/viewspace-736972/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle sql 优化
下一篇: oracle 连接数相关
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    162742