Help, my developers are killing me with varchar2(4000)

On 14 Aug 2008 18:45:19 GMT, xhoster@gmail.com wrote:

>gazzag wrote:
>> On 13 Aug, 21:13, DA Morgan 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.

--
Sybrand Bakker
Senior Oracle DBA

On Aug 14, 11:45*am, xhos...@gmail.com wrote:
> gazzag wrote:
> > On 13 Aug, 21:13, DA Morgan 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
>
> --
> --------------------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.

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.

xhoster@gmail.com wrote,on my timestamp of 15/08/2008 4:45 AM:
> gazzag wrote:
>> On 13 Aug, 21:13, DA Morgan wrote:
>>> Hopefully you aren't expecting the auto companies to now design cars
>>> that protect you from teenage drivers.
>
>> 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?


does it really matter? it's variable length anyway:
the size is a constraint, not an allocation issue.

Noons wrote:
> xhoster@gmail.com wrote,on my timestamp of 15/08/2008 4:45 AM:
>> gazzag wrote:
>>> On 13 Aug, 21:13, DA Morgan wrote:
>>>> Hopefully you aren't expecting the auto companies to now design cars
>>>> that protect you from teenage drivers.
> >
>>> 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?
>
>
> 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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote,on my timestamp of 15/08/2008 11:17 AM:

>>>
>>> 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?

On Aug 15, 4:08*am, Noons wrote:
> DA Morgan wrote,on my timestamp of 15/08/2008 11:17 AM:
>
>
>
> >>> 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?

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...ef=mpstoryview

Noons wrote:
> DA Morgan wrote,on my timestamp of 15/08/2008 11:17 AM:
>
>>>>
>>>> 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?

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

joel garry wrote,on my timestamp of 16/08/2008 2:28 AM:

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