Re: What's this...varchar2 as fast as char?

Well, speaking of varchar2 vs. char and rtrim:

Does there happen to be any easy way to perform joins against two tables on a column which is char on one table, but varchar2 on the other?

I know about the "rtrim" on the char column. But we have a few thousand lines of C code to convert from Sybase to Oracle. We've already found it necessary to break the code a LOT more than we originally thought. Any more breakage, and we'll be faced with the decision of whether it would be easier to rewrite it from scratch (not enough time or personnel!)

I also know about altering the table with the char to make it varchar, but we have about 300 tables with a big mixture of chars, varchars, and sizes. (This system wasn't designed by database experts: it just sort of =evolved=. Hence, you get things like Pandas using a wristbone as an opposable "thumb".)

Further, we have a partner on this project who is reluctant to make that many changes to the schema. If it were just us, I'd put together a script and blast the whole shooting match in one day! Once you get the Oracle internal schemas figured out, it's not that hard to do.

What I was hoping was that there might be some configuration set-up that would make rtrim and/or schema changes unnecessary.

I know I can do an "alter session set nls_date_format = 'YYYY/MM/DD'" which at least changes the default date format to what we've been using. I'm hoping there might be something similar for the varchar problem.

BTW, we're using Oracle 7.3.

Any ideas would be appreciated! Thanks!

Lun Wing San (Oracle) wrote:
>
> Sean Emery wrote:
> >
> > Hello,
> >
> > Excuse me for bringing up a probably hashed and re-hashed argument, but
> > two oracle consultants informed us that we should probably never use a
> > char field because varchar2 fields are just as fast inside an Oracle DB
> > and you never have to rtrim() them.
>
> varchar2 is variable length. As a result, it is space saving. In effect, it
> will save the time in table scan as it scans less blocks. Moreover, it uses non-blank
> padded comparison semantics. Thus, it reduces some overhead in comparison.
>
> ---
> Name : Lun Wing San
> Title : Oracle Application Developer of Hong Kong Productivity Council
> Oracle Database Administrator and System Administrator of QRC
> Phone : (852)27885841
>
> This posting represents the personal opinions of the author. It is not the
> official opinion or policy of the author's employer. Warranty expired when you
> opened this article and I will not be responsible for its contents or use.
 

-- 
+----------------------+---------------------------------------------+
| Alvin Sylvain        | Your freedom of speech doesn't mean an      |
| alvin_at_c-square.com   | obligation to listen.  Mine, however, does! |
| PLEASE! NO JUNK E-MAIL!  NO E-MAIL SOLICITATIONS!!  NO SPAM!!      |
+--------------------------------------------------------------------+