AskTom talk-- the question regarding "parallel select from remote site", version 10.2.0.3

Fabian -- Thanks for the question regarding "parallel select from remote site", version 10.2.0.3

Submitted on 20-Jun-2011 12:57 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 19-Mar-2012 10:19

You Asked

Hi Tom (and other readers)
I'm searching for solution how to implement parallel select from remote site.
So my question is what are possibilities to :
select * from big_partitioned_parallel_table@remote;
or
dbms_mview.refresh('VIEW_BUILD_ON_REMOTE_TABLES');
was not SERIAL_FROM_REMOTE but some kind of "PARALLEL_FROM_REMOTE".
I guess some kind of solution could be using DBMS_DEFER_SYS.SCHEDULE_PUSH (?), but i need to organize it without using SYS account and definitely on destination not source site. And by the way maybe you could answer the question if DBMS_DEFER_SYS can be used as a solution (or there is no sense to investigate it because it has other usage)
thanks and regards,

Ok, more details:
create table LOCAL_TABLE parallel as
select * from big_partitioned_parallel_table@remote;
first step in explain plan would be:
REMOTE REMOTE SERIAL_FROM_REMOTE big_partitioned_parallel_table remote Bytes: 2,272 Cardinality: 1
The second step is
PX SEND ROUND-ROBIN PARALLEL_FROM_SERIAL SYS.:TQ10000 Bytes: 2,272 Cardinality: 1
which is using parallel but in one thread, ie no parallel loading.
So is it possible to parallelize first step (selecting from remote table) to get whole process faster.
The same is with refreshing materialized views which goes in serial if table is remote one.
So the question is -- can we make any loading of segments by database link in parallel mode (in more than one thread)?

and we said...

You cannot do "parallel" over a single database link, you can do parallel on each end of the dblink, but as you have only one network pipe - it'll be serial over that. you'll have to break the job out into smaller pieces yourself.

In 11g, you could use dbms_parallel_execute, but in 10g - you'll have to "do it yourself"

The easiest would be to set up a job (dbms_job, dbms_scheduler) for each partition and do a load in that job. If the local table is partitioned the same way - each job would use dynamic sql to load its partition, something like:

insert /*+ append */ into localtable partition( PNAME )
select * from remotetable@dblink partition(PNAME)


If the local table is NOT partitioned, you will NOT be able to use the APPEND hint - as they would all end up serializing on each other.

see also

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211

Reviews

June 22, 2011 - 1am Central time zone Bookmark | Bottom | Top Reviewer: Fabian  ok, little confusing that one network pipe could be used in parallel in 11g but not in 10g ;-) but of course I understand the answer and thanks -- I won't waiste time for searching not existing solutions. Followup   June 22, 2011 - 10am Central time zone: where did you read it is different in 11g? parallel select from remote site   June 24, 2011 - 2am Central time zone Bookmark | Bottom | Top Reviewer: umar from India Hi Tom, Can we use /*+ parallel (a,4) */ hint for accessing remote table in parallel. Though while fetching data over DB link, it would be serialized, but to extract data from remote DB can it run in parallel? will this hint be useful along with driving_site() hint? Followup   June 24, 2011 - 8am Central time zone: you can run the remote query in parallel - yes. driving site only works with READ queries. If you have an insert as select, it doesn't do anything, it doesn't work. Oracle does not support remote select from partition   March 19, 2012 - 4am Central time zone Bookmark | Bottom | Top Reviewer: Alexander from Russia Hi, Tom. You wrote " insert /*+ append */ into localtable partition( PNAME ) select * from remotetable@dblink partition(PNAME) " But Oracle does not support remote select from partition ORA-14100... Followup   March 19, 2012 - 10am Central time zone: You can easily use the partition key on that one - you don't need it to avoid the locking or anything. Or a remote view.