Thanks for the question, John.
Asked: May 05, 2017 - 1:42 pm UTC
Last updated: May 06, 2017 - 2:06 am UTC
Version: 11g
Viewed 1000+ times
You Asked
We have a temporary table with about 500 columns that is used to generate a fixed format file (.txt). If we use all char fields, we can just build the data as
field1 || field2 || field3 ... field500
If we use varchar2 we have to rpad each field
rpad(field1,10,' ') || rpad(field2,50,' ') || rpad(field3,25,' ') ... rpad(field500,12,' ')
Our performance is horrible in this package. I haven't dug into the specifics but I'm assuming it is because of all of the rpads. Everything I read says to always use varchar2 instead of char but I'm wondering if this package is the exception and we would be better off using char instead to eliminate the overhead of the rpad for every field. This table will never be used for anything else so access of the rows in it is not a problem (selecting where field1 = 'Hello ' vs field1 = 'Hello').
and Connor said...
I never say "never use something" :-)
If CHAR was a "terrible" thing, we'd eventually desupport it and get rid of it.
It's just another tool in your toolbox, and if it suits you requirement, then by all means, use it.
One thing you should benchmark is:
CHAR fields + no rpad = more storage, less CPU to concatenate
versus
VACHAR2 + rpad = less storage, more CPU to concatenate
I'm not saying one is better than the other - I'm saying, benchmark the two and decide what works best for you.
Is this answer out of date? If it is, please let us know via a Comment