Will a timestamp value which is updated from initial NULL to a real value lead to a rewrite of the affected row? If the NULL timestamp value has the same size as a filled timestamp value I would expect, that SQL Anywhere can change the row in place.
SQL Anyhere represents each NULL column value as a single bit, and packs up to 8 of those bits into a single byte, in order to save space: http://dcx.sybase.com/1101en/dbreference_en11/nulls.html
"Column space utilization for NULL values is 1 bit per column and space is allocated in multiples of 8 bits. The NULL bit usage is fixed based on the number of columns in the table that allow NULL values."
Just another reason why I hate NULL values :)... if you replace a NULL value with a non-NULL value, the row grows in size. Row positioning is immutable: http://dcx.sybase.com/1101en/dbusage_en11/queryopt-sectb-5356438.html
"Once assigned a home position on a page, a row never moves from that page. If an update changes any of the values in the row so that it no longer fits in its assigned page, then the row splits and the extra information is inserted on another page. This characteristic deserves special attention, especially since SQL Anywhere allows no extra space when you insert the row. For example, suppose you insert a large number of empty rows into a table, then fill in the values, one column at a time, using UPDATE statements. The result would be that almost every value in a single row is stored on a separate page. To retrieve all the values from one row, the database server may need to read several disk pages. This simple operation would become extremely and unnecessarily slow. You should consider filling new rows with data at the time of insertion. Once inserted, they then have enough room for the data you expect them to hold."
The good news is, the run-time engine works real hard at avoiding row splits, and you can help by specifying PCTFREE. http://dcx.sybase.com/1101en/dbreference_en11/create-table-statement.html
answered 18 Mar '10, 09:46