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 .

Hi,

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

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) .

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.

What about number columns?

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

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)

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

You company needs "good system analyst".

Tamil

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 .

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.

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 .

Not that it is tooooo important but you also will incur an additional byte of overhead in the block header for each row with any column over 250 bytes.

Gregg

interesting , is it on the row data or table structure ( all columns width ).

where can i find more info on below statement

Not that it is tooooo important but you also will incur an additional byte of overhead in the block header for each row with any column over 250 bytes.

I herby nominate this thread as one of the most pointell Oracle discussions in the history of dbasupport.com

All in favor.

MH

Quote:

---

Originally posted by Mr.Hanky
I herby nominate this thread as one of the most pointell Oracle discussions in the history of dbasupport.com

All in favor.

MH

---

Aye,agreed

wtf is pointell?

Quote:

---

Originally posted by stmontgo
Aye,agreed

wtf is pointell?

---

You agreed, but you have no clue what pointell means? WTF???

LISTEN TO WHAT I MEAN , NOT WHAT I SAY.

DAMN YOU LINEAR THINKING TECHNICAL TYPES!

:p

MH