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, lets 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?
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?
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!