Re: Index Creation Question - Which is More Efficient?

From: Acme Acmeson <nahdsl232734_at_nahvip.cybercity.dk>
Date: Sun, 30 Jul 2006 20:48:29 +0200
Message-ID: <44ccfefc$0$60784$157c6196@dreader1.cybercity.dk>

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:XY5zg.77408$Lm5.133_at_newssvr12.news.prodigy.com...
>
> "Matthias Hoys" <anti_at_spam.com> wrote in message
> news:44ccd3ef$0$1211$ba620e4c_at_news.skynet.be...
>>
>> "Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message
>> news:Eo3zg.183675$F_3.68439_at_newssvr29.news.prodigy.net...
>> >
>> > "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
>> > news:1154267985.140826.173580_at_m79g2000cwm.googlegroups.com...
>> >> Dereck L. Dietz wrote:
>> >> > Your question "How often would you see code like that in a well
> written
>> >> > application?" is the key to this. This code I'm reworking is NOT
>> >> > well-written at all. It was actually written by non-programmers who
>> >> > consistently TRIM VARCHAR2 fields before inserting into tables. My
>> > opinion
>> >> > is someone read about the TRIM function and it became their flavor
>> >> > of
>> > the
>> >> > day and they have definitely overused it (they even TRIM the
> person_id
>> > field
>> >> > which is NUMERIC). In the code I included there is really no reason
> to
>> > use
>> >> > TRIM at all.
>> >> >
>> >> > I'm rewriting processes ever so slowly and will be getting rid of
>> > indexes
>> >> > and code such as included above. I just wanted written confirmation
>> > from
>> >> > someone other than me to show in case someone there tries to fight
>> >> > me
>> >> > on
>> > my
>> >> > changes.
>> >> >
>> >> > Thanks.
>> >>
>> >> There is nothing wrong with performing a TRIM of VARCHAR2 fields when
>> >> inserting into the database tables, although this could very likely be
>> >> handled within the program rather than requiring the Oracle database
>> >> to
>> >> perform the function. However, applying TRIM to the table columns in
>> >> the WHERE clause is generally not a good idea, especially if the
>> >> column
>> >> values are already pre-trimmed when the data is inserted into the
>> >> table.
>> >>
>> >> Creating function based indexes on TRIM(MY_COLUMN) can help improve
>> >> select performance if TRIM(MY_COLUMN) is included in the WHERE clause,
>> >> but it is better to determine if TRIM(MY_COLUMN)= is required, or if
>> >> MY_COLUMN= will work just as well.
>> >>
>> >> Charles Hooper
>> >> PC Support Specialist
>> >> K&M Machine-Fabricating, Inc.
>> >>
>> >
>> > According to the documentation I have (Oracle 10g Administration I
>> > Study
>> > Guide, page 153), TRIM isn't needed when storing VARCHAR2 characters in
> a
>> > database since a VARCHAR type only stores the amount of data that is
>> > actually used. You're TRIMming stuff that isn't stored in the database
> in
>> > the first place. Conversely CHAR datatypes will always store the full
>> > size
>> > of the field, right padding with spaces as necessary. So TRIMming a
> CHAR
>> > character is useless since the data will be right-padded with spaces
> when
>> > stored anyways.
>> >
>> >
>>
>> Hmmm ... I think they mean when you do something like this :
>>
>> INSERT INTO MyTable (mystring) values (' string ');
>>
>> If you want to remove the white spaces before the INSERT operation, you
> need
>> to perform a TRIM on it, right ? But, like some other poster mentioned,
> it's
>> better to correct this in the application before the insert statement is
>> generated.
>>
>>
>
> No they meant the way Oracle stores datatypes. According to my
> documentation Oracle doesn't store trailing spaces for VARCHAR characters
> so
> TRIMming them before inserting is not needed. If a field is defined as
> VARCHAR2(100) and the value stored in it is "TEST" only 4 characters are
> stored. However, if the field is defined as CHAR(100) then even if the
> value stored is "TEST" the full 100 characters are stored with trailing
> spaces making up the difference.
>
>

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\determig>sqlplus /nolog

SQL*Plus: Release 10.1.0.4.0 - Production on Sun Jul 30 20:38:04 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/keld_at_db92
Connected.

SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production NLSRTL Version 10.1.0.4.0 - Production

SQL> desc emp;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> insert into emp (empno,ename) values(9999,' acme ');

1 row created.

SQL> commit;

Commit complete.

SQL> select '<'||ename||'>' from emp where empno=9999;

'<'||ENAME||

---

< acme >

SQL> Received on Sun Jul 30 2006 - 13:48:29 CDT