Re:whic one to choose

Thanks for the info about fragmentation. The n here is rather small in lenght like 5,10,15,25. For large n we are using varch2 like 100, 200
As for the nature of expansion, we do not know to be precise, but in general, the files will expand later rather than shrink. So for example if we have an region_code field, it is most likey to be of 7 charcaters but later it may have become 10 charcaters on a rare occassion. So we thought of using varchar2(10) instead of char(7). But if your logic of fragm is correct, then what about other type of fields which we normally set up, like an address field, I have always seen an address field as varchar2 never as char. So I think we cano not avoid frag, by definition varchar2 will likely to bring fragmentation. I would say row migrarion or chaining. But this all depends on pctfree
anyway please provide your feedback with explaiantion for me to better understand what do you mean by frag here?                                                                                             

                    dgoulet_at_vicr.                                                      
    
                    com                  To:     MShahid_at_SaudiFrenchBank.com.sa, 
Multiple  
                                         recipients of list ORACLE-L                   
    
                    11/07/2001           <ORACLE-L_at_fatcity.com>                        
    
                    06:03 PM             cc:                                           
    
                                         Subject:     Re:whic one to choose            
    
                                                                                       
    

I would say that "it depends". You've got the field defined as a char(n) [which
I really wish you had specified what 'n' was]. Now is this field initially left
null and then later updated? Or will it be initially filled with a very small
value like '01' and then later updated to a larger (I guarantee) value? If so
then specifying it as a char will pre-allocate the entire field size at the start thereby reducing the need to expand the filed at a later date. This can
help with fragmentation.

    Now on the other hand, if this field is rarely if ever updated after it's
initial creation, a varchar2 is the better choice.

Dick Goulet

____________________Reply Separator____________________
Author: MShahid_at_SaudiFrenchBank.com.sa
Date:       7/11/2001 1:25 AM

The definition of char and varchar2 is clear from docu that one is used for fixed length and the other for variable. We are reviewing one database desgin given to use from our developers in that they have chosen to use CHAR(n)
data types at many places, even though the field can have variable lenght data.
For example for a column as region_code it is set as char(5) not this value can be '01' , 'CEN','WEST' etc
would it be wise to make it varhar2(5).
The reason is that if you want to query the colum in a where clause (if you have chosen char(5)) then I think
you have post fix spaces unnecessarily right? (where region_code = '01 '
becuase region_code = '01' would return no rows.

1)
Any ones has any suggestions on how to choose char and varchar2 for columsn that are small in lenght like < 30 characters and unsire of its fixed or variable?

2)
Is there any implication if we convert all those small lenght columsn for which we are not sure of data-lenght from char to varchar2???any side affects in making varch2?

Regards

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: MShahid_at_SaudiFrenchBank.com.sa

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: 
  INET: MShahid_at_SaudiFrenchBank.com.sa

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).