Re: help appreciated with db-link CTAS

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 8 Jul 2002 16:28:02 +0100
Message-ID: <1026142025.25153.0.nnrp-13.9e984b29@news.demon.co.uk>

I am slightly surprised that you got the

    load as select
      remote
at all - I recall reading a note on Metalink recently that said the expected behaviour was the

    load as select

      nested loop
        remote
        remote

that you saw in the worst case.

Have you tried variants of:

create table as
select

    columns, constants, columns
from

    (

        select /*+ no_merge */ columns
        from dept_at_remote, emp_at_remote

    )

Otherwise a workaround (if it is allowed) is to create a join view at the remote database and do 'select from view_at_remote'

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Telemachus wrote in message ...


>

>SQL> explain plan for select d.deptno,'X'

> from emp_at_oracle@remote e,

> dept_at_oracle@remote d

> where d.deptno=e.deptno;

>




>| CREATE TABLE STATEMENT    |          |       |      |        |       |

>|

>

>|  LOAD AS SELECT           |          |       |      |        |       |

>|

>

>|   REMOTE                  |          |       |      |        |       |

>|

Received on Mon Jul 08 2002 - 10:28:02 CDT