Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!cs.utexas.edu!samsung!brutus.cs.uiuc.edu!apple!oracle!news From: dsimson@oracle.com (David Simson) Newsgroups: comp.databases Subject: Re: variable length records in Oracle Message-ID: Date: 2 Feb 90 16:13:14 GMT References: <1990Feb1.165256.20379@oracle.com> <16486@boulder.Colorado.EDU> Sender: news@oracle.com Organization: Oracle Corperation, Belmont, CA Lines: 50 In-reply-to: deac@boulder.Colorado.EDU's message of 2 Feb 90 05:44:14 GMT > My question is what happens if a variable length record is updated > to increase the length of data in a field? Whether variable length records > are implemented by either white space compression or and extra byte > specifying the length of the field, there is no extra space on the > disk image to accomodate increasing the data in the field. > > So if "Don" is changed to "Donn", what happens to the disk file? The topic of how space in the Oracle disk files is maintained is comlplex; there are a couple of chapters in the DBA guide explaining it and how to tune the parameters optimally. Also, I'm not in the group that implements the kernel (database), rather in reporting tools. But I'll try to give as simple an explanation as I can... First off, Oracle uses pre-allocated database files. You can add disk files and spread a single database across many files on different disks, etc. Each file is subdivided into blocks, and in most cases, a block will contain records from only one table; the exception being "clustered" tables, where records from different tables sharing key values are stored in the same block. The database blocks are not tightly packed with data however. Each block has a block header which contains info about, among other things, which rows are stored in it. The rest of the block contains the data for those rows and a certain amount of free space. Rows with many columns can span multiple blocks. When a table is created, the creator can parameterize the block storage ratios specifying percentages above which no more rows should be added to the block and below which new rows can be added. The values of these parameters can be set for optimal space management based on the expected use of the table, and can be adjusted at any time. At any rate, what this means is that when a column's value length changes, in most cases there will be enough space within the block to fit it. If there isn't, either the whole row will be moved to a new block or the row will be split between multiple blocks, depending on the size and number of columns in the table. Again, this is a fairly simplistic explanation of how it's done, and I haven't touched on how database blocks themselves are managed. I really don't think I could do that justice. Perhaps if people are interested someone else here will help me out. Dave Simson dsimson@oracle.com P.S. I said in an earlier post that numbers are stored in a fixed-width format. That wasn't quite true. Numbers take up between 2 and 22 bytes on disk, depending on how big the value is. The largest numeric value that can be stored is 9.99 x 10^99.