記一次sql優化

      今天開發有個應用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欄位分割槽，然後按照更新時間倒序排序，然後再取出第一條資料就是我們所要的結果）