Help, my developers are killing me with varchar2(4000)
RogB...@gmail.com
and wants me to promote them to QA. I took a look at them, and nearly
every column is varchar2(4000). I am pretty sure this is overkill for
most of them. I know it takes up as much room as the data, but I just
don't like this design philosophy. Does anyone have any references/
urls saying this is a bad idea to design tables like this? It has been
my experience that you get bad data by allowing columns to contain
more data then what it should really hold.
Thanks in advance,
rgd...@monsanto.com
See, you just cannot make this stuff up...
Real email, received today from another Oracle person - asking me a
question:
A developer wants to represent all data types as Varchar2(2000) so
that they won't have to change sizes in the future. This includes
Numbers and Dates. The DBA wants to know if there are any management,
performance, or indexing implications.
Ouch (wonder why they stopped at 2000 with the varchar2?) That hurts
doesn't it...
Here was my response:
They are suggesting one of the worst ideas known to human data
processing people. I cannot over state how BAD AN IDEA THIS IS
ENTIRELY.
Funny thing - dates are 7 bytes, you cannot "under" or "over" size
them (you never CHANGE their size), timestamps are fixed sizes as well
- 7, 11, or 13 bytes depending on fractional seconds and timezone
needs.
Funny thing part two: if someone defines a Number(2), we can "alter
table t modify X number(3)" to increase the size. But, if someone was
to MAX OUT all numbers - we CANNOT shrink them!!!
Funny thing part three: same with varchar2 - we can make them bigger
anytime we need - immediately, absolutely. We cannot however fix the
oversized varchar2 in the future when the column has data - we can
GROW, we cannot SHRINK
Funny thing part four: I spend 20 minutes on this topic - using the
right datatype - in all of my seminars, over and over and over again.
Here is a cut and paste from my book Effective Oracle by Design.
Use the Correct Datatype
Using the correct datatype seems like common sense, but virtually
every system I look at does one of the following:
• Uses a string to store dates or times
• Uses a string to store numbers
• Uses VARCHAR2(4000) to store all strings.
• Uses CHAR(2000) to store all strings, wasting tons of space and
forcing
the use of a lot of trim function calls
• Puts text in a BLOB (raw) type
I have a very simple rule: Put dates in dates, numbers in numbers, and
strings in strings. Never use a datatype to store something other than
what it was designed for, and use the most specific type possible.
Furthermore, only compare dates to dates, strings to strings, and
numbers to numbers. When dates and numbers are stored in strings, or
stored using inappropriate lengths, your system suffers:
• You lose the edit upon insertion to the database, verifying that
your dates are actual dates and numbers are valid numbers.
• You lose performance.
• You potentially increase storage needs.
• You definitely decrease data integrity.
How many of you know what ORA-01722 or ORA-01858 errors are off the
top of your head? I bet many of you do, because they are so prevalent
in systems where numbers are stored in strings (ORA-01722: invalid
number) and dates in strings (ORA-01858: a non-numeric character was
found where a numeric was expected).
How Data Integrity Decreases
Using an incorrect datatype is wrong for many reasons, but the first
and foremost is data integrity. Systems that use strings for dates or
numbers will have some records with dates that are not valid and
numbers that are not numbers. It is just the nature of the game here.
If you permit any string in your date field, at some point, you will
get dirty data in there.
Without data-integrity rules in place, the integrity of your data is
questionable. I’ve needed to write the functions to convert strings to
dates but return NULL when the date won’t convert. I’ve also needed to
try one of five date formats to see if I can get the date to convert.
Can you look at 01/02/03 and tell what date that is? Is that yy/mm/dd,
dd/mm/yy, or something else?
How Performance Suffers
Beyond the obvious data-integrity issues associated with incorrect
datatypes, there are other subtle issues. To demonstrate, we’ll use an
example of a table with two date columns. One will be stored in a
string using YYYYMMDD and the other as a DATE type. We will index
these values and analyze the tables completely.
RogB...@gmail.com
> From Tom Kyte's blog http://tkyte.blogspot.com/search?q=varchar2%284000%29
>
RogB...@gmail.com
>
> See, you just cannot make this stuff up...
> Real email, received today from another Oracle person - asking me a
Found a good one from his Ask Tom site:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055
xho...@gmail.com
> I have a developer that created some tables in a development instnace
> and wants me to promote them to QA. I took a look at them, and nearly
> every column is varchar2(4000). I am pretty sure this is overkill for
> most of them. I know it takes up as much room as the data, but I just
> don't like this design philosophy. Does anyone have any references/
> urls saying this is a bad idea to design tables like this?
How can people provide a reference as to whether *your* data is such that
varchar2(4000) is reasonable or not?
> It has been
> my experience that you get bad data by allowing columns to contain
> more data then what it should really hold.
Because data entry clerks just keep adding gibberish to the end of the data
until it gets rejected for being oversize, and then cut off the last byte
of gibberish until it stops being rejected? "We let people enter random
crap, but by God it has to be random crap of the correct length!"
Xho
--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
Mark D Powell
The arguments you have detailed are pretty good. Namely if a data
value should have only 5 characters then the database should only
accept 5 characters because any value longer than 5 characters is
obviously an error and bad data should not be accepted.
Altering varchar2 columns to be longer and number columns to allow
larger values is pretty much a painless rdbms base table change (as
you noted).
HTH -- Mark D Powell --
joel garry
I added it to the asktom post, but for completeness here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref734
The part about having to make client arrays larger is probably more
important that this, though.
jg
--
@home.com is bogus.
"Besides, I do derive some professional amusement from breaking
systems :-)"
http://catless.ncl.ac.uk/Risks/25.24.html#subj5
Tim X
OMG - are som eof our developers working for you as well?
I have this arguement regularly, particularly with web developers for
some reason. I'll be there are no check, fk or any other referenctial
integrity constraints in there as well!
I think part of the problem here is that many, particularly young and
often web oriented developers just use the database as a 'bit
bucket'. We had exactly this issue recently. Not only were all the
fields varchar2(400) (including ones used to store dates, timestamps,
and numbers - even unnatural primary keys that used sequences to
generate the unique key) the tables were called things like bens_data
where Ben is one of the developers. Worse still, all the logic
reagarding the data relationships and constraints was handled in the
client app code (more often than not java/jsp),
I think essentially, it is a combination of laziness (can't be bothered
actually doing any formal analysis and data modeling) and ignorance
(don't even understand when I try to explain why its bad design).
I regularly try to explain the benefits of using the facilities of the
database to assist in managing your data e.g. don't have to replicate
the logic in every client app and therefore don't have problems in
keeping things consistent between apps, providing valuable information
for the poor suckers that end up having to maintain the apps etc. All I
get back is that their way is more flexible and quicker. My way is
slower and harder to modify. Of course, they don't consider
maintenance.
A big part of the problem is lack of experience. For some reason, there
is this growing belief in some management quarters that these young
recent graduates, who are half the cost of more experienced developers,
are doing excellent work - I mean, seew snazy that web page looks. Of
course, when they realise the app is becoming nearly impossible to
maintain, the original developer has moved on and so has the
manager. Some new manager and new recent grad is responsible. they end
up bitching about how bad the previous lot were and then go about
re-implementing the app because its too hard to maintain. Unfortunately,
they just do the same thing all over again and the whole process starts
another cycle.
All I can suggest is to keep pushing your case and don't get too
frustrated when you can't get any support for your arguments. If your
really really into frustration and pain, start talking about the
benefits of developers using things like dbms_application_info and
providing some help to the DBAs who keep getting hassled to solve
performance problems etc. I'm not a DBA BTW.
Tim
--
tcross (at) rapttech dot com dot au
gym dot scuba dot kennedy at gmail
<RogB...@gmail.com> wrote in message
news:fca4ed5e-1e56-4703...@d1g2000hsg.googlegroups.com...
In another part of the company I work for they use Mysql and have committed
the same sins. So mysql stores data as you would expect, if you have 5
bytes in a varchar2(4000) column it takes close to 5 bytes on disk.
However, once you read it off disk mysql expands the size to the size
defined for the column. and they wonder why they have memory issues and
perfornamce issues. (glad Oracle doesn't do that)
Jim
RogB...@gmail.com
Funny!
>
> I think part of the problem here is that many, particularly young and
> often web oriented developers just use the database as a 'bit
> bucket'.
> I think essentially, it is a combination of laziness (can't be bothered
> actually doing any formal analysis and data modeling) and ignorance
> (don't even understand when I try to explain why its bad design).
Often it is laziness. Sometimes ingnorance, and when I explain, they
are usually happy to go by any recommendations that I made.
However, I think in this case, the developers are under a deadline and
they don't want to take the time to get the correct specs, or are
afraid to pester the customer/end user with questions. They said
"these were the design requirements provided." Like I am sure the
administrators said "make every field varchar2(4000)." They don't even
know what a datatype is.
>
> A big part of the problem is lack of experience.
True, we usually hire beginners because they are the only ones that
accept the salary that is offered.
Wolfram Roesler
@d1g2000hsg.googlegroups.com:
Probably they want "random length" strings - a possibility to store
a string without having to implement an artificial size limit.
C++ can do it (using the std::string datatype), JavaScript can do it,
Tcl can do it, SQLite can do it, old Unix shells and modern BASICs can
can do it, but Oracle can't, so they use VARCHAR2(4000), which is the
closest thing to a random length string they can get with Oracle. Quite
understandable for someone with a C++/JavaScript/Tcl/whatever background
imho.
Of course it is bad style, but the question why this actualls IS bad (i. e.
what bad things will happen with it) still hasn't been answered in this
discussion. Neither has the question been asked why Oracle doesn't support
a random length string datatype.
Best regards
W. Rösler
sybr...@hccnet.nl
wrote:
> Neither has the question been asked why Oracle doesn't support
>a random length string datatype.
That question doesn't need to be asked.
First of all Oracle already has CLOBs for this purpose, since 8.0, so
in the previous milennium.
Secondly, a random length string datatype would cause heavy row
chaining, so kill performance.
--
Sybrand Bakker
Senior Oracle DBA
Serge Rielau
There seems to be a general movement in the industry to support random
length strings while keeping row-chaining in check.
That is to inline LOBs in the row as far as is possible without chaining
while providing a full complement of (fast) string manipulation function
on them.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Noons
>>
> Aren't there some severe limitations on what you can do with a CLOB?
>
> There seems to be a general movement in the industry to support random
> length strings while keeping row-chaining in check.
> That is to inline LOBs in the row as far as is possible without chaining
> while providing a full complement of (fast) string manipulation function
> on them.
you have got to re-read the doco for 10g and 11g,
been available for a while. most of the liitations
have been removed. most of the string manipulation
functions now work quite well with clobs.
and the in-lining has been there since clob's
started in 8.
Wolfram Roesler
4ax.com:
>> Neither has the question been asked why Oracle doesn't support
>>a random length string datatype.
>
> That question doesn't need to be asked.
Why not?
> First of all Oracle already has CLOBs for this purpose, since 8.0, so
> in the previous milennium.
Can a CLOB be used in the same way a VARCHAR2 can be used, e. g. in
SELECT, UPDATE, WHERE, ORDER BY, GROUP etc.?
> Secondly, a random length string datatype would cause heavy row
> chaining, so kill performance.
Could you elaborate on this?
Thanks and best regards
W. Rösler
xho...@gmail.com
> On Mon, 11 Aug 2008 13:21:42 +0000 (UTC), Wolfram Roesler <w...@spam.la>
> wrote:
>
> > Neither has the question been asked why Oracle doesn't support
> >a random length string datatype.
>
> That question doesn't need to be asked.
> First of all Oracle already has CLOBs for this purpose, since 8.0, so
> in the previous milennium.
Using Perl's DBD::Oracle, CLOBs have several undesirable side effects
if what one wants is a varchar2 but without the arbitrary limit. I don't
know if other drivers do a better job of circumventing these problems or
not.
> Secondly, a random length string datatype would cause heavy row
> chaining, so kill performance.
If 99.99% of the data has short strings and 0.01% needs row chaining, the
performance impact will be negligible, while the benefits of not having
0.01% of your data trigger the collapse of your application are great.
The A, the I, and the D of ACID all "kill" performance, too. Yet they
are worth it.
DA Morgan
> sybr...@hccnet.nl wrote in news:sng0a4p22d5tnep5em9cn9qnjqfgcs709c@
> 4ax.com:
>
>>> Neither has the question been asked why Oracle doesn't support
>>> a random length string datatype.
>> That question doesn't need to be asked.
>
> Why not?
Well for one thing there's SYS.ANYDATA.
>> First of all Oracle already has CLOBs for this purpose, since 8.0, so
>> in the previous milennium.
>
> Can a CLOB be used in the same way a VARCHAR2 can be used, e. g. in
> SELECT, UPDATE, WHERE, ORDER BY, GROUP etc.?
With, in some cases, the use of the DBMS_LOB package: Yes.
>> Secondly, a random length string datatype would cause heavy row
>> chaining, so kill performance.
>
> Could you elaborate on this?
Go to http://tahiti.oracle.com and look up row chaining. If you don't
understand the concept and the implications reading the docs is better
than a few random sentences thrown over the cubicle wall.
> Thanks and best regards
> W. Rösler
Serge: You really need to start spending more time in Redwood Shores.
<g> Maybe take a job there.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
phil_h...@yahoo.com.au
> Of course it is bad style, but the question why this actualls IS bad (i. e.
> what bad things will happen with it) still hasn't been answered in this
> discussion.
The difference is one of outlook. Many application developers take the
position that the DB is part of the application; it's just a bucket
you can toss things into and fish out later on. Almost everybody else
takes the view that the DB is a separate thing, and the app is just
one of a number of tools that use it. This latter group includes the
DBA, the bean counters who prowl the data, the managers who read the
reports from the bean counters, and pretty much all the other
stakeholders who depend on access to accurate data to do their jobs.
These people generally take the view that the database should contain
as much semantic information as possible about the data that it
contains, so making all your data long strings is heading in the
opposite direction to what you want. Rather, you want to be as
specific as possible at all times. That means if you're designing a
database and you don't know what's going in a particular column, you
find out. Don't be lazy and just say "oh, let's make it a big string
and code around that"; find out what it is. If it's a date, use a
date. If it's an integer, use an integer. If it's a PDF, use a BLOB.
Most modern DBMSs have enough types to satisfy just about any
requirement. Using them appropriately is a good thing. It conveys
useful information. It also catches a whole raft of programming
errors, such as when people try to stuff a string into a date column.
If the column is correctly typed, that can't happen, but if it's a
string, it will.
Theere are also issues of scalability, both in data size and number of
users. The world gets a lot more complex when your database leaves
your test environment, where it had a few dozen rows per table and
three or four users, and suddenly has a few million rows and a few
hundred users. In such circumstances, I have seen few implementations
of the "big bucket" data model survive for long. Most fail pretty
quickly, because you need to do everything right if you want to scale
up substantially.
-- Phil
Tim X
I second that. The database isn't supposed to just be a bit bucket. It
is supposed to be a representation of the underlying data model. As
such, it provides, or at least can provide, a lot of valuable
maintenance information and error checking and 'free' data integrity
management through the use of the right types, various constraints
(including maximum data sizes) etc.
In a reasonable number of years doing database development, I've not yet
come across any character type that I couldn't identify a maximum length
for that I would want to process/manipulate as a varchar2 (or similar
types in other products). maybe I've been lucky, but by the time I've
encountered something that wouldn't fit inside the 4k limit, it usually
meant it wasn't something I needed to manipulate in a string like
manner, but instead treated as just a big blob of something. Of course,
the landscape is changing a bit with XML, but Oracle has alternative
types and support for XML anyway.
Noons
>big snip
> Theere are also issues of scalability, both in data size and number of
> users. The world gets a lot more complex when your database leaves
> your test environment, where it had a few dozen rows per table and
> three or four users, and suddenly has a few million rows and a few
> hundred users. In such circumstances, I have seen few implementations
> of the "big bucket" data model survive for long. Most fail pretty
> quickly, because you need to do everything right if you want to scale
> up substantially.
>
very true. To use a db as a bit bucket is the negation
of the very idea of building on previous work. Which is
a characteristic of some current deranged development
methodologies: nothing like re-inventing the wheel at every
corner to guarantee a continuous stream of income, ain't it?
Fact is: dbs have inbuilt mechanisms for type diversity
and coherence as well as scalability. Already debugged,
optimized and ready to work for any project, not just the
flavour-de-jour. To ignore such a resource in the name of
vague design notions never proven in any successful
delivery borders on criminal...
DA Morgan
> very true. To use a db as a bit bucket is the negation
> of the very idea of building on previous work. Which is
> a characteristic of some current deranged development
> methodologies: nothing like re-inventing the wheel at every
> corner to guarantee a continuous stream of income, ain't it?
>
> Fact is: dbs have inbuilt mechanisms for type diversity
> and coherence as well as scalability. Already debugged,
> optimized and ready to work for any project, not just the
> flavour-de-jour. To ignore such a resource in the name of
> vague design notions never proven in any successful
> delivery borders on criminal...
Criminal is a reasonable description. If physicians are required
to take an oath to "do no harm" ... developers and DBAs should do
so too. Those that ignore the proven capabilities of industrial
strength databases are those that couldn't answer a question
about third-normal form if doing so would save them from drowning.
gazzag
> Criminal is a reasonable description. If physicians are required
> to take an oath to "do no harm" ... developers and DBAs should do
> so too. Those that ignore the proven capabilities of industrial
> strength databases are those that couldn't answer a question
> about third-normal form if doing so would save them from drowning.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
I agree with all the comments regarding "bit-buckets". However, has
anyone noticed that, when using Oracle XE's Object Browser to create a
table - I don't use GUI's to create objects as I prefer to script them
but I was demonstrating the GUI to a developer, ironically - when you
create a column of type VARCHAR2 the scale defaults to a value of
4000?
Oracle's not even giving us DBA's a fighting chance to stamp out bad
habits! :)
-g
Noons
> I agree with all the comments regarding "bit-buckets". However, has
> anyone noticed that, when using Oracle XE's Object Browser to create a
> table - I don't use GUI's to create objects as I prefer to script them
> but I was demonstrating the GUI to a developer, ironically - when you
> create a column of type VARCHAR2 the scale defaults to a value of
> 4000?
What does NUMBER default to? (Sorry, haven't touched
XE in years!)
>
> Oracle's not even giving us DBA's a fighting chance to stamp out bad
> habits! :)
That's more a habit than many would admit...
;)
DA Morgan
> Oracle's not even giving us DBA's a fighting chance to stamp out bad
> habits! :)
>
> -g
Hopefully you aren't expecting the auto companies to now design cars
that protect you from teenage drivers.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Palooka
> What does NUMBER default to? (Sorry, haven't touched
> XE in years!)
>
Palooka
Noons
>
> Hopefully you aren't expecting the auto companies to now design cars
> that protect you from teenage drivers.
LOL! And WHY not?
I want a nanny!!!!
gazzag
> What does NUMBER default to? (Sorry, haven't touched
> XE in years!)
It doesn't as I guess you can simply specify type NUMBER.
gazzag
>
> Hopefully you aren't expecting the auto companies to now design cars
> that protect you from teenage drivers.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
Of course not. But I don't expect the throttle to default to "full"
either.
-g
Noons
Ah, that's NUMBER with max size then. Thanks.
DA Morgan
Perhaps the same logic is why Oracle used to makes it hard from
untrained people to install the product. <g>
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
xho...@gmail.com
So what should the default size of a varchar2 be then? 7? 12? 23? 0?
sybr...@hccnet.nl
>gazzag <gar...@jamms.org> wrote:
>> On 13 Aug, 21:13, DA Morgan <damor...@psoug.org> wrote:
>> >
>> > Hopefully you aren't expecting the auto companies to now design cars
>> > that protect you from teenage drivers.
>> > --
>> > Daniel A. Morgan
>> > Oracle Ace Director & Instructor
>> > University of Washington
>> > damor...@x.washington.edu (replace x with u to respond)
>> > Puget Sound Oracle Users Groupwww.psoug.org
>>
>> Of course not. But I don't expect the throttle to default to "full"
>> either.
>
>So what should the default size of a varchar2 be then? 7? 12? 23? 0?
>
>Xho
30 does quite nicely in many circumstances.
Vince
We dont use a single default but rather when we do our data modeling,
we use domains based on the intent of the column. "names", for example
might be 50, "codes", maybe 10, "descriptions" might be 1000, etc.
Noons
> gazzag <gar...@jamms.org> wrote:
>> On 13 Aug, 21:13, DA Morgan <damor...@psoug.org> wrote:
>>> Hopefully you aren't expecting the auto companies to now design cars
>>> that protect you from teenage drivers.
>
>> either.
>
> So what should the default size of a varchar2 be then? 7? 12? 23? 0?
does it really matter? it's variable length anyway:
the size is a constraint, not an allocation issue.
DA Morgan
As with many things ... depends ...
For example when working with bind variables VARCHAR2s are rounded to
the next highest length that can be 32 bytes, 128 bytes, 2000 bytes,
or 4000 bytes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Noons
>>>
>>> So what should the default size of a varchar2 be then? 7? 12? 23? 0?
>>
>>
>> does it really matter? it's variable length anyway:
>> the size is a constraint, not an allocation issue.
>
> As with many things ... depends ...
>
> For example when working with bind variables VARCHAR2s are rounded to
> the next highest length that can be 32 bytes, 128 bytes, 2000 bytes,
> or 4000 bytes.
I don't think VARCHAR2s are rounded up at all in the
tables, which is what I thought we were talking about.
Sure: a bind variable might "adjust" the length of a
VARCHAR2 parameter, but that does not replace the table
column's definition in the dictionary?
IOW: the bind variable might not store data very efficiently
but the column in the table doesn't stop having its max
size checked because of that?
joel garry
The OP asked if it is a bad idea to design tables with everything 4000
long varchar2's, so part of the answer is going to involve what
happens when you move the data out of the db and manipulate it. Does
the size definition make a difference in how Oracle deals with the
data? Yes, it does. Is it a big deal? Well, to answer that, you
have to define what a big deal is.
Whatever definition you come up with, if you start investigating what
happens under high load, it becomes a big deal. That's where most of
these developers that create generalized solutions fall short, since
they assume you can always throw more hardware at it and it will
magically scale, without specifying an upper limit requirement.
Right?
jg
--
@home.com is bogus.
Mommy, why are they arresting Tinkerbell?
http://edition.cnn.com/2008/US/08/15/disney.protesters.ap/index.html?iref=mpstoryview
DA Morgan
I specifically said "bind variables." My intention was to be clear
and concise and to indicate that in some cases it does matter with
respect to allocation.
Noons
>>
>> IOW: the bind variable might not store data very efficiently
>> but the column in the table doesn't stop having its max
>> size checked because of that?
>
> The OP asked if it is a bad idea to design tables with everything 4000
> long varchar2's, so part of the answer is going to involve what
> happens when you move the data out of the db and manipulate it. Does
> the size definition make a difference in how Oracle deals with the
> data? Yes, it does. Is it a big deal? Well, to answer that, you
> have to define what a big deal is.
Yeah, good point. It might even have an impact
of SQL optimisation: if all varchar2 is 4000,
cbo might try to "guess" rows will be large.
> Whatever definition you come up with, if you start investigating what
> happens under high load, it becomes a big deal. That's where most of
> these developers that create generalized solutions fall short, since
> they assume you can always throw more hardware at it and it will
> magically scale, without specifying an upper limit requirement.
> Right?
>
LOL! How true...