This content has been marked as final. Show 5 replies
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.
What else is involved then?
What else is involved then?
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.
RP wrote:Using varchar is OK; using char can be a problem.
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?
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.
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
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
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
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..
Please stop using others peoples work without attributing the comments posted to the original author!