RE: RE: flag

Stephane,

You forgot to finish your message with :-)

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Faroult
Sent: Thursday, May 22, 2003 12:37 PM
To: Multiple recipients of list ORACLE-L

  1 select COLUMN_NAME, DATA_LENGTH
  2 from DBA_TAB_COLUMNS
  3 where TABLE_NAME='DBA_IND_COLUMNS'
  4* and COLUMN_NAME = 'COLUMN_NAME'
SQL> /

COLUMN_NAME                    DATA_LENGTH
------------------------------ -----------
COLUMN_NAME                           4000

So it looks like no matter what the column name looks like, the column_name is always 4000 characters long.

SF

>----- ------- Original Message ------- -----
>From: "gmei" <gmei_at_incyte.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 22 May 2003 08:11:53
>
>SQL> select * from v$version;
>
>BANNER
>Oracle8i Enterprise Edition Release 8.1.7.3.0 -
>Production
>PL/SQL Release 8.1.7.3.0 - Production
>CORE 8.1.7.0.0 Production
>TNS for Solaris: Version 8.1.7.3.0 - Production
>NLSRTL Version 3.4.1.0.0 - Production
>
>SQL> create table t1(f1 char(1), f2 varchar2(1), f3
>number, f4 number(1));
>
>Table created.
>
>SQL> select COLUMN_NAME,DATA_LENGTH from cols where
>TABLE_NAME='T1';
>
>COLUMN_NAME DATA_LENGTH
>------------------------------ -----------
>F1 1
>F2 1
>F3 22
>F4 22
>
>
>So it looks like no matter what precision one
>defines for NUMBER column, the
>data_length is always 22. So char or varchar would
>use less space for column
>data (not index data).
>
>Guang
>
>> -----Original Message-----
>> From: root_at_fatcity.com
>[mailto:root_at_fatcity.com]On Behalf Of
>> Orr, Steve
>> Sent: Thursday, May 22, 2003 11:07 AM
>> To: Multiple recipients of list ORACLE-L
>> Subject: RE: flag
>>
>>
>> Y/Null instead of Y/N worked really well for us.
>We had a
>> multi-million row table that was often queried
>with the flag
>> value and only returned a small number of rows
>based on the
>> flag. An index was needed on the flag but if all
>rows
>> contained a 'Y' or an 'N' then the index required
>a lot of
>> overhead and quickly became stale and needed
>rebuilding. By
>> changing the data to Y/Null instead of Y/N and
>rewriting the
>> code just a little we had a much smaller index
>and
>> performance didn't degrade. Using NULL for
>boolean operations
>> works well because NULL is not nothin' ya know.
>>
>> - Steve
>>
>>
>> -----Original Message-----
>> Sent: Wednesday, May 21, 2003 9:07 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> I liked the idea of using 0/1 for Java Boolean
>operations
>> but on the other side of the argument is that we
>inadvertently
>> did a datatype transformation and ended up not
>using the
>> index (Oracle implicitly converted the data side
>to number
>> and failed to use the index, where we compared
>against
>> a field defined, based on a table and the table
>had been
>> created with wrong data type AAAaaargh.
>>
>> I much prefer Y/N. However because many of our
>> applications (for Fed Govt) need to be bilingual,
>
>> 1/0 is used (rather than worry about storing
>Oui/Non)
>>
>> - Babette
>> -----Original Message-----
>> Sent: Wednesday, May 21, 2003 8:28 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> If it's a Y/N flag (boolean) that needs to be
>indexed then
>> you can just make
>> it Y/NULL and handle it via code. This can
>substantially
>> reduce the size of
>> the index. (not talking about bitmap indexes now)
>
>>
>>
>>
>> -----Original Message-----
>> Sent: Wednesday, May 21, 2003 3:48 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> Are you planning to use the flagged column as a
>boolean across a jdbc
>> connection? If so, then go with a number
>datatype. The 9i
>> jdbc driver does
>> not know how to handle boolean datatype, thus if
>you store
>> 'Y' in a char
>> column the jdbc driver will throw errors when the
>java
>> application calls
>> GetBoolean against the jdbc driver.
>>
>> Mark
>>
>> -----Original Message-----
>> Sent: Wednesday, May 21, 2003 1:17 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> Would you use a number or char for a flag in a
>table? Would you use a
>> char since it's one byte and a number is 22
>bytes?
>>
>> Dave
>> --
>> Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>> --
>> Author: David Turner
>> INET: turner_at_tellme.com
>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).