今天开发有个应用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

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博客

记一次sql优化

最新文章