INSERT..SELECT Parallel DML
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: INSERT..SELECT Parallel DML

  1. #1
    Join Date
    Oct 2001
    Posts
    122
    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.

  2. #2
    Join Date
    Nov 2001
    Posts
    4
    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

  3. #3
    Join Date
    Oct 2001
    Posts
    122
    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.

  4. #4
    Join Date
    Aug 2001
    Posts
    111
    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
    Performance... Push the envelope!

  5. #5
    Join Date
    Oct 2001
    Posts
    122
    Were are the gurus? Any feedback?

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    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.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width