Re: Index Creation Question - Which is More Efficient?

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1154295122.335117_at_bubbleator.drizzle.com...
> Acme Acmeson wrote:
>>
>> 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>
>
> If you write a lousy insert statement the solution is to fix
> the insert statement. What you have here is a waste of CPU.
>
> INSERT INTO emp (empno, ename) VALUES (9999, TRIM(' acme '));
>
> You can only insert a record one time.
> You can query is numerous times.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

You obviously didn't get the point.
I was merely trying to demonstate to the OP that leading and trailing spaces are stored in varchar2 fields.
It is as simple as that.

<Dereck L. Dietz>
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.
</Dereck L. Dietz>

Keld Nielsen. Received on Sun Jul 30 2006 - 21:51:55 CDT