OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » oracle9i (Precaution for defining VARCHAR2(4000))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle9i [message #334946]

Fri, 18 July 2008 15:26

shrinika
Messages: 306
Registered: April 2008

Senior Member

Hello,

Why don't we declare max length(VARCHAR2(4000)) length for all string columns? Even if we store Hello, oracle would not allocate 4000. It allocate what we enter in the columns.

Tom has answered for this question.

http://tkyte.blogspot.com/2006/11/see-you-just-cannot-make-this-stuff-up.html

After reading the above link, i have one qestion on his below Tom's statement.

[B]Say the code prepares a query that selects ten columns
that are VARCHAR2. The developers, for performance, would like
to array fetch (very important). They would like to array fetch
say 100 rows (very typical). So, you have 4,000 * 10 * 100 
= almost 4MB of RAM the developers must allocate! Now, consider
if that were ten VARCHAR2(80) fields (it’s probably much 
smaller than that). That’s about 78KB. Ask the data modeler 
how much RAM he is willing to kick in for this system [/B]

He is saying array fetch. Is this operating system specific or db specifc? Can we chane the array fetch? If so how do we change the array fetch? Any answer appreciated..

Report message to a moderator

Re: oracle9i [message #334948 is a reply to message #334946]

Fri, 18 July 2008 15:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is this operating system specific or db specifc?
Yes

>Can we chane the array fetch?
WE? I can. Can you?

>If so how do we change the array fetch?
What do you get when doing a keyword search on http://asktom.oracle.com for "array fetch"?

Report message to a moderator