Re: How to drop column in a table

Shaochun Lin (lovedog_at_gwis2.circ.gwu.edu) wrote:
: I have simple question, it seems it's hard to answer. I have a very large
: table (150 columns). Now I want to drop three columns. I look thru all the
: book. It seems they dont' have a way to do that. If you have way to do
: that, please do give me a answer, other wise, I have to drop whole table
: and recreate it again. That's a really tedious procedure. Many thanks in
: advance. Any suggestion is welcome. S.C.

Consider that Oracle parses SQL column names into column numbers. This should give a clue to how the data dictionary is managed. In other words, table names relate to some neutral descriptor that uses column numbers. That being said, imagine the chaos that would ensue if you changed that mapping so that some column numbers were no longer there. Would you use place holders, or just shift the remaining columns to the left, i.e. to lower column numbers? This implies a remapping of all the views that are loaded to your system. You would definitely have to shut the database down to do something like this, were it even possible! PL/SQL and any embedded SQL would have to be redone as well!

Another question. What about the underlying data storage? Let's say you have a 20GB table. Would you maintain a separate mapping for existing data, or just explicitly remove the offending columns? If you maintained separate mapping, what about the case where 10 of the 150 columns were removed at far different times? This would imply a data mapping that changes with the date of the data. This also implies a slow database! If you explicitly removed the offending columns, imagine the opportunities for failure. What if a hard disk dies? Of course this is always a problem, but you wouldn't want this to happen in the middle of actually reorganizing a 20GB table! The same situation applies if the OS dies, or the machine is powered down, etc. In addition, 20GB is going to take quite some time to remap!

All things considered, I'm not surprised that there is no Oracle command for removing a column.

The next obvious question is why doesn't it slow an RDBMS down to add columns. I think that you can just check the length of the basic row storage unit (whatever method Oracle uses for the base data), or perhaps some other trivial method, to see how many of the columns are actually in the table. The longer the storage unit, the more columns are there.

This is all just supposition. I have no direct knowledge of how the internals of Oracle works.

Later.

--
This is not a permanent address.  Do not add it to your alias file.

1. What would you do to improve life on earth?
2. If it worked, how would you feed the increasing population?

Bob Stewart (KB9ZW)            |  Who's gonna pay the bills
wk USA (310) 335-7152          |  when everyone's on welfare?