Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using oracle 11,

Although a lot was already written about blobs, there is something that is not still clear for me.

  1. I was told that by default blobs stored in row if the are < 3900. Does it means that for a single table, some blobs will be stored in row and some others won't?
  2. Is there a way to check whether they are stored in row or not?
  3. If I set ENABLE STORAGE IN ROW, does it means that whatever the size the blob has, it will be save within the same segment than the other data?

Oracle Doc says:

"When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the default)."

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

I got something finally,

After further reading the DOC and making some test:

  1. Yes, by default, depending on the size of the row, some blobs will be stored in row and other out of row.
  2. Yes, following this you should be able to determinate it:

    LOB values are stored out-of-line when any of the following situations apply:

    • If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage clause when you create the table.
    • If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.
    • If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.

    LOB values are stored inline when any of the following conditions apply:

    • When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the
      default).
    • When the LOB value is NULL (regardless of the LOB storage properties for the column).
  3. No, this is just the default option, when the blob is too big(arpox 3800) it will be stored out of row

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.