Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tom.

Asked: July 27, 2016 - 12:54 am UTC

Last updated: August 04, 2016 - 7:18 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello Tom,

I read your article https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593 (and would have replied there, but couldn't figure out how to do that) and was wondering if this MS SQL "feature" I read about in "Building a Scalable Data Warehouse with Data Vault 2.0" also occurs with Oracle (and thus is a difference between char and varchar2 that can make char better than varchar2 in the very limited circumstance where you're trying to ensure statements that similar to this performs optimally:
select * from Very_Wide_Link_Table WLT
left join T1 on WLT.T1_MD5_Hash = T1.T1_MD5_Hash -- the MD5_hash columns are CHAR(32 CHAR) or varchar2(32 char)
left join Tn on WLT.Tn_MD5_Hash = Tn.Tn_MD5_Hash
(+ lots more similar joins)

): "Columns that use a varchar datatype might be stored in text pages instead of the main data page under some circumstances. Microsoft SQL Server moves columns with variable length out of the data page if the row size grows over 8,060 bytes, because hash keys are used for joining, the join performance will greatly benefit from having the hash key in the data page. If the hash key is stored in the text page, it has to be de-referenced first. Columns using a fixed-length datatype are guaranteed to be included in the data page."

My DBA says the entire row's information (except for the large objects like LOBs and CLOBs) will always be stored together (in heap tables) and the entire row will be moved if it grows too big, so this doesn't apply to Oracle. Is he correct? Does the answer change if we were talking about Index Organized Tables?

Sincerely,
Tom

and Connor said...

Your DBA is right.

varchar2 and char are stored inline with the row.

There are exceptions

- in 12c, varchar2 can be up to 32k, and they will be treated similarly to LOBs when they get large (ie, they might be moved out of the row).

- if you update a varchar2 col to a larger value, the row might need to be migrated elsewhere simply because it doesnt fit

- if the table has more than 255 columns, then the latter columns might be linked piecewise across blocks

- for IOT's you can opt to nominate what happens to rows that get large, or which columns will not be stored inline with the row/key data

and other boundary conditions like that. But in the main, char/varchar2 are stored right alongside the other data for the row.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the answer Connor

Tom, July 27, 2016 - 2:51 pm UTC

Thanks, that's what I was looking for.

Is there a minimum # of characters before the varchar2 column is considered "large"? I assume in general the varchar2s will only be moved out when the column gets to be thousands of characters long.

"for IOT's you can opt to nominate what happens to rows that get large, or which columns will not be stored inline with the row/key data"
From what I've seen with IOTs, you can only nominate that "all columns after column x should be moved to the overflow segment..." does it change in 12c so you can nominate particular columns without regard to how they're ordered?
Chris Saxon
August 04, 2016 - 7:18 am UTC

It could be *any* number of characters.

For example, if you fill a block in a table defined as "pctfree 0", then the block has *no* room for any more data.

So I might update a 10character varchar2 to 12 character, and it will *still* need to move that row somewhere else.

You're right about the IOT's - that was my poor choice of language.

Thanks!

Tom, August 04, 2016 - 2:10 pm UTC

Thanks.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here