Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

If does, how to avoid it?


asked 07 Jun '16, 14:41

pLee's gravatar image

accept rate: 0%

You will find SQL Anywhwere tries to save you storage space whenever possible. Unlike some other database technologies, with SQL Anywhere, LOBs will not allocate extra 'blob' pages unless required. There are a few layers to this.

The simplest case is the Null value case which Mark has covered already. I.E. NO extra space.

The 2nd layer, there is a space savings for short LOBs if they are less than the INLINE size, they are store inside your row and will not use blob pages. This can save you lots of space (in comparison to some other implementations) if most of your LOBS are small. This is true of you zero-length (ie. empty) LOB case; we need to store the length byte for those and that's it.

And there are other aspects of LOB storage you might want to also be aware of.

permanent link

answered 07 Jun '16, 15:22

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 32%

Thank you for the answer! What if alter long binary column from not null to null, Does it cost any space in database?

(07 Jun '16, 15:23) pLee
Replies hidden

I believe you are only concerned about the larger LOBs here ... those that need their own extra page allocations outside the row ... Such pages (as with any page inside the database) once allocated will remain a part of the database but will be freed up for reuse or other purposes as appropriate.

We will try to associate a cluster of pages together for a specific object (to help for I/O purposes) but freed pages are reused in many different ways inside the database.

Are you seeing a specific issue with blobs in your usage?

(07 Jun '16, 15:37) Nick Elson S...

It added a lot spaces to database. How to free up them?

(07 Jun '16, 15:44) pLee

It depends on what you mean by "free up". Here is a simplistic explanation:

Deleted data becomes "free space" that is available for inserts.

The database server will not shrink the database file.

A "full reorganization" can be used to effectively shrink the database: dbunload everything, delete the .db file, dbinit a new .db file, dbisql to reload the data. The dbunload utility can be configured to do all four steps automatically.

(07 Jun '16, 21:04) Breck Carter

A NULL column of any type only takes up 1 bit in the row header.

permanent link

answered 07 Jun '16, 14:54

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 07 Jun '16, 14:41

question was seen: 2,193 times

last updated: 07 Jun '16, 21:04