关于dblink的sql优化

开发丢来一个sql，说跑10多分钟都没有出结果

sql及执行计划如下：

SQL> set lines 200 pagesize 999
SQL> explain plan for select distinct u.id, c.real_name,u.user_name,c.phone,c.region,c.email,pv.name 
  2    from orders o , orderitem oi , users u ,customer c,product_version pv 
  3   where o.user_id = u.id
  4   and u.id = c.user_id
  5   and o.id = oi.order_id
  6   and oi.product_id = pv.product_id
  7   and oi.version_id = pv.version_id
  8   and oi.product_id in (
  9  28801
 10  ,28800
 11  ,28693
 12  ,28542
 13  ,28508
 14  ,28766
 15  ,28722
 16  ,28750
 17  ,28708
 18  ,28762
 19  ,28765
 20  ,28702
 21  ,28767
 22  ,28718
 23  ,28754
 24  ,28705
 25  ,28764
 26  ,28721
 27  ,28751
 28  ,28709
 29  ,28761
 30  ,28719
 31  ,28482
 32  ,28861
 33  ,28860
 34  ,28480
 35  ,28481
 36  ,28859
 37  ,28463
 38  )
 39   --and oi.version_id =1
 40   and o.ordertime >= to_date('20130918', 'yyyymmdd')
 41   and o.ordertime < to_date('20131012','yyyymmdd')
 42   and o.orderstatusid = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3641462162

-----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |   480 |    13   (8)| 00:00:01 |        |      |
|   1 |  HASH UNIQUE      |                 |     1 |   480 |    13   (8)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS    |                 |     1 |   480 |    12   (0)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS   |                 |     1 |   382 |    10   (0)| 00:00:01 |        |      |
|   4 |     NESTED LOOPS  |                 |     1 |   129 |     9   (0)| 00:00:01 |        |      |
|   5 |      NESTED LOOPS |                 |     1 |    90 |     6   (0)| 00:00:01 |        |      |
|   6 |       REMOTE      | ORDERS          |     1 |    48 |     4   (0)| 00:00:01 | NEW_N~ | R->S |
|   7 |       REMOTE      | USERS           |     1 |    42 |     2   (0)| 00:00:01 | NEW_T~ | R->S |
|   8 |      REMOTE       | ORDERITEM       |     1 |    39 |     3   (0)| 00:00:01 | NEW_N~ | R->S |
|   9 |     REMOTE        | PRODUCT_VERSION |     1 |   253 |     1   (0)| 00:00:01 | NEW_N~ | R->S |
|  10 |    REMOTE         | CUSTOMER        |     1 |    98 |     2   (0)| 00:00:01 | NEW_N~ | R->S |
-----------------------------------------------------------------------------------------------------


Remote SQL Information (identified by operation id):
----------------------------------------------------


   6 - SELECT "ID","USER_ID","ORDERSTATUSID","ORDERTIME" FROM "ORDERS" "O" WHERE
       "ORDERTIME">=TO_DATE(' 2013-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ORDERSTATUSID"=1
       AND "ORDERTIME"<TO_DATE(' 2013-10-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
       'NEW_NEWCOURSE' )

   7 - SELECT "ID","USER_NAME" FROM "USERS" "U" WHERE :1="ID" (accessing 'NEW_TO_CORE' )

   8 - SELECT "ORDER_ID","PRODUCT_ID","VERSION_ID" FROM "ORDERITEM" "OI" WHERE
       ("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
       "PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
       "PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
       "PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
       "PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
       "PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
       "PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
       "PRODUCT_ID"=28861) AND :1="ORDER_ID" (accessing 'NEW_NEWCOURSE' )

   9 - SELECT "PRODUCT_ID","VERSION_ID","NAME" FROM "PRODUCT_VERSION" "PV" WHERE
       ("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
       "PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
       "PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
       "PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
       "PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
       "PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
       "PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
       "PRODUCT_ID"=28861) AND :1="PRODUCT_ID" AND :2="VERSION_ID" (accessing 'NEW_NEWCOURSE' )

  10 - SELECT "USER_ID","REAL_NAME","EMAIL","PHONE","REGION" FROM "CUSTOMER" "C" WHERE
        :1="USER_ID" (accessing 'NEW_NEWCOURSE' )

SQL> 

很明显看得出来，所有的表都是通过dblink访问的远程的库里的数据，而驱动站点选择了本地库

问了这位朋友得知，查询后返回的数据也就30条左右

要优化这个sql很简单，把驱动站点放到远程数据库上，过滤完数据在返回结果集

最后这个sql能在2秒内执行完成