Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: varchar2 conversation

    I am from the mainframe COBOL world. I had a conversation with a Java/Oracle programmer regarding VARCHAR2 and CHAR.

    In my Mainframe DB2 world we would commonly define columns like FIRST_NAME as CHAR(30). He suggests that it is more common and correct in the Java/Oracle world to construct the column as
    VARCHAR2(30).

    I recognize the difference between how Java and COBOL handle strings. Is my friend correct in the Java/Oracle world. Are most of your string type columns defined as VARCHAR2? And are you then simply maximizing the column size to 255?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    YES!

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    Would you be tempted to code the FIRST_NAME column as VARCHAR2(30).

    My thinking is that it makes sense... especially if I want to limit the size of First_NAME.

  4. #4
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Originally posted by taa_sarge
    Would you be tempted to code the FIRST_NAME column as VARCHAR2(30).

    My thinking is that it makes sense... especially if I want to limit the size of First_NAME.
    First_name char(30) or varchar2(30).
    It's up to you. How many people will exceed this limit of 30 characters in their first name? How important is it to store the entire first name?
    If you have an abundance of storage space use char(30).
    They will both use the same amount of space for names 30 characters long.
    However, if you use varchar2(30) and the name is only 4 characters long Oracle will only latch onto enough space for those 4 characters.
    char(30) will use 30 characters of storage space regardless!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Would you be tempted to code the FIRST_NAME column as VARCHAR2(30).
    YES!
    The company for which I work runs off of inhouse written s/w using Oracle Forms, Reports & 800+ DB packages.
    AFAIK, there is not a single column of type CHAR in the whole application
    All the strings are either VARCHAR2 or CLOBs.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    VARCHAR2 is the datatype of choice and unless you fully understand the implications of CHAR vs VARCHAR2 datatypes you should stick with VARCHAR2.

    CHAR datatypes use a fixed storage space per row/column, therefore row chaining tends not to occur. Oracle can always overwrite the existing value with a new value in the existing allocated storage.

    With a VARCHAR2 column Oracle usually uses less space per row but with a column which is frequently updated, row chaining can occur. The update does not check whether the new value can fit in the already allocated space for the previous value. Instead it tags in onto the end of the row storage. This can result in rows being chained over multiple data blocks on disk, which in turn leads to performance degradation.

    The simple answer is yes, use VARCHAR2, the more complex answer is read up on row chaining.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Nov 2002
    Posts
    833
    and there is a difference in behabior with the like opperator

    select rawtohex(usr) from users where usr like 'PATCH%';

    RAWTOHEX(USR)
    ------------------
    504154434820202020
    1 row selected.

    SQLWKS> select rawtohex(usr) from users where usr like 'PATCH%';


    RAWTOHEX(USR)
    ------------------
    5041544348
    1 row selected.

  8. #8
    Join Date
    Nov 2002
    Posts
    833
    and there is a difference in behabior with the like opperator vs. =

    select rawtohex(usr) from users where usr like 'PATCH%';

    RAWTOHEX(USR)
    ------------------
    504154434820202020
    1 row selected.

    SQLWKS> select rawtohex(usr) from users where usr like 'PATCH%';


    RAWTOHEX(USR)
    ------------------
    5041544348
    1 row selected.

  9. #9
    Join Date
    Feb 2004
    Posts
    5
    Thanks guys. I did some reading as you suggest and it seems that the problem of row chaining and fragmentation in Oracle is the same type of problem with DB2. The solution for Oracle and DB2 is to REORG when necessary.

    I will experiment with queries using LIKE%. Can anyone explain why this occurs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •