add a new column into the middle of an existing table
lei wang
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.
Thanks.
Daniel Morgan
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.
Daniel Morgan
Scott Mattes
table_a contains 3 fields, fld1, fld2, fld3 and you want to add field fld2_5
to the table. And you would like fld2_5 to appear between fld2 and fld3.
Adding the field is no problem, but it will be added to the end, no way
around that other than to create a new table and copy all the records --
messy and not needed.
just specify the fields in your select to put them in the order you want
select fld3, fld2_5, fld1, fld2 from ...
select fld2, fld1, fld3, fld2_5 from ...
select fld1, fld2, fld2_5, fld3 from ...
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3CCF162A...@exesolutions.com...
stinky
I thought it was recommended to place nullable columns at the end of a
table. I thought I read that somewhere. Something about saving space
in the data block.
>
>
>Daniel Morgan
>
Pete Sharman
Years and years ago (I think it was prior to 7.3) we did some testing of column
placement because of the very issue you raised initially - new columns are added
to the end of the row. What we found was placement of a new column could be
important for two reasons:
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.
Now I haven't tested this in a long time so the behaviour may be different now.
If it's not, then the way to reorg the table is dependent on the version. In
versions before Oracle9i, you need to recreate the table with the columns in the
right order. In 9i, you can use the DBMS_REDEFINITION package to reorder the
columns while the table remains online.
>
>>
>>
>>Daniel Morgan
>>
>
>
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Andy
1. Export the table, drop it, recreate it with the new column order,
import it with ignore=y
2. Use TOAD (www.quest.com) - go to schema browser, select the table,
click on rebuild table, double-click on the columns in the top pane to
move them into the bottom one, then add them again from the bottom
pane to get them in the desired order. All this does is provide a GUI
interface for (1) above.
1:
SQL> desc andy
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(2)
COL2 VARCHAR2(10)
COL3 VARCHAR2(10)
SQL> !
oracle@nadir$ exp oradba/PASSWORD file=a.dmp log=a.log tables=andy
Export: Release 8.0.6.0.0 - Production on Wed May 1 15:00:32 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 -
Production
PL/SQL Release 8.0.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table ANDY 3 rows
exported
Export terminated successfully without warnings.
oracle@nadir$ exit
SQL> drop table andy;
Table dropped.
SQL> create table andy(col1 char(2),col3 varchar2(10),col2
varchar2(10));
Table created.
SQL> desc andy
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(2)
COL3 VARCHAR2(10)
COL2 VARCHAR2(10)
SQL> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.6.0.0 -
Production
PL/SQL Release 8.0.6.0.0 - Production
oracle@nadir$ imp oradba/PASSWORD file=a.dmp full=y ignore=y
Import: Release 8.0.6.0.0 - Production on Wed May 1 15:01:31 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 -
Production
PL/SQL Release 8.0.6.0.0 - Production
Export file created by EXPORT:V08.00.06 via conventional path
. importing ORADBA's objects into ORADBA
. . importing table "ANDY" 3 rows
imported
Import terminated successfully without warnings.
oracle@nadir$ exit
SQL> desc andy
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(2)
COL3 VARCHAR2(10)
COL2 VARCHAR2(10)
Andy Hassall
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 (an...@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyh.uklinux.net/space | disk usage analysis tool
Nuno Souto
>
> 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
nso...@optushome.com.au.nospam
J.P.
The physical location of a column is totally irrelevant in a RDBMS.
JP
leiw...@hotmail.com (lei wang) wrote in message news:<af074b9c.02043...@posting.google.com>...
Daniel Morgan
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
D.Y.
This makes sense. I would guess queries are still done this way. How do you
get to the second column without at least reading the length indicator of the
first column if it's a variable length field. People are mostly concerned with
I/O and you certainly will save little I/O, if not nothing, whatever the column
order is. But if you can save a few CPU cycles without hurting anything else
then why not?
Pablo Sanchez
According to the Ixora News - April 2002, emperical analysis shows
that you're incorrect Daniel:
----------------------
A call to order
The order of columns in a table can have an impact on performance.
There are two factors to consider.
Firstly, Oracle rows are stored as a row header followed by column
data. The row header contains a flag byte, lock byte and column count,
then for each column there is a column length followed by the column
data. To access the value of any column in a row, Oracle has to first
examine the length bytes of all the preceding columns. This is a very
quick and efficient operation, but it is done with such frequency that
it nevertheless does have an impact on performance.
....
This demonstrates that it took more than 10% longer to access the 10th
column in this table than it did to access the 1st column. The
principle is simple. Place frequently accessed columns early in the
table column order. Remember that column values will normally be
accessed more frequently for WHERE clause predicate evaluation, than
for select-list expression evaluation. However, column values that
appear in important driving predicates may be accessed relatively
infrequently if the table is consistently accessed by rowid via an
index on that column. In particular, primary key columns are seldom
the most intensively accessed table columns, and should not normally
be first in the table column order.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pa...@hpdbe.com
Available for short-term and long-term contracts
Chris Weiss
on benchmarks.
--
~~~~~~~~~~~~~~~~
Chris Weiss
ch...@hpdbe.com
www.hpdbe.com
High Performance Database Engineering
~~~~~~~~~~~~~~~~
"J.P." <jp_bo...@yahoo.com> wrote in message
news:7e388bc3.02050...@posting.google.com...
Pete Sharman
says...
The one thing I forgot to add on to this last night was that there were lots of
things I'd tune with a badly performing database before worrying about
reordering tables to match queries (which might be impossible anyway if you have
queries with different requirements).
Daniel Morgan
Thanks. And good information.
But of every 1,000 people wanting to re-order a table. One does it for
performance and 999 do it because it makes them feel good.
Daniel Morgan
Steven Healey
> You gott'a stop thinking in non Relational-DB style...
>
> The physical location of a column is totally irrelevant in a RDBMS.
>
> JP
>
> leiw...@hotmail.com (lei wang) wrote in message news:<af074b9c.02043...@posting.google.com>...
>
>>Is there an easy way to add a new column into the middle of an
>>existing table?
It is not however irrelevant to users of GUI-based tools such as Crystal
Reports - particularly novice or light users. Typically in such tools
columns can be displayed in either the natural order as stored in the
database or in alphabetical order. However, the majority of reports hit
a few key columns, and when a packaged software vendor adds a column at
the end of the table that is often used with the first few columns the
result is a pain in the wrist for the tool user.
Experienced users may know methods for twisting column ordering around
in report writers (and similar tools) to meet our needs, but it is still
a pain.
sPh
Jim Kennedy
does not read a part of a block.
Jim
"D.Y." <dyo...@aol.com> wrote in message
news:f369a0eb.02050...@posting.google.com...
Bill Garcia
in another database instance.
rename old_table to old_table_bak;
copy from user/password@sid --
to user/password@sid --
create old_table --
using --
select "column list with new column in order you want" --
from old_table_bak;
Of course you will lose the indexes and/or constraints on the table. So,
they will have to be recreated.
Bill Garcia
Tampa Florida
"lei wang" <leiw...@hotmail.com> wrote in message
news:af074b9c.02043...@posting.google.com...
Howard J. Rogers
If you have to do it, you have to do it. But it's expensive, and if you can
avoid doing it, it's better to.
Regards
HJR
"Bill Garcia" <garc...@verizon.net> wrote in message
news:g52A8.28157$iJ.1...@nwrddc02.gnilink.net...
J.P.
While I certainly can see your idea, I still think that
users/developers that base their reports/GUI's/etc upon the physical
structure of a DB are in for a surprise in the long run: "Stuff moves
around".
I always insist that the users/developers that work on my DB's NEVER
EVER use anything positionally, even in inserts with all the columns.
JP
Steven Healey <sphe...@worldnet.att.net> wrote in message news:<3CD09721...@worldnet.att.net>...
Malcolm Dew-Jones
: Steven,
: While I certainly can see your idea, I still think that
: users/developers that base their reports/GUI's/etc upon the physical
: structure of a DB are in for a surprise in the long run: "Stuff moves
: around".
users/developers *don't* base their reports upon the physical structure of
the DB - the GUI tools do.
The user then has to spent time rearranging the report or screen to make
it look correct.
It just makes it much faster for the developer if the order of the columns
is prearranged to work well for the anticipated reports and screens.
A trick around this is to define temporary views that have the order and
formatting that you want, and after the report has been generated then
change the SQL it uses to use the correct table instead of the temporary
view.
M Buckle
apart from efficiency condiderations, similar kinds of things should
group together if only for the sake of the poor s*d doing maintenance
work who is trying to understand the structure and meaning of some
under-documented system. If foreign keys are to be found together
near the head of the table, date and timestamps together at the
bottom, it makes things simpler in the long-run, or perhaps you would
disagree with such notions along with the use of column naming
conventions, use of consistent data types etc etc.
Sorry if that sounded like a rant, but your reply sounded like a
smart-a*se put-down.
Cheers, M
jp_bo...@yahoo.com (J.P.) wrote in message news:<7e388bc3.02050...@posting.google.com>...
TurkBear
I dont know what GUI tools you are using, or how you design reports, but the order of the fields in a table is not
related to their arrangement in the report in any reporting tool I've used...
yf...@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
-----------== Posted via Newsfeeds.Com - Uncensored Usenet News ==----------
http://www.newsfeeds.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Ulimited Fast Downloads - 19 Servers =-----
Daniel Morgan
Anyone that thinks about reordering columns in a production database should consider the implications on
everything from SQL*Loader scripts to insert statements. And the fact that they have, by virtue of this activity,
cost any serious company a substantial sum of money in testing for a near gratuitous change.
I will not believe that anyone has ever encountered a database with performance problems where the solution was
reordering columns. If there is a performance improvement, something of which my own cursory testing has yet to
demonstrate, it must be insignificant.
Daniel Morgan
Steven Healey
> I dont know what GUI tools you are using, or how you design reports, but the order of the fields in a table is not
> related to their arrangement in the report in any reporting tool I've used...
Well, Crystal Reports comes to mind. I guess that isn't too widely used
though ;-).
Not the order that the fields appear in the report, but the order in
which they appear in the "field chooser" (or whatever that tool calls
it) is what is in question here. The tool then generates SQL which is
of course order-independent.
sPh
TurkBear
my users do not find it too onerous to scroll down the list of available fields ..
.( maybe I have shouldn't have beaten them so much to get them to cooperate ;-) )
For those who do, I create views for them, or I use the Dictionary tool to create subsets of columns for them to use instead
of directly accessing the underlying table(s)
Steven Healey <sphe...@worldnet.att.net> wrote:
-----------== Posted via Newsfeeds.Com - Uncensored Usenet News ==----------
A.Liggins
table :-
storage... if you will then populate the new columns (via an update)
then you end up with row migration (i.e. is grown to big to fit back in
where it lived in the block and will get moved (leaving behind a forwarding
address as rowids dont change). If the table is wide you may then also get
row chaining. This will impact performance.
queries... if you have any hardcoded select * from xxx then you may get
more than you wanted.
existing inserts should be okay as long as the columns are nullable.
Recommend :-
Just add the columns at the end and test everything works afterwards
OR
Recreate the table (investigate what it has hanging off it
(triggers/fks/indexes/grants etc), export it first.... and then test.
Alun
"stinky" <stan...@stunkitystunk.org> wrote in message
news:3CCF3CA7...@stunkitystunk.org...