Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14

    Unanswered: Direct-path insert

    Hello,

    I have read about the benefits of the direct-path insert technique in Oracle 9i. But, the disadvantage is that the direct-path INSERT requires more space than conventional-path INSERT, because direct-path INSERT does not use existing space in the free lists of the segment.

    1. Are there solutions in order to avoid this "more space requirement"? For example, after direct-path inserting operation, to perform some operations those have as effect to collapse the space available for the table, to make the space on the table contiguous? In this way, online I can perform a direct-path insert to see more quickly the rows inserted, and afterwards, let’s say offline to move the blocks such that to have a contiguous usage space?

    2. Do you have any idea why the Oracle team has designed the direct-path INSERT operation that supports only the subquery syntax of the INSERT statement, not the VALUES clause?

    Thank you and best regards,
    Adrian

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Direct-path insert

    1. Why bother? You will have no more wasted space after the direct path insert than you had before it, more or less. I suppose export/import would do it though.

    2. Because each INSERT ... VALUES statement is performed separately, and so would use a new block, resulting in 1 row per block and a lot of wasted space!

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14

    Re: Direct-path insert

    1.A. Ok Tony, you are right. I will have the same wasted space after the direct-path insert as before. But, let's suppose that I want to move from the classic insert operation to direct-path insert. It could be possible to have some wasted space because I had used INSERT ... VALUES statement and I want to reduce it at the minimum before going to direct-path insert. What are the actions that should I perform to do not have wasted space for the table X?

    1.B. What it's happening if I am using the direct-path insert operation and afterwards the delete statement on other rows than inserted and then direct-path insert ...? Will be or not created a wasted space?

    Thank you and best regards,
    Adrian

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Direct-path insert

    1.A. AFAIK, you would have to export the data to a file, truncate the table and re-import it. But don't take my word for that, I'm not a DBA!

    1.B. Sure. If the direct-path insert created records in blocks 3, 4 and 5 and you then delete records from blocks 1 and 2 (or indeed 3, 4 or 5) then you will leave a "hole" in that datablock, which may get filled by a later insert. Try not to think of it as "wasted" space, just space that hasn't been reused yet!

Posting Permissions

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