Re: Pizza Example

On Tue, 06 Apr 2004 01:00:26 -0400, Jerry Gitomer <jgitomer_at_erols.com> wrote:

>Whoa! I can alter a table and increase the width of a column without
>taking the database down or interfering with the users of the database.

Just because you can, doesn't necessarily mean that you should.

>There is no risk in increasing the width of a column.

There are many risks associated with increasing the size of a column, some of which have been mentioned elsewhere in the thread, but for example:

Programs may have been written assuming that a column is of a particular size, and so have allocated only enough working storage to accommodate that size. If the column size increases, those programs will begin to fail once the "old" size is exceeded. If those programs run as part of a batch suite, it may be necessary to re-run the suite from the last save step. If your month end suite takes 24 hours to run, and it falls over an hour from the start, then on rerun falls over again after two hours, then again after four, etc. all because the DBA increased the length of the "flavour" column by 1 character so a user could enter "Pineapple and strawberry cream with a hint of vanilla, cointreau and gooseberries" there are going to be a lot of questions the next day.

Reports may have been created reliant on the column being a particular size. Changing the column size may mess up the report to such an extent that it is illegible or misleading.

If the column is used to populate a data item which is part of a structured record picked up by another program, or a data packet transmitted to an external system, the receiver of that record or packet will likely not be able to cope with the new size.

For a real-world example which should be familiar to most people, recall Y2K, where a simple change from two-digits to four-digits to represent the year required an enormous development effort. If a user had come to you, the DBA, to request that you change all the year fields to four digits, would you have done it? Just because you can, doesn't mean you should.

In a system I worked on a couple of years ago, a DBA was asked by a user to add an index to a column with very low cardinality (it was a flag which contained the values 0 and 1 only). It seemed a harmless change, so he did it. A queries which had been taking a few milliseconds began taking around 5 minutes to complete. This query was executed, once per call, on an overnight data-collection system, connecting to 12,000 remote terminals via dialup. Calls normally took around 20 seconds to complete, with a 10-second "no data" timeout. Needless to say, we didn't collect any data until that was found and fixed.

>If you are working
>with a DBA who refuses to make necessary changes or a DBMS that won't
>allow such changes get rid of the offender.

Because of the risks, it is necessary to perform impact analysis for even the most apparently trivial of changes to the database structure.

Before a DBA makes such changes, he should have to satisfy himself firstly that the change is really necessary, and secondly that adequate impact analysis has been done. Where the impact analysis shows that changes are required to programs, reports, etc. which make use of the data he must also be satisfied that these changes are being/have been completed, and the change to the column length will take place at the same time new versions of programs/reports are implemented. This is why most well-run organisations have change-control measures in place: Users request changes, explain the reason for them, experts from a number of areas perform impact analysis, the change is either approved or denied and development resources are allocated to the changes required to the rest of the system.

This isn't confined to relational databases, it's a "feature" of all database systems which have to interface with the outside world, where "outside world" includes programs, reports, GUIs, datacomms, etc.

Lemming

-- 
Curiosity *may* have killed Schrodinger's cat.