Skip to Main Content
  • Questions

  • char vs varchar2 when end result is filxed format value

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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.

We're not taking comments currently, so please try again later if you want to add a comment.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library