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.

asked 18 Mar '10, 09:27

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

edited 15 Sep '11, 08:32

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


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

permanent link

answered 18 Mar '10, 09:46

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×31
×19
×7
×7

question asked: 18 Mar '10, 09:27

question was seen: 1,248 times

last updated: 15 Sep '11, 08:32