RE: : RE: bytes vs chars
Date: Tue, 15 Mar 2016 16:57:17 +0000
Message-ID: <DUB113-W1180602851503F905205A5F85890_at_phx.gbl>
Your case seems to be "People aren't very good at data modelling so lets just over-allocate everything and not worry about it."
I don't agree with that.
Your assertion "This means there is no negative operational impact due to overly large VARCHAR2 columns." is false. There are very real performance impacts in relation to being unable to do set-based operations efficiently.
I have no problem with over-allocation - but not everywhere. If it's a small lookup table, a few million rows, no problem. If it's a big table will tens of billions of rows, I don't want it to be greater than an Oracle block size, have more than 254 columns, contain in-line LOBs (unless you always retrieve them), or have too many indexes. You will have problems.
Yes, computers are very powerful. Yes, commodity memory and disk are relatively cheap, especially when compared to Oracle license costs. But there's no need for incorrect assertions promoting poor design.
regards
Neil
If you think hiring a profession is expensive, see how much it costs when you hire an amateur.
> To: oracle-l_at_freelists.org
> From: oracle_at_1001111.com
> Subject: RE: : RE: bytes vs chars
> Date: Tue, 15 Mar 2016 08:00:48 -0600
>
> First, I agree with Robert the underlying problem is poor data modeling. The reasons for this
> are varied:
> - data modeling is hard and very few people have formal training,
> - people tend to model existing data rather than describing objects and their characteristics,
> - and finally, it is hard to predict the future.
>
> None of us are omniscient.
>
> Given the above lets deal with the impacts of incorrect sizes.
>
> There are no negative server side impacts from overly large VARCHAR2 columns. Space is not allocated
> until a VARCHAR2 is used and then only enough space to hold the data is consumed. The only negative
> impact is on the client where memory must be allocated to hold results returned from the database.
> This was important 15-20 years ago but has little relevance now that we have GBs of RAM available.
> IF it does create problems it should be detected during testing and then it is trivial to fix by reducing
> the column size. This means there is no negative operational impact due to overly large VARCHAR2 columns.
>
> However, too small VARCHAR2 cause outages. A database should be able to store any valid data no matter what
> the length. If it cannot then, at least for that application, the database is not available. I hear over
> and over it is "easy" to increase the size of a column. However, the outage has already occurred, and
> there are often application code changes required to handle the larger column meaning there are negative
> impacts and costs to your client.
>
> How many have dealt with ZIPCODE NUMBER(5)?
> How many have dealt with ZIPCODE NUMBER(6)?
> Should it be ZIPCODE VARCHAR2(6)?
> Possibly ZIPCODE VARCHAR2(10)?
> How about ZIPCODE VARCHAR2(64)?
>
> Only one will handle all variants of zipcodes/postal codes used in the world today. Sure your company or client
> only does business in the USA so NUMBER(6) works. Then, your company chooses to do business in Canada. Ooops,
> lots of time and money spent to allow that.
>
> In summary, if you are not planning for the future then you are planning to fail!
> I do not believe DBAs should plan to fail.
>
> And a bit of anecdotal evidence.
>
> At a client, in the early 1990s, long before my time, the company could not believe they would ever issue more than
> 1 million invoices per year. Accordingly the model used the 4 digit year concatenated with a 6 digit number as
> a unique identifier. Move forward to 2010 and the final invoice run of the year fails 2/3 of the way through.
>
> Negative Impacts:
> - 5-6 million dollars worth of invoices cannot be issued
> - massive reconciliation problems
> - cannot close the monthly books quarterly books or yearly books.
> - since the invoice id was a primary key it was not easy to increase it. All child tables had to change too
>
> The company was lucky it was private otherwise there would issues with the SEC due to late reporting of financial figures.
> Estimated cost including lost productivity, and repair time was estimated at more than the original cost to build the
> program. What would have been the negative impact of using a NUMBER(15,0) column instead of a NUMBER(10,0).?
> (I know the year should not have been encoded either, which would have prevented the problem also, but the business wanted....)
>
> I have dealt with too small columns too many times to count. I have never had an issue with a column definition
> that caused problems because it was too large. I am not saying it cannot happen, I am claiming it happens very rarely.
>
> YMMV
> Dave
>
> --
> Dave Morgan
> Senior Consultant, 1001111 Alberta Limited
> dave.morgan_at_1001111.com
> 403 399 2442
> --
> http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 15 2016 - 17:57:17 CET