DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance

  1. #1
    Join Date
    Feb 2001
    Posts
    100
    Hi all,

    Oracle balances the B*-tree indexes after each INSERT statement or after each row is added to the table. Let say, if I have to add 1000 records to a table. If I generate 1000 INSERTs of 1 row Oracle will balance the tree after each insert. But If I have 1 INSERT (statement) of 1000 records does Oracle balance the tree once or 1000. I know that it will be 1000 parse time and execution instead of 1 parse time and execution. What's about indexes.


    Thanks in advance

  2. #2
    Join Date
    Feb 2001
    Posts
    100
    Any help?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How does oracle balances the B*tree? By index block splitting. This splitting is done only when it is needed (when no more space is available in an index block for the new index entry). So it is irrelevant if you do a row-by-row insert/update or a bulk insert/update in a single insert/update statement. Whenewer an indexed column is inserted/updated, an index entry must be created, no matter of what the scope of transaction is. So the ballancing mechanism is active all the time, as soon as table row entry is changed the corresponding index entry has to be changed.

    In an extreme example, let's say you are inserting 1000 new rows, either in row-by-row or a single insert statement. As a result, some new index blocks are created and some existing index blocks might be splitted. Now let's say you decide to rollback the entire transaction. As a result, the newly created table rows will dissapear, while the splitted index blocks will not be "glued" back to the old state.

    In short, as far as index ballancing mechanism is concerened, there is no difference between a row-by-row insert or a single bulk insert.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2001
    Posts
    100
    Thank you very much Jurij

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