SQL & PL/SQL

Hi All,

I have a DML like the following:

insert /*+ append*/ into table1
select *
from COMPLEX_VIEW a;

Here complex_view contains a very complicated SQL, in which there are some heavy table joins, subqueries and aggregations.

Question1:

Assume the underlying tables have no 'parallel' attribute. Where should I add 'parallel hint' to force it to be executed in parallel and can get best performance?

Some members think the following is good.

insert /*+ append*/ into table1
select /*+ parallel(a 4)*/*
from COMPLEX_VIEW a;

But I think the hints should be put into the defintion of the complex view where they should be put and don't put the hints to the main insert DML, like this:

insert /*+ append*/ into table1
select *
from COMPLEX_VIEW a; -- I have added the hints in the COMPLEX_VIEW.

What's your opinion?

Quesion2:
Without 'alter session enable parallel DML', I can notice the parallel session in v$px_session as well. And the execution time has been shortened. This shows without this statement, the DML is aslo executed in parallel.

So what's the effect of this statement?

Best regards,
Leon