5 Replies Latest reply: Jun 28, 2012 1:08 PM by rp0428 RSS

    Using varchar

    Rajan.Panchal
      I have a query - if I set some column as varchar2(500) and currently I use only 50 characters long string to store into this column then does it affect in any way the performance of database?
        • 1. Re: Using varchar
          Billy~Verreynne
          Even if you use all 500 characters and not 50, there could be no performance impact (as the entire datablock containing that row needs to be read).

          So no - performance is not related to using varchar2(500) to store 50 byte strings. It is a lot more involved than that.
          • 2. Re: Using varchar
            Rajan.Panchal
            What else is involved then?
            • 3. Re: Using varchar
              Paulie
              RP wrote:
              What else is involved then?
              Hi,

              Nothing. The system will take care of it for you. The whole point about
              VARCHAR2 is that whether you use 1 character or the whole 500 it
              makes no difference.

              Those who write the Oracle kernel go to a lot of trouble to make sure
              that you don't have to.

              Feel free to use your VARCHAR2 to your heart's content any way
              you please - EXCEPT, please don't store DATEs in it - that's far and
              away the most common problem with VARCHAR2 - not the amount
              of characters you have in it.

              HTH,

              Paul...
              • 4. Re: Using varchar
                HuaMin Chen
                RP wrote:
                I have a query - if I set some column as varchar2(500) and currently I use only 50 characters long string to store into this column then does it affect in any way the performance of database?
                Using varchar is OK; using char can be a problem.

                VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.

                Thanks
                and we said...
                hah, up to 50% faster... Hmm, fascinating. I wonder where they "made up" that number from.

                Since a char is nothing more than a VARCHAR2 that is blank padded out to the maximum length - that is, the difference between the columns X and Y below:

                create table t ( x varchar2(30), y char(30) );
                insert into t (x,y) values ( rpad('a',' ',30), 'a' );

                IS ABSOLUTELY NOTHING, and given that the difference between columns X and Y below:

                insert into t (x,y) values ('a','a')

                is that X consumes 3 bytes (null indicator, leading byte length, 1 byte for 'a') and Y consumes 32 bytes (null indicator, leading byte length, 30 bytes for 'a ' )


                Umm, varchar2 is going to be somewhat "at an advantage performance wise". It helps us NOT AT ALL that char(30) is always 30 bytes - to us, it is simply a varchar2 that is blank padded out to the maximum length. It helps us in processing - ZERO, zilch, zippo.



                Anytime you see anyone say "it is up to 50% faster", and that is it - no example, no science, no facts, no story to back it up - just laugh out loud at them and keep on moving along.


                There are other "made up things" on that page as well, for example:

                Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string.
                Whereas in VARCHAR the system has to first find the end of string and then go for searching.

                FALSE: a char is just a varchar2 blank padded - we do not store strings "at a specified position from each other". We do search for the end of the string - we use a leading byte length to figure things out.

                Edited by: HuaMin Chen on Jun 28, 2012 5:20 PM
                • 5. Re: Using varchar
                  rp0428
                  Once again it appears that you have plagiaraized part of a blog by Tom Kyte and have not cited him as the source.
                  This appears to be an exact copy of his reply in this AskTom blog
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

                  What you have posted IS NOT your work or contribution.

                  You were asked to stop doing this in one of your previous threads where I said this
                  Re: question about rownum
                  >
                  This response appears to plagiarize Tom Kyte's material as it seems to be a copy of this article
                  http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

                  While it is ok to use excerpts from copyrighted material in answering questions you always need to provide the source of the material and attribute it to the author.

                  As far as I can tell every word in your reply came from Tom's article.

                  Please edit your post to indicate the true source of the material you posted and do the same in all future replies.
                  >
                  Tom Kyte himself, in the same post ask you not to do this
                  >
                  next time, please just post a link to the article instead of copying bits of someone else's work out of context..
                  There is nothing wrong with providing brief excerpts from copyrighted material as long as you indicate the author and source of the material. And, as Tom suggested, post a link to the article so people know where the information came from.

                  Please stop using others peoples work without attributing the comments posted to the original author!