RE: DWH varchar2(4000)

From: Xiang Rao <xiang_rao_at_hotmail.com>
Date: Mon, 22 Dec 2014 13:26:54 -0500
Message-ID: <BLU405-EAS2462784D7CF0C0C86F0D46A95560_at_phx.gbl>



Thanks Jonathan. Just checked my records, my issue was actually with a clob column with 11.2.0.3/4. Luckily the application only needs first 30 characters so a cast to varchar2(30) solved our problem.

Sent from my Windows Phone



From: Jonathan Lewis<mailto:jonathan_at_jlcomp.demon.co.uk> Sent: ‎12/‎22/‎2014 12:54 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: DWH varchar2(4000)

That may be version dependent; do you have any links to demonstrations ? As far as sorting is concerned I've just done a quick test that showed the memory requirement was dictated by the used length not the declared length (in 12c).

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Xiang Rao [xiang_rao_at_hotmail.com] Sent: 22 December 2014 17:42
To: ricard.martinez_at_gmail.com; Martin Preiss Cc: oracle-l_at_freelists.org
Subject: RE: DWH varchar2(4000)

One issue with varchar2(4000) columns is, when these columns are included in large aggregations, sorting, or even hash join, you could run into problem with memory usages and temp space usages.

Sent from my Windows Phone



From: Ricard Martinez<mailto:ricard.martinez_at_gmail.com> Sent: ‎12/‎22/‎2014 12:04 PM
To: Martin Preiss<mailto:mtnpreiss_at_gmx.de> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: DWH varchar2(4000)

Thanks for that Martin.
Pretty interesting and disturbing at the same time.

Regards

On Mon, Dec 22, 2014 at 4:44 PM, Martin Preiss <mtnpreiss_at_gmx.de<mailto:mtnpreiss_at_gmx.de>> wrote: Ricard,

another point that could result in difficulties has been mention by Nicolay Savvinov recently: rows with a potential row length (i.e. the addition of data type sizes of the different columns) that is bigger than the block size result in a row-by-row processing and supress bulk operations: http://savvinov.com/2014/12/15/4k-bug-is-not-a-bug/.

Regards

Martin Preiss

Am 22.12.2014 16:01, schrieb Ricard Martinez: Hi list,

We have a DWH database on 11.2.0.4.
The developers/data modelers are creating all the tables with varchar2(4000) as standard by default. They don't care if they just insert 10 bytes, 200 or nothing. When asked about why this standard is in place, the answer is just: "Just in case we need it".

For my point of view this is not a good practice, as can cause memory waste on client applications, as well as data garbage or errors like ORA-01450: maximum key length (3218) exceeded.

But as i'm not a DWH expert, maybe im missing information that proves that this is really a good practice, and not just a laziness way of working.

I will be glad to know your opinion, or point me to any doc/test that proves me wrong, in order to learn about this.

Thanks

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 22 2014 - 19:26:54 CET

Original text of this message