OraFAQ Forum: Networking and Gateways

Home » RDBMS Server » Networking and Gateways » dblink and left join

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dblink and left join [message #552047]

Mon, 23 April 2012 05:36

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 !

Report message to a moderator

Re: dblink and left join [message #552126 is a reply to message #552047]

Mon, 23 April 2012 12:44

John Watson
Messages: 8929
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.

Report message to a moderator

Previous Topic:	Doubt in Create database link command
Next Topic:	database cloning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:09:02 CDT 2024