performance difference between varchar2(20) and varchar2(2000)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: performance difference between varchar2(20) and varchar2(2000)

Hybrid View

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    performance difference between varchar2(20) and varchar2(2000)

    Hi Friends ,

    I have a fundamental question on how Oracle internally handles columns with varchar2(20) datatype and varchar2(2000) datatype .

    according to theory , oracle uses same amount of space in column with varchar2(20) and varchar2(2000) when identical data is entered.

    eg:

    How does test tables test_tab_A differ from test_tab_B

    create table test_tab_A
    ( name varchar2(20)
    place varchar2(20)
    thing varchar2(20)
    animal varchar2(20)
    .....
    ...... ) ;

    create table test_tab_B
    ( name varchar2(2000)
    place varchar2(2000)
    thing varchar2(2000)
    animal varchar2(2000)
    .....
    ...... ) ;


    could anybody please suggest,
    1) which is a better design practice ,
    2) are there any limitations in designing tables in test_tab_B type.
    3) how does Oracle internally handle varchar2(20) and varchar2(2000) column regarding performance.

    Thanks in advance .

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Hi,

    Did you notice any performance difference between these 2 tables populated with the same data?

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi Mike ,

    This is in initial design stage ,
    we are identifying pros and cons , and any known issues
    while designing table with bigger column widths varchar2(2000) or does this effect anything while loading this table into buffer pool.

    also design people doesn't know how much column width should be .

    so there question was can we have varchar2(2000) instead of varchar(20) .

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You probably won't find any performance difference ever. The width of a VARCHAR2 column should be treated merely as an additional built-in constraint.

    If you know the dataa in a particular column will never and should never exceed 20 characters (or bytes, depnds on how you define it), then define it as VARCHAR2(20). That gives you extra data integrity security, because now you know that noone will be able to insesrt text exceeding 20 characters by mistaake. If you know that maximum length of the column will be 2000 characters then by all means define it as VARCHAR2(2000), but there is no reason to define it as VARCHAR2(4000). Got he point?

    Now if you are 95% sure that lenth will not exceed 20, but there is slight posibilty that your assumptions are wrong and it might be longer, I would still define it as VARCHAR2(20). If longer text actualy appears in the input side of that column it will error out, but you allways have the ability to make the column wider with a simple ALTER command if and when this happens.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    What about number columns?

    Difference between NUMBER(10) and NUMBER. (or indeed NUMBER(30))???

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    The number of numbers you can number in a number datatype.

    It's a size constraint. You would get an error if you inserted 123 in a number(2) column.

    There is no performance issue (that I know of)
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =============
    also design people doesn't know how much column width should be
    =============

    You company needs "good system analyst".

    Tamil

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Jmodic ,

    I accept that , defining varchar2(20) over varchar2(2000) is incorporates data integration security ( 200% correct )

    Think that design team are not given enough time but still want to make fool prrof application.

    they are just curious how oracle internally handles tables with varchar2(20) and varchar2(2000) ?

    does oracle occupy more data in buffer pool or any other resorces while executing DML statements ?

    Anywany thanks to every one .

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Also, a point to remember is that a varchar2(2000) has the potential of introducing row chaining or migration if you have a really small block size and the varchar2(2000) keeps getting updated ever larger.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    I think if identical data is present in varchar2(200) and varchar2(2000) . row chaining and row migration should also be same .

    it should not change on varchar2(xx) width .

Posting Permissions

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



Click Here to Expand Forum to Full Width