-
I'm trying to gain a fuller understanding of storage fragmentation issues at the table level. The basic concern here is: do frequent deletes and inserts of fresh data from/into a table cause fragmentation? Say for example I have a table which, at times, might contain 1 million rows, but typically contains about 100,000 rows. After I shrink the table data (not the allocated space) by deleting the last 900K rows, and then do an insert, where would the data start being appended? Right at the end of the 100K th row?
Thanks
Johnny D.
-
It depends upon the PCTUSED parameter setting. The data block will be added into free list as soon as the actual data reaches that level (PCTUSED %). So if you are sure that after 900K, all the rows are deleted, then those blocks will be available for new insertion of rows.
If you delete randomly, then it is not guranteed that those blocks will be available for new insert.
-
Your data goes into the datafile in blocks of "db_block_size" size. A block may contain more than one row. In addition, Oracle maintains a list of blocks that are free. Once you have moved your highwater mark by inserting 1M rows, your data can go in any of the blocks that are on the free list.
-
if an insert happens, and till it gets committed, is there an use of DBC at all? I know DBC is very much required for Updates and Selects to process the queries..
Thx.
