This discussion is archived
11 Replies Latest reply: May 11, 2013 7:33 PM by Debbie_1004338a RSS

VARCHAR() Size and Performance?

Debbie_1004338a Newbie
Currently Being Moderated
Many moons ago, I took an Oracle class in college, and I seem to recall my instructor saying that you would get better performance if you choose a VARCHAR() that was certain multiples of whatever.

For example, that VARCHAR(64) would be better than VARCHAR(63) or VARCHAR(65).


Reminder: I am actually working in the MySQL world, and just here to steal knowledge from all of you brainy Oracle people!! :)


As far as I can tell from the MySQL Manual, a VARCHAR() variable - for standard character sets - just takes the String Length and adds 1 Byte.

So, "Debbie" would be 7 Bytes regardless of whether you used a VARCHAR(63), VARCHAR(64) or VARCHAR(65).

How does this work in the Oracle world?

It's probably a petty point in either MySQL or Oracle, but I'm trying to learn how to do things the right way!!

Thanks,


Debbie
  • 1. Re: VARCHAR() Size and Performance?
    sybrand_b Guru
    Currently Being Moderated
    The Oracle*reference* manual for your unknown version of Oracle, which you prior to asking this doc question, didn't consult, will indicate there are seperate length bytes, which aren't included in the length of the string.
    If you attempt to 'learn from the pros' by asking as much doc questions as possible: this constitute abuse of this forum.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    sybrand_b wrote:
    The Oracle*reference* manual for your unknown version of Oracle, which you prior to asking this doc question, didn't consult, will indicate there are seperate length bytes, which aren't included in the length of the string.
    And that doesn't state anything that I didn't say above, so why are you repeating things?

    It also doesn't answer my question...

    If you attempt to 'learn from the pros' by asking as much doc questions as possible: this constitute abuse of this forum.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
    Funny, no one else has had problems with me or questions I have asked in this forum.

    Any reason you are waving the abuse flag in your first response?


    Debbie
  • 3. Re: VARCHAR() Size and Performance?
    sybrand_b Guru
    Currently Being Moderated
    You seem to be developing into someone who wants to be a real pest in this forum: being too lazy to use online resources, you are violating the Forums Etiquette, you also didn't read, expecting people here enjoy answering your doc questions.
    I am on several forums for almost 20 years, and during those years, I have seen people like you become incredibly lazy, and I'm sick and tired of the likes of you.
    So, yes, I want you to behave as soon as possible, and maybe you will only listen to the impolite method. I'm sorry for that, but people like you need that.

    --------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: VARCHAR() Size and Performance?
    EdStevens Guru
    Currently Being Moderated
    Debbie_1004338a wrote:
    sybrand_b wrote:
    The Oracle*reference* manual for your unknown version of Oracle, which you prior to asking this doc question, didn't consult, will indicate there are seperate length bytes, which aren't included in the length of the string.
    And that doesn't state anything that I didn't say above, so why are you repeating things?

    It also doesn't answer my question...

    If you attempt to 'learn from the pros' by asking as much doc questions as possible: this constitute abuse of this forum.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
    Funny, no one else has had problems with me or questions I have asked in this forum.

    Any reason you are waving the abuse flag in your first response?


    Debbie
    Don't worry, Sybrand is well known in the Oracle community both for knowing his stuff, and for his prickly attitude. Hang around a while and you'll learn to just ignore him when needed. He's not going to single-handedly get you banned from the forum.
  • 5. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    EdStevens wrote:
    Don't worry, Sybrand is well known in the Oracle community both for knowing his stuff, and for his prickly attitude. Hang around a while and you'll learn to just ignore him when needed. He's not going to single-handedly get you banned from the forum.
    Thanks for being a voice of reason, Ed!

    Well, I won't get used to it, but I'll certainly ignore him. (Since he finds me so "painful", maybe he should learn to do the same...)

    Funny how someone who doesn't know me, can be so quick to label...

    Especially considering that I did go to the MySQL Manual before coming here. And since I know little about Oracle, I don't even know what manual or where to use...

    Regardless, I asked a reasonable question, and for those that are bored by it, maybe they (i.e. Sybrand) should move on...

    It boggles my mind how questions like mine get lumped into the "Urgent! I need an answer on this!" and "I have an assignment where..." and "Help me create a _____ that does ____"

    Oh well, Forums are havens for bitchers...


    Now, back on topic...

    Was there any truth in what that old instructor told me or not? (I sure didn't see anything in the MySQL manual, but then that doesn't mean anything "in the real world"...)

    Thanks,


    Debbie
  • 6. Re: VARCHAR() Size and Performance?
    Justin Cave Oracle ACE
    Currently Being Moderated
    In Oracle, there is no performance reason to define columns in multiples of some number. There is no performance difference between a VARCHAR2(63) and a VARCHAR2(64). Depending on the specific version of Oracle (this changes across versions), there may be marginal performance differences in PL/SQL (particularly assuming that you are using anchored types) where Oracle allocates memory for the variable differently. For "small" strings, Oracle will allocate enough memory for the maximum length of the string. For "large" strings, it won't allocate the maximum size initially, it will only do so when that becomes necessary. Which approach is actually more efficient will depend on your actual PL/SQL code. But this is, at best, a marginal difference that you would not normally care about.

    If you're actually working with MySQL, though, it seems likely that you'd be much better served asking this question in a MySQL forum. I have no idea whether MySQL is more efficient when VARCHAR columns are declared in multiples of 2. When you start talking about performance, you're dealing with lots of implementation details so you may well get a different answer in different databases.

    Justin
  • 7. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    Justin Cave wrote:
    In Oracle, there is no performance reason to define columns in multiples of some number. There is no performance difference between a VARCHAR2(63) and a VARCHAR2(64). Depending on the specific version of Oracle (this changes across versions), there may be marginal performance differences in PL/SQL (particularly assuming that you are using anchored types) where Oracle allocates memory for the variable differently. For "small" strings, Oracle will allocate enough memory for the maximum length of the string. For "large" strings, it won't allocate the maximum size initially, it will only do so when that becomes necessary. Which approach is actually more efficient will depend on your actual PL/SQL code. But this is, at best, a marginal difference that you would not normally care about.
    Okay

    If you're actually working with MySQL, though, it seems likely that you'd be much better served asking this question in a MySQL forum.
    Right, but I was just curious how Oracle handles things, particularly since the class I alluded to was an Oracle class, and the instructor worked as an Oracle DBA by day.

    I have no idea whether MySQL is more efficient when VARCHAR columns are declared in multiples of 2. When you start talking about performance, you're dealing with lots of implementation details so you may well get a different answer in different databases.

    Justin
    From what I have read, the choice doesn't affect how MySQL itself works, but it can possibly have an impact on performance in that if you choose VARCHAR(200) for a field that is usually 2-3 characters in size, MySQL will load the field in memory as a CHAR(200), so that could hurt you.

    Again, I was just curious if this makes a difference in the Oracle world.

    Thanks,


    Debbie
  • 8. Re: VARCHAR() Size and Performance?
    EdStevens Guru
    Currently Being Moderated
    Debbie_1004338a wrote:
    EdStevens wrote:
    Don't worry, Sybrand is well known in the Oracle community both for knowing his stuff, and for his prickly attitude. Hang around a while and you'll learn to just ignore him when needed. He's not going to single-handedly get you banned from the forum.
    Thanks for being a voice of reason, Ed!

    Well, I won't get used to it, but I'll certainly ignore him. (Since he finds me so "painful", maybe he should learn to do the same...)

    Funny how someone who doesn't know me, can be so quick to label...

    Especially considering that I did go to the MySQL Manual before coming here. And since I know little about Oracle, I don't even know what manual or where to use...

    Regardless, I asked a reasonable question, and for those that are bored by it, maybe they (i.e. Sybrand) should move on...

    It boggles my mind how questions like mine get lumped into the "Urgent! I need an answer on this!" and "I have an assignment where..." and "Help me create a _____ that does ____"

    Oh well, Forums are havens for bitchers...


    Now, back on topic...

    Was there any truth in what that old instructor told me or not? (I sure didn't see anything in the MySQL manual, but then that doesn't mean anything "in the real world"...)

    Thanks,


    Debbie
    The closer you get to the hardware, the more likely it is that you come across a situation that would need to consider how things line up on memory boundaries and thus be concerned about powers of 2. But in the case of either storage or CPU effiency for variable length columns in a variable length row, ... lacking some clear and convincing evidence coupled with an iron-clad technical explanation, I'd say the instructor was just passing along bad information. Wouldn't be the first time.
  • 9. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    EdStevens wrote:

    The closer you get to the hardware, the more likely it is that you come across a situation that would need to consider how things line up on memory boundaries and thus be concerned about powers of 2. But in the case of either storage or CPU effiency for variable length columns in a variable length row, ... lacking some clear and convincing evidence coupled with an iron-clad technical explanation, I'd say the instructor was just passing along bad information. Wouldn't be the first time.
    Ha ha. So true.

    Okay, so I won't lose any sleep over this issue. (I just figured now is a good time to ask while I'm building Tables for my new Data Model.)

    Thanks for the help guys!

    Sincerely,


    Debbie

    Edited by: Debbie_1004338a on May 11, 2013 7:29 PM
  • 10. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    Thanks for the help!!
  • 11. Re: VARCHAR() Size and Performance?
    Debbie_1004338a Newbie
    Currently Being Moderated
    Thanks for the help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points