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.

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?

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...

Hi tom,

I have problems with a insert..select and remote table
...like this

insert into table1_big
select/*+ PARALLEL(8) */
campo1, campo2
from table2_big@dblink;

when i saw the execution, this query do not show me the parallel sesions

when put this query in the local db...like this:

insert into table1_big
select/*+ PARALLEL(8) */
campo1, campo2
from table2_big--- not need the dblink is in the local table

in this case, it show me the parrallel sesion..

what is wrong?? what is the problem??

the table are very big and it have partition

hi again Tom

i have a process like this:

insert/*+ append parallel(15)*/ all
into big_partition_table1
into big_partition_table2
into big_partition_table3
select /*+ parallel(15)*/
* /*...( several fields)*/
from big_tabla_locally a
left join small_tabla1 locally b
on ( a.key = b.key)
left join small_tabla2 locally c
on ( a.key = c.key)
left join small_tabla3 locally d
on ( a.key = d.key)
..
left join small_tabla16 locally x
on ( a.key = x.key)
where a.particion_fiel = p_parameter

this big_tabla_locally load 40 million rows every day,
i am working on a super server, but recently this process took an hour, now takes 10 hours and it never ends

what is the problem? helpme please..

I am doing merge on a table across dblink
the table is huge 15 million
basel_data is accessed across a dblink while Account_Product_History is a local table and is 3 million records.
when i add a parallel hint ,
/*+ PARALLEL(b, 8) */
it gives error. what is going wrong here?
whereas the same thing works when all the tables are local, without a dblink!
i am using oracle 10g



MERGE /*+ PARALLEL(b, 8) */
INTO basel_data b
USING (
with xxx as
(select bd.*,
case when accph_act_code_orig ='CONV' then NULL
when accph_act_code_orig is NULL then bd_act_code
else accph_act_code_orig
end accph_act_code_orig
,first_value(bd_book_source) over(PARTITION by bd_account_no order by bd_period desc) as derived_book_source
,first_value(bd_book_code) over(PARTITION by bd_account_no order by bd_period desc) as derived_book_code
from basel_data bd
,(select distinct accph_account_no accph_account_no
,first_value(Accph_Act_Code_from)
over (partition by accph_account_no order by Accph_Transfer_From_Date, Accph_Subacc_No ) as accph_act_code_orig
from Account_Product_History ) qry
where qry.accph_account_no(+)=bd_account_no
)
select
xxx.bd_account_no, xxx.bd_period,
xxx.derived_book_source, xxx.derived_book_code,
--xxx.*,
NVL(ps1.prst_scheme_type,'STAN') prst_scheme_type_orig,
ps2.prst_scheme_type prst_scheme_type_curr,
case when (ps1.prst_scheme_type is null and accph_act_code_orig is null)
then Null
else NVL(px1.pacx_product_code,px2.pacx_product_code)
end pacx_product_code_orig,
px2.pacx_product_code pacx_product_code_curr
from xxx
,product_act_code_xref px1
,product_stype_availability ps1
,product_act_code_xref px2
,product_stype_availability ps2
where px1.pacx_act_code(+) = xxx.accph_act_code_orig
and ps1.prst_pr_product_code(+) = px1.pacx_product_code
and px2.pacx_act_code(+) = xxx.bd_act_code ----second join
and ps2.prst_pr_product_code(+) = px2.pacx_product_code
) e
ON (b.bd_account_no = e.bd_account_no and b.bd_period=e.bd_period )
WHEN MATCHED THEN
UPDATE SET
b.bd_prst_scheme_type_orig =e.prst_scheme_type_orig ,
b.bd_prst_scheme_type_curr =e.prst_scheme_type_curr ,
b.bd_pacx_product_code_orig =e.pacx_product_code_orig ,
b.bd_pacx_product_code_curr =e.pacx_product_code_curr
, b.bd_book_code =e.derived_book_code
, b.bd_book_source =e.derived_book_source;

Good input. Thanks Tom.