INSERT..SELECT Parallel DML

[prafful]

Hi Guys,

I have confusion about whether INSERT..SELECT can be run as parallel DML against 8.0.5.

I have read somewhere that Oracle doesnot run INSERT..SELECT in Parallel unless run as Direct Load with APPEND hint.

NOTE: Following exists before executing INSERT..SELECT
* Parallel DML is enabled.
* The table being inserted has Parallel degree pre-defined and/or the SELECT SQL has suppied with PARALLEL hints and/or the table being queried has Parallel degree pre-defined.
* Enough PQ Servers are available/running.
* Hardware supports Parallel DML.
* Instance has been started with enough various memory pockets.

Your feedback is greatly appreciated.

[veeramur]

Hi,

We able to insert and select both at same time against one table in 8.0.5.For example now I am in production environment,suppose I start any process I mean insert against one table I my porcess every 10,000 records I am giving commit,so I keep on wtaching how many records inserted or any problem(I mean select query).So very much possible to insert at the same select command against one table.We don't need to parallel option.

Thanks

[prafful]

Hello veeramur, I Think you haven't understood what I am asking !

What I am asking is, whether Oracle8 allows running INSERT...SELECT as a parallel DML.

[roobaron]

You can either do inserts via PARALLEL DML or DIRECT load via the APPEND HINT. But not both. THere is more info in the Concept section of the manual.

I moved a schema into a separate database (around 15 Gig) in around 1 hour using the APPEND hint. As we only have 3 CPUs and the databases were on the same machine there was no real time benefit running with the PARALLEL hint.

You might want to do some benchmarking on your system to see which method will be faster.

Have Fun

[prafful]

Were are the gurus? Any feedback?

[gpsingh]

NOTE: Following exists before executing INSERT..SELECT
* Parallel DML is enabled.
* The table being inserted has Parallel degree pre-defined and/or the SELECT SQL has suppied with PARALLEL hints and/or the table being queried has Parallel degree pre-defined.
* Enough PQ Servers are available/running.
* Hardware supports Parallel DML.
* Instance has been started with enough various memory pockets.


Apart from above , it has to be multi cpu box if that iswhat u mean by Hardware.

Parallel load anyway does a direct load and is above HWM of table.

The append hint just pushes the insert above the HWM of table.

So u did not need to give both hints together.