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

On Tue, 30 Apr 2002 22:09:47 GMT, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>> Is there an easy way to add a new column into the middle of an
>> existing table? Seems to me that you have to create a new table and
>> copies data from the old table. <Alter table add column> statement
>> will only adds columns at the end of an existing table.
>>
>> I am using Oracle 8.1.7 EE on Solaris 2.7.
>
>You can not ... but it is equally true that the location of a column in
>a table is absolutely irrelevant. Stick it at the end and adjust your
>thinking.

 Certainly it should be completely irrelevant in terms of queries; only badly written queries and badly written programs relying on the order of columns returned by a 'select *' would be affected. They should of course be rewritten, so this isn't a real reason.

 But isn't it true that there are some (admittedly minor) space savings to be had in putting fields that are commonly NULL at the end of a table, as trailing NULLs in a row take no storage (Oracle 8i Concepts Guide, chapter 10, section on 'Nulls')?

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c08schem.htm#2848

-- 
Andy Hassall (andy_at_andyh.org) icq(5747695) http://www.andyh.org
http://www.andyh.uklinux.net/space | disk usage analysis tool