Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2002
    Posts
    1

    Question Unanswered: Varchar2 for all table columns!

    I have just started work with a client who has a habit of defining all
    table columns (except dates and numbers) as VARCHAR2. Even 1 character
    columns are Varchar2!

    Does anyone have any references on performance/space issues with using
    this approach to database design?

    Thanks

    Wally Randall

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    I guess this client is doing the right thing.

    With VARCHAR2() the rest of the unused characters bits are not stored in the database, but with CHAR() they are padded with blank spaces.

    VARCHAR2() is more economical on disk space than CHAR() and you should use it always before CHAR().

    And here is the proof with some manuals snippets:

    CHAR Datatype

    The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes, not characters) between 1 and 2000 for the CHAR column width. The default is 1. Oracle then guarantees that:

    When you insert or update a row in the table, the value for the CHAR column has the fixed length.

    If you give a shorter value, then the value is blank-padded to the fixed length.

    If you give a longer value with trailing blanks, then blanks are trimmed from the value to the fixed length.

    If a value is too large, Oracle returns an error.



    VARCHAR2 Datatype

    The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes, not characters) between 1 and 4000 for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error.

    For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.

    Oracle compares VARCHAR2 values using nonpadded comparison semantics.


    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  3. #3
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24

    Varchar2 for all tble columns

    In my experience, I have found it to be important to declare fields that are known to be updated (i.e., have their length increased) after insert operations as CHAR rather than VARCHAR2 to avoid problems with chained rows. Space is cheap, so this optimization can be a real life safer.

    The other exception is for single character data. A VARCHAR2(1) takes more space than a CHAR(1) due to the length management information stored as part of a VARCHAR2 datatype.

    Other than these 2 exceptions, I always user VARCHAR2...

    Troy

  4. #4
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24

    Varchar2 for all tble columns

    In my experience, I have found it to be important to declare fields that are known to be updated (i.e., have their length increased) after insert operations as CHAR rather than VARCHAR2 to avoid problems with chained rows. Space is cheap, so this optimization can be a real life safer.

    The other exception is for single character data. A VARCHAR2(1) takes more space than a CHAR(1) due to the length management information stored as part of a VARCHAR2 datatype.

    Other than these 2 exceptions, I always user VARCHAR2...

    Troy

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Varchar2 for all tble columns

    Originally posted by tsclark
    In my experience, I have found it to be important to declare fields that are known to be updated (i.e., have their length increased) after insert operations as CHAR rather than VARCHAR2 to avoid problems with chained rows. Space is cheap, so this optimization can be a real life safer.

    The other exception is for single character data. A VARCHAR2(1) takes more space than a CHAR(1) due to the length management information stored as part of a VARCHAR2 datatype.

    Other than these 2 exceptions, I always user VARCHAR2...

    Troy
    I would question whether these exceptions are justified:

    Exception 1: is avoiding chained rows worth the horrendous problems with using CHAR to hold variable length data? I don't think so.

    Ecception 2: as you said, space is cheap! I see no problem with using CHAR(1) for a NOT NULL column only, but doubt whether its worth the bother.

    VARCHAR2 rules!!!

  6. #6
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    I believe you are far too quick to dismiss the value of avoiding chained rows can have on the performance of a high volume system.

    Also, concerning your statement "...the horrendous problems with using CHAR to hold variable length data", if this is your idea of a horrendous problem, then you have lead a very fortunate, albeit sheltered DBA life.

    That's the beauty of opinions and open forums. We can share our data and pick and choose that which works for us at any given point in our DBA journey.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by tsclark
    I believe you are far too quick to dismiss the value of avoiding chained rows can have on the performance of a high volume system.

    Also, concerning your statement "...the horrendous problems with using CHAR to hold variable length data", if this is your idea of a horrendous problem, then you have lead a very fortunate, albeit sheltered DBA life.

    That's the beauty of opinions and open forums. We can share our data and pick and choose that which works for us at any given point in our DBA journey.
    Actually, I am not a DBA at all, and must admit that I am not expert in the subject of row chaining and its impact on performance. However, I am an Oracle database designer with over 12 years experience, and have never come across this justification for the usage of CHAR for variable length strings before. Would the benefits outway the degradation of performance due to larger table and index sizes?

    The problems I refer to do not affect DBAs in general, they affect users (including developers). If I create a table EMP ( firstname CHAR(20), lastname CHAR(20) ), then my record in this table would be ('Tony................','Andrews.............') - I have used '.' to represent a space. This means that:

    1) to display 'Tony Andrews' I will have to select RTRIM(first_name)||' '||RTRIM(last_name) instead of first_name||' '||last_name

    2) The following select will raise NO_DATA_FOUND:

    DECLARE
    v_firstname VARCHAR2(20);
    v_lastname VARCHAR2(20) := 'Andrews';
    BEGIN
    SELECT firstname INTO v_firstname
    FROM emp
    WHERE lastname = v_lastname;
    END;

    OK, I should have declared the variables as CHAR(20) or emp.xxxname%TYPE to match the table - but since VARCHAR2 is the norm for most users, this is a bug waiting to happen; especially if you use a mixture of CHAR and VARCHAR2 columns in your database.

    However, in the light of your comments I intend to educate myself on the subject of row chaining!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I found this relevant answer on AskTom:
    **********
    1) a CHAR is simply a a VARCHAR2 that is stored blank padded. A char is stored
    with a leading byte field -- just like a varchar2. It is in fact a varchar2
    that is always the maximum length (or null).

    If all of the data in the table would eventually consume 100 bytes (eg: i insert
    5 bytes, update it to 10, update it to 20, .... update it to near 100) it would
    be more efficient to use a char(100) as it preallocated the space and rows will
    not migrate over time due to updates. If the rows are truly varying length
    (normal case) a varchar2 is appropriate.
    ***********

  9. #9
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Hi I'm not an Oracle DBA, I'm a SQLServer DBA, however I have to interface with Oracle at my site. I do design the databases as well as develop and maintain them. In SQLServer there are 2 types of UPDATEs, Deffered and Update Inplace, these are handled internally be SQLServer. Deffered, is actually a delete and then an insert, where as an Update inplace is a straight replace of the record. The second, Update Inplace is the preferred way. To achieve this your record must be of fixed length, make sense, you have a space of X and you can only fit X into that space not X+1. So when it comes to designing if I know that SIN or SSN for Americans is 9 characters I declare it as CHAR(9), same as Health Cards, phone numbers.

    If I can achieve a fixed length record I will do it, however I'm not going to declare all fields as CHAR, address thata are 100 characters are ging to be VARCHAR2.

    Just a question to Andrew, after you have designed a database to you go back and review performance?
    MCDBA

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by achorozy
    Just a question to Andrew, after you have designed a database to you go back and review performance?
    You mean me? I'm Tony!

    I believe that if you do your design work properly you will not have many performance issues coming up later, but there will always be some. You should find these through realistic system testing, i.e. with realistic data volumes - and if possible, realistic number of users (more difficult!) It is then a case of using trace tools to identify the bottlenecks and attacking the biggest ones first.

    Then of course in production things won't always go as you had planned, and you have to tune again. For example, on one project we had a batch process that ran in less than half a minute for all users, using an index - until a new business unit that came on to the system whose data was so skewed that it took over an hour to process. A quick fix was found: ignore the index, then ALL batch jobs ran in about 3 minutes using a full table scan, which was preferable. We could then come up with a better solution at our leisure.

Posting Permissions

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