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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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@x.washington.edu> wrote in message
news:1086915921.985866@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/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>

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@x.washington.edu> wrote in message
> news:1086915921.985866@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/e...ad/oad_crs.asp
>>http://www.outreach.washington.edu/e...oa/aoa_crs.asp
>>damorgan@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.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

> 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.

Main different between char(x) and varchar2(x) is that
value inserted into char(x) has always length x (padded with spaces).

Secondly, char(x) could be soon skipped in new version of Oracle DB.
--
TomekB

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1086927757.540852@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@x.washington.edu> wrote in message
> > news:1086915921.985866@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/e...ad/oad_crs.asp
> >>http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> >>damorgan@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 --

> 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.
>

This is not a valid reason, but I use CHAR(1) in place of the non-existant
Boolean (no, I don't want to argue about the need for Boolean data), or
maybe for a flag. Just for ease when scanning designs and so on.

Note in-line

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Mark D Powell" <Mark.Powell@eds.com> wrote in message
news:2687bb95.0406110545.e66f62f@posting.google.co m...
>
> 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.
>

There are two different scenarios, though.

The speed of accessing a given amount of data, and
the estimated cardinality for the where clause that
is supposed to identify the data.

Representing numeric (or date) information as
character columns can cause the optimiser to
get wildly inaccurate estimates of cardinality
and therefore choose the wrong execution
path. (Which brings you back to the performance
question from a different direction).

Alan wrote:

>>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.
>>
>
>
> This is not a valid reason, but I use CHAR(1) in place of the non-existant
> Boolean (no, I don't want to argue about the need for Boolean data), or
> maybe for a flag. Just for ease when scanning designs and so on.

But do keep in mind that 0 and 1 would be more efficient for that purpose.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Note in-line.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1087071115.755274@yasure...
>
> > This is not a valid reason, but I use CHAR(1) in place of the
non-existant
> > Boolean (no, I don't want to argue about the need for Boolean data), or
> > maybe for a flag. Just for ease when scanning designs and so on.
>
It sounds like a perfectly valid reason to me.
Standards that do no harm and add clarity to
structure and code are always valid.

> But do keep in mind that 0 and 1 would be more efficient for that purpose.
>

Depends where you want the efficiency - from an Oracle
viewpoint,
0 is stored as 0x1 0x80
1 is stored as 0x2 0xC1 0x02
which is arguably less efficient than the character equivalent.