Home » RDBMS Server » Networking and Gateways » dblink and left join
dblink and left join [message #552047] Mon, 23 April 2012 05:36 Go to next message
lerry_wu
Messages: 12
Registered: December 2011
Junior Member
hi:
I meet a strange problem, I used oracle transparent getway to create a dblink which conenct to sqlserver.
when the sql used dblink and left join ,it query slowly
but it excute quickly on sqlserver
moreover, if i remove left join ,it also excute quickly

sql:
select distinct a."sReelId", b.* from (select * from log_Board_Parts@ms_lcr where "sBoard" in ('N0259FBDC49CCNS0S4')) a
left join
(select "sReelId","sDateCode","sLotNo","sVendor" from reeldata@ms_lcr) b
on a."sReelId"=b."sReelId";

how can i slove this problem?
thank you !
Re: dblink and left join [message #552126 is a reply to message #552047] Mon, 23 April 2012 12:44 Go to previous message
John Watson
Messages: 4102
Registered: January 2010
Location: Global Village
Senior Member
Hello - I hesitated to reply, because I know nothing about SQL Server, and I thought the problem could be there. But no-one has replied, so I'll have a go.

An outer join may have a very different execution plan from an inner join. Here's an example - you'll see that even though the query is fully remote whether it is inner or outer, the execution plan is very different:
orcl> conn scott/tiger
Connected.

Session altered.

orcl> set autotrace traceonly explain
orcl> select * from emp@l1 natural join dept@l1;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE      |         |    14 |   812 |     6  (17)| 00:00:01 |        |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |   ORCL |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |   ORCL |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |        |
|   5 |    TABLE ACCESS STORAGE FULL | EMP     |    14 |   532 |     3   (0)| 00:00:01 |   ORCL |
-------------------------------------------------------------------------------------------------

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

   4 - access("A2"."DEPTNO"="A1"."DEPTNO")
       filter("A2"."DEPTNO"="A1"."DEPTNO")

Note
-----
   - fully remote statement

orcl> select * from emp@l1 natural left join dept@l1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970

--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE    |      |    14 |   812 |     7  (15)| 00:00:01 |        |
|*  1 |  HASH JOIN OUTER           |      |    14 |   812 |     7  (15)| 00:00:01 |        |
|   2 |   TABLE ACCESS STORAGE FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |   ORCL |
|   3 |   TABLE ACCESS STORAGE FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |   ORCL |
--------------------------------------------------------------------------------------------

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

   1 - access("A2"."DEPTNO"="A1"."DEPTNO"(+))

Note
-----
   - fully remote statement

orcl>
Your more complex example might also mean that the query cannot be "fully remote" when it includes the outer join.
Hope this helps - at least with diagnosis, if not solution.
Previous Topic: Doubt in Create database link command
Next Topic: database cloning
Goto Forum:
  


Current Time: Thu Apr 24 23:14:53 CDT 2014

Total time taken to generate the page: 0.12885 seconds