Re: Query with dblink runs slow

From: NetComrade <netcomrade_at_netscape.net>
Date: 31 Jul 2002 08:48:23 -0700
Message-ID: <ab810584.0207310748.4deb8252@posting.google.com>

Never trust the docs :) The following two queries both USE and DON'T USE the indices on the remote server. It shows that query needs playing around with. Maybe w/ CBO they don't work (it would be very sad)

I have 100's of queries running over db-link on tables w/ millions of records w/o any problems.

SQL> explain plan for
  2 select email
from member_details
where member_id = (select member_id
from teams
where id=:var1) 3 4 5 6
  7 /

Explained.

SQL> @explain

EXPLAIN                                  OTHER
----------------------------------------
----------------------------------------

0 SELECT STATEMENTCost=
1 FILTER

2      REMOTE                            SELECT "MEMBER_ID","EMAIL"
FROM
                                         "MEMBER_DETAILS"

"MEMBER_DETAILS"

3      TABLE ACCESS TEAMS
4        INDEX TEAMS_PK

can be rewritten as:

SQL> explain plan for
  2 select md.email
from member_details md, teams t
where
t.member_id=md.member_id
and
t.id=:var1
  3 4 5 6 7 8
SQL> / Explained.

SQL> @explain

EXPLAIN                                  OTHER
----------------------------------------
----------------------------------------

0 SELECT STATEMENTCost=
1 MERGE JOIN

2      TABLE ACCESS TEAMS
3        INDEX TEAMS_PK
4      REMOTE                            SELECT "MEMBER_ID","EMAIL"
FROM
                                         "MEMBER_DETAILS"
"MEMBER_DETAILS" WHERE
                                         :1="MEMBER_ID"

TurkBear <jgreco1_at_mn.rr.com> wrote in message news:<9ge3kugdh5oadub3c7g7hdbed5g698g0qb_at_4ax.com>...
> Sorry NetComrade, Remote Database links do NOT use the indexes on the remote server..
> Check the docs..
> ( at least thru 8.1.x)
>
Received on Wed Jul 31 2002 - 10:48:23 CDT