Re: which column type char,varchar2 is better for index?

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1086927757.540852_at_yasure>...
> Jaguk Ku wrote:
>
> > This is not the school work, i just curious about it.
> > i can make table and index as you wish, and i might find out the
> > differences, and the index structure algorithm from oracle documents.
> > i think everyone can do this, what the newsgroup is for?
> > if you don't want to answer the question. just ignore it. don't post the
> > reply like this.
> >
> > Jaguk Ku
> >
> >
> > "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1086915921.985866_at_yasure...
> >
> >>Jaguk Ku wrote:
> >>
> >>
> >>>hi there,
> >>>
> >>>if there are two columns A and B, A is defined as char(10), B is defined
> >
> > as
> >
> >>>varchar2(10)
> >>>if when i create indexes for both of them. which one has better
> >
> > performance,
> >
> >>>and why?
> >>>
> >>>I think it's not much different, if the index is made with b-tree
> >
> > algorithm.
> >
> >>>Would anyone teach me what is different and which one is better and why?
> >>>
> >>>Thanks in advance,
> >>>
> >>>Jaguk Ku
> >>
> >>This is school work and your instructor expects you to build two
> >>tables, create a loop, and test this out and learn from what you
> >>observe.
> >>
> >>--
> >>Daniel Morgan
> >>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> >>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> >>damorgan_at_x.washington.edu
> >>(replace 'x' with a 'u' to reply)
>
> Well then let me say that this is exactly the type of exercise I
> give my students ... and I would suggest that you learn this the
> same way they do rather than asking the question.
>
> But as I doubt you will let me give you the answer ... there is not
> a single valid reason I can think of in Oracle to ever use a CHAR
> column. And performance is only a small part of the reason.
>
> Perhaps you can do some research and learn the rest of them.

I agree with Daniel on the use of varchar2 over char every time.

Daniel also has a good point on running your own test. The test of char vs varchar2 in an index should be fairly easy to construct and while you are at it you should try number verse varchar2.

You might want to try your char verse varchar2 test under two different runs: where both indexes are built on data that is exactly the specified size and where the data is of variable length within the specified char field length.

When you test number verse varchar2 you should find very little practical difference though numeric key access should prove to be a little faster. At least it did when an Oracle support analyst posted some test results a couple years back.

HTH -- Mark D Powell -- Received on Fri Jun 11 2004 - 08:45:16 CDT