Re: add a new column into the middle of an existing table

Nuno Souto wrote:

> In article <aanpsk01hv1_at_drn.newsguy.com>, you said (and I quote):
> >
> > 1. Storage - if you have a null column in the middle of a row, there is a
> > length indicator for the null column. If the null column is at the end of the
> > row there is no length indicator. Not a major concern, but there is some wasted
> > space having null columns in the middle of a row.
> >
> > 2. Performance - when Oracle reads a row for a query it only reads as far along
> > the row as it needs to satisfy the query. Let's say you had a query that needed
> > only the first two columns in the row. The rest of the row is not read. Since
> > mandatory columns can also be queried frequently, this may mean that when you
> > add a mandatory column that it's useful to add it at the front of the row.
> >
>
> And the most important:
>
> 3- Compatibility - when the database has 3 tons of code written with the
> following general syntax:
>
> INSERT INTO <tablename> VALUES(:col1,:col2,:col3...);
>
> instead of:
>
> INSERT INTO <tablename> (COL1,COL2,COL3...)
> VALUES (:col1,:col2,:col3...);
>
> In the first case, if the new column went anywhere but the "end" of the
> previous columns, you'd have a big mess in your hands!
>
> One of these days someone should write a book on how to design and code
> defensively. As in: designed and written to avoid likely future
> problems.
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

I can't speak to a book ... but I can tell you that I teach that you should never code as:

INSERT INTO <tablename> VALUES(:col1,:col2,:col3...);

Because of the danger that columns will be added or dropped causing the code to fail.

Daniel Morgan Received on Wed May 01 2002 - 17:23:52 CEST