danderso@oracle.com (David E. Anderson) (02/02/90)
In article <Jan.19.11.33.05.1990.17070@presto.IG.COM>, yang@presto.IG.COM (Young-Mee Yang) writes: >How about others, Oracle, Sybase, Ingres? Do they allow variable >record length? In what I consider an accurate response, but in hindsight, a risky light-hearted play on the 7UP tv commercial, I responded with my very first net message: <Subject: Oracle supports variable-length records < The subject says it all.. Always had it, Always will then:Small Flame Thrower On: jkrueger@dgis.dtic.dla.mil (Jon) > Oh, good. Please show me your SQL statement that creates a table >|with a text column without a specified size. and: dsimpson@oracle (Dave) provides short explanation of oracle data storage and Larger Flame Thrower On: jkrueger@dgis.dtic.dla.mil (Jon) >Thanks for the clarification. As we see, Oracle has never had it, and >as far as we know, never will. >"It" is variable record length, not trailing whitespace compression. >The original article never asked about the latter, >the question was simply which databases support the former. >If you must put your marketing claims on the net, please limit >yourself to accurate information that answers the question put. >Thank you. >-- Jon dsimpson@oracle.com then responded with a detailed and very accurate explanation of how oracle handles size declarations and data storage. I would like to add a few comments: 1) In posting my original message, I made a mistake which caused Mary Winslow's name to appear in the header as well as my own. She is not involved in this discussion, she is the Vice-President of my division :-) In Jon's followup Mary's name appeared rather than mine; my apologies for the confusion. 2) I think a mountain is being made out of a molehill. Let's see if I can be more explicit. I am a member of the Oracle kernel technical support group. I answer customer questions every day. In the few years I have done this, users who have asked about data storage have always referred to fixed-length and variable-length records as those which pre-allocate space according to the data definition and those which dynamically allocate space as a function of actual data size. I do not think it is unreasonable or surprising to use the terms this way and I certainly do not think that it calls for impuning my motives or asking for my silence. Using these definitions, Oracle clearly supports variable-length records, not a radical claim (or for that matter, a very commercially competitive claim, as I assume that most of our competitors provide the same functionality). I would like to know what definitions are used by Jon and, more importantly, the original poster... 3) To get on the right track, I suggest we discuss how software works and how we want it to work, which is what the original poster wanted to know. Dave Simpson has already described how Oracle works. I would add only that the user-defined maximum data size for a column can be changed at a later date using the ALTER TABLE MODIFY command. I would also like to take this opportunity to thank those of you who carried on the interesting threads centered on clients/servers/tp-monitors and oodbs over the last few months. David My original message was short because I thought this was a straight-forward, unambigous topic; live and learn, especially about the dangers of humorous/provocative wording!
deac@boulder.Colorado.EDU (Deac Lancaster) (02/02/90)
David, Thanks for your explanation of your original response. :-)). 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? Thanks, Deac
dsimson@oracle.com (David Simson) (02/03/90)
> 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.