有关DBLINK操作的语句执行机制及优化方式

dingjun123 · 发表于 2014-2-17 09:49

如果2边都是大数据量处理，那driving_site也几乎用途不大，最好的，就是数据导到本地来做了

风行年华 · 发表于 2014-2-17 17:17

学习了，大牛

g644516804 · 发表于 2014-4-25 08:51

dingjun123 发表于 2013-5-6 17:37
大家尽情举例dblink操作的优化啊，我补充补充，

兔哥，我这有条sql 真不知道怎么优化了，用了dblink之后就很慢了
原sql:

select *
  from (select rownum num, temp.*
      from (select  a.fid,
                     a.codenum,
                     a.parentcode,
                     a.remittername,
                     a.remitAccount,
                     a.receiveaddress,
                     a.accountname,
                     a.openbank,
                     a.accountno,
                     a.claimamount,
                     a.reachamountdate,
                     a.remitmethod,
                     b.type          remittype,
                     c.category       amountscategory,
                     a.belregion,
                     a.claimstate,
                     a.turnamountstate,
                     a.claimtime,
                     a.turnamounttime,
                     d.deptname       remitdepart,
                     a.remitaccountdate,
                     a.writeoffstate,
                     a.statementsencode,
                     a.transportinvoice,
                     a.alreadyamount,
                     a.unuseamount,
                     a.remark,
                     f.empname       addperson,
                     a.addtime,
                     g.empname       updateperson,
                     a.totamount,
                     h.empname       claimman,
                     a.status,
                     a.fundCompany,
                     a.recBelong,
                     k.principal,
                     a.updateTime,
                     a.repealState,
                     a.remitdepart    remitDeptCode,
                     a.claimType,
                     s.sname          as supplier,
                     a.payAppForm,
                     cus.fname_l2    as customer,
                     a.supplier       as supplierCode,
                     a.customer       as customerCode,
                     a.transactionId,
                     i.opendate       as recipIssu,
                     j.empname       as recipIssuName,
                     i.deposityno    as recipIssuCode,
                     a.collectiontype as collectiontype,
                     y.deptname       as playdeptName,
                     a.playdeptname    as playdeptNameCode
               from t_claim_remittancerecord a
               left join t_pay_remittype b
                  on a.remittype = b.fid
               left join t_pay_fundtype c
                  on c.fid = a.amountscategory
               left join t_org_department d
                  on a.remitdepart = d.finasyscode
               left join t_org_department y
                  on a.playdeptname = y.finasyscode
               and y.status = 1
               left join t_org_employee f
                  on f.empcode = a.addperson
               left join t_org_department k
                  on f.deptid = k.id
               left join t_org_employee g
                  on g.empcode = a.updateperson
               left join t_org_employee h
                  on h.empcode = a.claimman
               left join t_bd_customer cus
                  on cus.fnumber = a.customer
               left join V_LMS_SUPPLIER s
                  on s.snumber = a.supplier
               left join t_deposit_printer i
                  on i.codenum = a.codenum
               left join t_org_employee j
                  on i.createuser = j.empcode
               WHERE 1 = 1
               and a.writeOffState like '%未核销%'
               and a.reachAmountDate between to_date('2014-01-01','yyyy-mm-dd') and to_date('2014-04-01','yyyy-mm-dd')
               and a.repealstate != 1
               order by addTime desc, codeNum) temp) t WHERE t.num<=50 and t.num>0


Plan hash value: 3345438767

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                         |                         | 27687 | 99M|    | 23407 (1)| 00:04:41 |
|*  1 |  VIEW                                     |                         | 27687 | 99M|    | 23407 (1)| 00:04:41 |
| 2 | COUNT                                  |                         |    |    |    |          |       |
| 3 | VIEW                                  |                         | 27687 | 99M|    | 23407 (1)| 00:04:41 |
| 4 |    SORT ORDER BY                         |                         | 27687 | 20M| 21M| 23407 (1)| 00:04:41 |
|*  5 |    HASH JOIN RIGHT OUTER                |                         | 27687 | 20M|    | 18829 (1)| 00:03:46 |
| 6 |    TABLE ACCESS FULL                   | T_BD_CUSTOMER          | 27281 |  1012K|    |  1847 (1)| 00:00:23 |
|*  7 |    HASH JOIN RIGHT OUTER                |                         | 27628 | 19M|    | 16980 (1)| 00:03:24 |
|*  8 |       TABLE ACCESS FULL                   | T_ORG_DEPARTMENT       | 11447 | 413K|    | 137 (1)| 00:00:02 |
|*  9 |       HASH JOIN RIGHT OUTER             |                         | 27628 | 18M|    | 16842 (1)| 00:03:23 |
|* 10 |       TABLE ACCESS FULL                | T_ORG_DEPARTMENT       | 10843 | 423K|    | 137 (1)| 00:00:02 |
|* 11 |       HASH JOIN RIGHT OUTER             |                         | 27628 | 17M|    | 16704 (1)| 00:03:21 |
|  12 |       VIEW                            | index$_join$_028       |  1516 | 80348 |    | 28 (4)| 00:00:01 |
|* 13 |          HASH JOIN                      |                         |    |    |    |          |       |
|* 14 |          HASH JOIN                      |                         |    |    |    |          |       |
|* 15 |          INDEX RANGE SCAN             | IDX_T_BD_FUSEDSTATUS    |  1516 | 80348 |    |    2 (0)| 00:00:01 |
|  16 |          INDEX FAST FULL SCAN          | IDX_BD_SUPPLIER_NUM    |  1516 | 80348 |    |    6 (0)| 00:00:01 |
|* 17 |          INDEX FAST FULL SCAN          | IX_SUPPNAME             |  1516 | 80348 |    | 23 (0)| 00:00:01 |
|* 18 |       HASH JOIN RIGHT OUTER             |                         | 27628 | 16M|    | 16675 (1)| 00:03:21 |
|  19 |          TABLE ACCESS FULL                | T_PAY_REMITTYPE       |    8 | 168 |    |    3 (0)| 00:00:01 |
|* 20 |          HASH JOIN RIGHT OUTER          |                         | 27628 | 15M|    | 16671 (1)| 00:03:21 |
|  21 |          TABLE ACCESS FULL             | T_PAY_FUNDTYPE          | 33 |  1023 |    |    3 (0)| 00:00:01 |
|* 22 |          HASH JOIN RIGHT OUTER          |                         | 27628 | 14M|    | 16668 (1)| 00:03:21 |
|  23 |          TABLE ACCESS FULL             | T_ORG_DEPARTMENT       | 16422 | 176K|    | 137 (1)| 00:00:02 |
|* 24 |          HASH JOIN RIGHT OUTER          |                         | 27628 | 14M|  5048K| 16530 (1)| 00:03:19 |
|  25 |             TABLE ACCESS FULL             | T_ORG_EMPLOYEE          | 147K|  3317K|    | 892 (1)| 00:00:11 |
|* 26 |             HASH JOIN RIGHT OUTER       |                         | 27628 | 13M|  4328K| 14680 (1)| 00:02:57 |
|  27 |             TABLE ACCESS FULL          | T_ORG_EMPLOYEE          | 147K|  2596K|    | 892 (1)| 00:00:11 |
|* 28 |             HASH JOIN RIGHT OUTER       |                         | 27628 | 13M|  4328K| 12889 (1)| 00:02:35 |
|  29 |             TABLE ACCESS FULL          | T_ORG_EMPLOYEE          | 147K|  2596K|    | 892 (1)| 00:00:11 |
|  30 |             NESTED LOOPS OUTER          |                         | 27628 | 13M|    | 11121 (1)| 00:02:14 |
|* 31 |                HASH JOIN RIGHT OUTER    |                         | 27628 | 12M|    | 11120 (1)| 00:02:14 |
|  32 |                TABLE ACCESS FULL       | T_DEPOSIT_PRINTER       | 420 | 28980 |    |    5 (0)| 00:00:01 |
|* 33 |                TABLE ACCESS BY INDEX ROWID| T_CLAIM_REMITTANCERECORD | 27628 | 10M|    | 11114 (1)| 00:02:14 |
|* 34 |                INDEX SKIP SCAN          | IDX_REMIT_COMP4       | 27714 |    |    |  5402 (1)| 00:01:05 |
|  35 |                TABLE ACCESS BY INDEX ROWID | T_ORG_EMPLOYEE          |    1 | 18 |    |    1 (0)| 00:00:01 |
|* 36 |                INDEX UNIQUE SCAN       | UK_EMPLOYEE_EMPCODE    |    1 |    |    |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."NUM">0 AND "T"."NUM"<=50)
5 - access("CUS"."FNUMBER"(+)=SYS_OP_C2C("A"."CUSTOMER"))
7 - access("A"."REMITDEPART"="D"."FINASYSCODE"(+))
8 - filter("D"."FINASYSCODE"(+) IS NOT NULL)
9 - access("A"."PLAYDEPTNAME"="Y"."FINASYSCODE"(+))
  10 - filter("Y"."FINASYSCODE"(+) IS NOT NULL AND "Y"."STATUS"(+)=1)
  11 - access("FNUMBER"(+)=SYS_OP_C2C("A"."SUPPLIER"))
  13 - access(ROWID=ROWID)
  14 - access(ROWID=ROWID)
  15 - access("FUSEDSTATUS"=1)
  17 - filter("FUSEDSTATUS"=1)
  18 - access("A"."REMITTYPE"="B"."FID"(+))
  20 - access("C"."FID"(+)="A"."AMOUNTSCATEGORY")
  22 - access("F"."DEPTID"="K"."ID"(+))
  24 - access("F"."EMPCODE"(+)="A"."ADDPERSON")
  26 - access("G"."EMPCODE"(+)="A"."UPDATEPERSON")
  28 - access("H"."EMPCODE"(+)="A"."CLAIMMAN")
  31 - access("I"."CODENUM"(+)="A"."CODENUM")
  33 - filter("A"."REPEALSTATE"<>1)
  34 - access("A"."REACHAMOUNTDATE">=TIMESTAMP' 2014-01-01 00:00:00' AND "A"."REACHAMOUNTDATE"<=TIMESTAMP' 2014-04-01
            00:00:00')
   filter("A"."REACHAMOUNTDATE">=TIMESTAMP' 2014-01-01 00:00:00' AND "A"."WRITEOFFSTATE" LIKE '%未核销%' AND
            "A"."REACHAMOUNTDATE"<=TIMESTAMP' 2014-04-01 00:00:00' AND "A"."WRITEOFFSTATE" IS NOT NULL)
  36 - access("I"."CREATEUSER"="J"."EMPCODE"(+))


迁移后的sql:
select *
  from (select rownum num, temp.*
      from (select  a.fid,
                     a.codenum,
                     a.parentcode,
                     a.remittername,
                     a.remitAccount,
                     a.receiveaddress,
                     a.accountname,
                     a.openbank,
                     a.accountno,
                     a.claimamount,
                     a.reachamountdate,
                     a.remitmethod,
                     b.type          remittype,
                     c.category       amountscategory,
                     a.belregion,
                     a.claimstate,
                     a.turnamountstate,
                     a.claimtime,
                     a.turnamounttime,
                     d.deptname       remitdepart,
                     a.remitaccountdate,
                     a.writeoffstate,
                     a.statementsencode,
                     a.transportinvoice,
                     a.alreadyamount,
                     a.unuseamount,
                     a.remark,
                     f.empname       addperson,
                     a.addtime,
                     g.empname       updateperson,
                     a.totamount,
                     h.empname       claimman,
                     a.status,
                     a.fundCompany,
                     a.recBelong,
                     k.principal,
                     a.updateTime,
                     a.repealState,
                     a.remitdepart    remitDeptCode,
                     a.claimType,
                     s.sname          as supplier,
                     a.payAppForm,
                     cus.fname_l2    as customer,
                     a.supplier       as supplierCode,
                     a.customer       as customerCode,
                     a.transactionId,
                     i.opendate       as recipIssu,
                     j.empname       as recipIssuName,
                     i.deposityno    as recipIssuCode,
                     a.collectiontype as collectiontype,
                     y.deptname       as playdeptName,
                     a.playdeptname    as playdeptNameCode
               from t_claim_remittancerecord a
               left join t_pay_remittype b
                  on a.remittype = b.fid
               left join t_pay_fundtype c
                  on c.fid = a.amountscategory
               left join t_org_department d
                  on a.remitdepart = d.finasyscode
               left join t_org_department y
                  on a.playdeptname = y.finasyscode
               and y.status = 1
               left join t_org_employee f
                  on f.empcode = a.addperson
               left join t_org_department k
                  on f.deptid = k.id
               left join t_org_employee g
                  on g.empcode = a.updateperson
               left join t_org_employee h
                  on h.empcode = a.claimman
               left join t_bd_customer@dipfin cus
                  on cus.fnumber = a.customer
               left join V_LMS_SUPPLIER@difpin s
                  on s.snumber = a.supplier
               left join t_deposit_printer i
                  on i.codenum = a.codenum
               left join t_org_employee j
                  on i.createuser = j.empcode
               WHERE 1 = 1
               and a.writeOffState like '%未核销%'
               and a.reachAmountDate between to_date('2014-01-01','yyyy-mm-dd') and to_date('2014-04-01','yyyy-mm-dd')
               and a.repealstate != 1
               order by addTime desc, codeNum) temp) t WHERE t.num<=50 and t.num>0



Plan hash value: 3877825334

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                            |                         | 27687 | 99M|    | 38384 (1)| 00:07:41 |       |    |
|*  1 |  VIEW                                        |                         | 27687 | 99M|    | 38384 (1)| 00:07:41 |       |    |
| 2 | COUNT                                     |                         |    |    |    |          |       |       |    |
| 3 | VIEW                                     |                         | 27687 | 99M|    | 38384 (1)| 00:07:41 |       |    |
| 4 |    SORT ORDER BY                            |                         | 27687 | 87M| 108M| 38384 (1)| 00:07:41 |       |    |
| 5 |    NESTED LOOPS OUTER                      |                         | 27687 | 87M|    | 19309 (1)| 00:03:52 |       |    |
|*  6 |    HASH JOIN RIGHT OUTER                |                         | 27687 | 86M|    | 19308 (1)| 00:03:52 |       |    |
| 7 |       TABLE ACCESS FULL                   | T_DEPOSIT_PRINTER       | 420 | 28980 |    |    5 (0)| 00:00:01 |       |    |
| 8 |       VIEW                                  |                         | 27687 | 84M|    | 19302 (1)| 00:03:52 |       |    |
|*  9 |       HASH JOIN RIGHT OUTER                |                         | 27687 | 88M|    | 19302 (1)| 00:03:52 |       |    |
|  10 |       REMOTE                            | V_LMS_SUPPLIER          |  1936 | 956K|    | 66 (0)| 00:00:01 | DIPFIN | R->S |
|  11 |       VIEW                               |                         | 27687 | 74M|    | 19235 (1)| 00:03:51 |       |    |
|* 12 |          HASH JOIN RIGHT OUTER             |                         | 27687 | 24M|  8632K| 19235 (1)| 00:03:51 |       |    |
|  13 |          REMOTE                            | T_BD_CUSTOMER          | 27281 |  8312K|    |  1184 (1)| 00:00:15 | DIPFIN | R->S |
|* 14 |          HASH JOIN RIGHT OUTER             |                         | 27628 | 15M|    | 16821 (1)| 00:03:22 |       |    |
|  15 |          TABLE ACCESS FULL                | T_ORG_DEPARTMENT       | 16422 | 176K|    | 137 (1)| 00:00:02 |       |    |
|* 16 |          HASH JOIN RIGHT OUTER          |                         | 27628 | 15M|    | 16683 (1)| 00:03:21 |       |    |
|* 17 |             TABLE ACCESS FULL             | T_ORG_DEPARTMENT       | 11447 | 413K|    | 137 (1)| 00:00:02 |       |    |
|* 18 |             HASH JOIN RIGHT OUTER          |                         | 27628 | 14M|    | 16545 (1)| 00:03:19 |       |    |
|* 19 |             TABLE ACCESS FULL             | T_ORG_DEPARTMENT       | 10843 | 423K|    | 137 (1)| 00:00:02 |       |    |
|* 20 |             HASH JOIN RIGHT OUTER          |                         | 27628 | 13M|  5048K| 16407 (1)| 00:03:17 |       |    |
|  21 |             TABLE ACCESS FULL             | T_ORG_EMPLOYEE          | 147K|  3317K|    | 897 (2)| 00:00:11 |       |    |
|* 22 |             HASH JOIN RIGHT OUTER       |                         | 27628 | 13M|  4328K| 14598 (1)| 00:02:56 |       |    |
|  23 |                TABLE ACCESS FULL          | T_ORG_EMPLOYEE          | 147K|  2596K|    | 897 (2)| 00:00:11 |       |    |
|* 24 |                HASH JOIN RIGHT OUTER       |                         | 27628 | 12M|  4328K| 12848 (1)| 00:02:35 |       |    |
|  25 |                TABLE ACCESS FULL          | T_ORG_EMPLOYEE          | 147K|  2596K|    | 897 (2)| 00:00:11 |       |    |
|* 26 |                HASH JOIN RIGHT OUTER       |                         | 27628 | 12M|    | 11122 (1)| 00:02:14 |       |    |
|  27 |                TABLE ACCESS FULL          | T_PAY_FUNDTYPE          | 33 |  1023 |    |    3 (0)| 00:00:01 |       |    |
|* 28 |                HASH JOIN RIGHT OUTER    |                         | 27628 | 11M|    | 11118 (1)| 00:02:14 |       |    |
|  29 |                   TABLE ACCESS FULL       | T_PAY_REMITTYPE       |    8 | 168 |    |    3 (0)| 00:00:01 |       |    |
|* 30 |                   TABLE ACCESS BY INDEX ROWID| T_CLAIM_REMITTANCERECORD | 27628 | 10M|    | 11114 (1)| 00:02:14 |       |    |
|* 31 |                   INDEX SKIP SCAN          | IDX_REMIT_COMP4       | 27714 |    |    |  5402 (1)| 00:01:05 |       |    |
|  32 |    TABLE ACCESS BY INDEX ROWID          | T_ORG_EMPLOYEE          |    1 | 18 |    |    1 (0)| 00:00:01 |       |    |
|* 33 |       INDEX UNIQUE SCAN                   | UK_EMPLOYEE_EMPCODE    |    1 |    |    |    1 (0)| 00:00:01 |       |    |
--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."NUM">0 AND "T"."NUM"<=50)
6 - access("I"."CODENUM"(+)="A"."CODENUM")
9 - access("S"."SNUMBER"(+)=SYS_OP_C2C("A"."SUPPLIER"))
  12 - access("CUS"."FNUMBER"(+)=SYS_OP_C2C("A"."CUSTOMER"))
  14 - access("F"."DEPTID"="K"."ID"(+))
  16 - access("A"."REMITDEPART"="D"."FINASYSCODE"(+))
  17 - filter("D"."FINASYSCODE"(+) IS NOT NULL)
  18 - access("A"."PLAYDEPTNAME"="Y"."FINASYSCODE"(+))
  19 - filter("Y"."FINASYSCODE"(+) IS NOT NULL AND "Y"."STATUS"(+)=1)
  20 - access("F"."EMPCODE"(+)="A"."ADDPERSON")
  22 - access("H"."EMPCODE"(+)="A"."CLAIMMAN")
  24 - access("G"."EMPCODE"(+)="A"."UPDATEPERSON")
  26 - access("C"."FID"(+)="A"."AMOUNTSCATEGORY")
  28 - access("A"."REMITTYPE"="B"."FID"(+))
  30 - filter("A"."REPEALSTATE"<>1)
  31 - access("A"."REACHAMOUNTDATE">=TIMESTAMP' 2014-01-01 00:00:00' AND "A"."REACHAMOUNTDATE"<=TIMESTAMP' 2014-04-01 00:00:00')
   filter("A"."REACHAMOUNTDATE">=TIMESTAMP' 2014-01-01 00:00:00' AND "A"."WRITEOFFSTATE" LIKE '%未核销%' AND
            "A"."REACHAMOUNTDATE"<=TIMESTAMP' 2014-04-01 00:00:00' AND "A"."WRITEOFFSTATE" IS NOT NULL)
  33 - access("I"."CREATEUSER"="J"."EMPCODE"(+))

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

  10 - SELECT "SNUMBER","SNAME" FROM "V_LMS_SUPPLIER" "S" (accessing 'DIPFIN' )

  13 - SELECT "FNAME_L2","FNUMBER" FROM "T_BD_CUSTOMER" "CUS" (accessing 'DIPFIN' )

langzaizi0311 · 发表于 2014-5-27 18:50

非常棒，帮我解决了大问题啊！

pineapple_lch · 发表于 2014-7-17 09:31

真是不错的呢，值得大家学习。

moseslin · 发表于 2014-7-17 10:31

虽然还没看懂，还是V兔子一个。

初识orcl · 发表于 2014-7-19 00:06

分布式SQL的相关帖子很少，兔子果然威武。

bjddd192 · 发表于 2014-8-4 10:49

本帖最后由 bjddd192 于 2014-8-4 10:51 编辑

写的真好，受益匪浅啊。
我最近也用到了dblink，发生产后发现有性能问题，用DRIVING_SITE HINT优化产生了一些作用，但是发现居然数据不准了，数据少了
原语句：
WITH TEMPA AS
(
      SELECT nvl(A.po_no,'-') DRP_BILLNO,A.UNTREAD_NO,A.LOCNO STORE_NO,B.ZONE_NO,
            a.locno SEND_STORE,a.store_no RECEIPT_STORE,A.STATUS,
            d.CREATETM DOWN_TIME,d.predeltime REQ_CAR_TIME,
            ( CASE WHEN a.untread_type IN ('3','4') THEN 1 ELSE 0 END) QUALITY,c.sys_no SYS_NO,
            (SELECT min(z.createtm)  FROM city_bill_um_receipt z WHERE z.untread_no=a.untread_no) RECEIPT_TIME
      FROM city_bill_um_untread A
      INNER JOIN CITY_STORE B ON A.LOCNO = B.STORE_NO
      INNER JOIN CITY_STORE c ON A.STORE_NO= c.STORE_NO
      LEFT JOIN Bill_Delivery d ON d.bill_no=a.po_no
      WHERE  NOT EXISTS (SELECT 1 FROM RPT_UNTREAD Z WHERE A.UNTREAD_NO = Z.UNTREAD_NO)
            AND ROWNUM <= 1000
),
TEMPB AS
(
      SELECT H.UNTREAD_NO,
            COUNT(DISTINCT H.BOX_NO) SEND_BOX_QTY,SUM(H.item_qty) SEND_QTY
      FROM CITY_BILL_UM_UNTREAD_DTL H
      WHERE H.UNTREAD_NO IN (SELECT UNTREAD_NO FROM TEMPA)
      GROUP BY H.UNTREAD_NO
)
SELECT a.DRP_BILLNO,a.UNTREAD_NO,a.ZONE_NO,a.STORE_NO,a.QUALITY,a.DOWN_TIME,
      NULL REQ_CAR_TIME,NULL ARRIVE_TIME,a.RECEIPT_TIME,a.SEND_STORE,a.RECEIPT_STORE,
      b.SEND_QTY,b.SEND_BOX_QTY,NULL REF_ORDER,a.STATUS,a.SYS_NO
FROM TEMPA A
INNER JOIN TEMPB B ON A.UNTREAD_NO = B.UNTREAD_NO;
返回结果为1000行，加了HINT后：
WITH
TEMPA AS
(
      SELECT /*+DRIVING_SITE(A)*/ nvl(A.po_no,'-') DRP_BILLNO,A.UNTREAD_NO,A.LOCNO STORE_NO,B.ZONE_NO,
            a.locno SEND_STORE,a.store_no RECEIPT_STORE,A.STATUS,
            d.CREATETM DOWN_TIME,d.predeltime REQ_CAR_TIME,
            ( CASE WHEN a.untread_type IN ('3','4') THEN 1 ELSE 0 END) QUALITY,c.sys_no SYS_NO,
            (SELECT min(z.createtm)  FROM city_bill_um_receipt z WHERE z.untread_no=a.untread_no) RECEIPT_TIME
      FROM city_bill_um_untread A
      INNER JOIN CITY_STORE B ON A.LOCNO = B.STORE_NO
      INNER JOIN CITY_STORE c ON A.STORE_NO= c.STORE_NO
      LEFT JOIN Bill_Delivery d ON d.bill_no=a.po_no
      WHERE NOT EXISTS (SELECT 1 FROM RPT_UNTREAD Z WHERE A.UNTREAD_NO = Z.UNTREAD_NO)
            AND ROWNUM <= 1000
),
TEMPB AS
(
      SELECT /*+DRIVING_SITE(H)*/ H.UNTREAD_NO,
            COUNT(DISTINCT H.BOX_NO) SEND_BOX_QTY,SUM(H.item_qty) SEND_QTY
      FROM CITY_BILL_UM_UNTREAD_DTL H
      INNER JOIN TEMPA A ON A.UNTREAD_NO = H.UNTREAD_NO
      GROUP BY H.UNTREAD_NO
)
SELECT a.DRP_BILLNO,a.UNTREAD_NO,a.ZONE_NO,a.STORE_NO,a.QUALITY,a.DOWN_TIME,
      NULL REQ_CAR_TIME,NULL ARRIVE_TIME,a.RECEIPT_TIME,a.SEND_STORE,a.RECEIPT_STORE,
      b.SEND_QTY,b.SEND_BOX_QTY,NULL REF_ORDER,a.STATUS,a.SYS_NO
FROM TEMPA A
INNER JOIN TEMPB B ON A.UNTREAD_NO = B.UNTREAD_NO;
返回结构只有253行了，真是很奇怪，不知道这结果是怎么来的，大大帮分析下
我不用with as语法，改成子查询就是OK的，为什么会这样呢？如果在dblink里面想用临时表，而且把这个临时表建到远端有什么好办法
我的CITY_打头的就是远程表

chinagjj2008 · 发表于 2014-8-19 15:34

丁总V5 无意中搜索看到的非常详细！！

skiiwujinhua · 发表于 2014-8-30 22:58

兔子兄：
我在个人网页中引用了你这个贴，谢谢。

[精华] 有关DBLINK操作的语句执行机制及优化方式

浏览过的版块