Home » SQL & PL/SQL » SQL & PL/SQL » add field at position 144
add field at position 144 [message #253235] Mon, 23 July 2007 04:23 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

With the code below, the field P1 will be added at the very end of the table:

alter table TAB
add P1 integer


My table TAB has 300 fields. How can I insert the new parameter at the position 144 (P1 is then between other fields and not at the end)?

Thanks
Stefan

[Updated on: Mon, 23 July 2007 04:24]

Report message to a moderator

Re: add field at position 144 [message #253238 is a reply to message #253235] Mon, 23 July 2007 04:30 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Take the backup of your current table... delete the current table. Recreate the table according to your column preferences ...copy the backup data

Re: add field at position 144 [message #253272 is a reply to message #253238] Mon, 23 July 2007 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A much better solution would be to simply not bother and spend the rest of the day enjoying the weather.
Re: add field at position 144 [message #253283 is a reply to message #253235] Mon, 23 July 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you should never use "select *" or insert without naming the column, who cares?

Regards
Michel
Re: add field at position 144 [message #253298 is a reply to message #253235] Mon, 23 July 2007 07:29 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why in the world does this EVER matter ??
Re: add field at position 144 [message #253299 is a reply to message #253272] Mon, 23 July 2007 07:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Frank wrote on Mon, 23 July 2007 12:08
A much better solution would be to simply not bother and spend the rest of the day enjoying the weather.

Reminded me of:
"I'm drinking wine and eating cheese, and catching some rays, you know."

Bonus points for naming the film.

I would also say, that the order of columns is not entirely insignificant (but I'd imagine it's fairly rare for even this to be a real issue)

SQL> DROP TABLE ord1;

Table dropped.

SQL> DROP TABLE ord2;

Table dropped.

SQL> DROP TABLE ord3;

Table dropped.

SQL>
SQL> CREATE TABLE ord1 AS
  2  SELECT rownum rn,
  3       object_type,
  4       object_name
  5  FROM all_objects;

Table created.

SQL> ALTER TABLE ord1 ADD a VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD b NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD c DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD d NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD e DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD f NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD g VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD h NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD i VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD j NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD k DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD l NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD m DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD n NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD o VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD p NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD q VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD r NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD s DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD t NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD u DATE;

Table altered.

SQL> ALTER TABLE ord1 ADD v NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD w VARCHAR2(10);

Table altered.

SQL> ALTER TABLE ord1 ADD x NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD y NUMBER;

Table altered.

SQL> ALTER TABLE ord1 ADD z NUMBER;

Table altered.

SQL>
SQL> CREATE TABLE ord2 AS
  2  SELECT *
  3  FROM ord1;

Table created.

SQL>
SQL> CREATE TABLE ord3 AS
  2  SELECT *
  3  FROM ord1;

Table created.

SQL>
SQL> SELECT segment_name,
  2       bytes / 1024 / 1024,
  3       blocks,
  4       extents
  5  FROM dba_segments
  6  WHERE segment_name IN('ORD1','ORD2','ORD3');

SEGMENT_NA BYTES/1024/1024     BLOCKS    EXTENTS
---------- --------------- ---------- ----------
ORD2                     3        384         18
ORD3                     3        384         18
ORD1                     3        384         18

SQL>
SQL> UPDATE ord1
  2  SET h = 1;

50112 rows updated.

SQL>
SQL> UPDATE ord2
  2  SET a = 1;

50112 rows updated.

SQL>
SQL> UPDATE ord3
  2  SET z = 1;

50112 rows updated.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT segment_name,
  2       bytes / 1024 / 1024,
  3       blocks,
  4       extents
  5  FROM dba_segments
  6  WHERE segment_name IN('ORD1','ORD2', 'ORD3');

SEGMENT_NA BYTES/1024/1024     BLOCKS    EXTENTS
---------- --------------- ---------- ----------
ORD2                     3        384         18
ORD3                     5        640         20
ORD1                     4        512         19

Re: add field at position 144 [message #253312 is a reply to message #253299] Mon, 23 July 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But is really this the thought of the OP?
Take care of rules first, after that you can optimize storage.
Former is for the developer, latter is for DBA.

Regards
Michel
Re: add field at position 144 [message #253323 is a reply to message #253312] Mon, 23 July 2007 09:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
But is really this the thought of the OP?

I don't know, do you?
Quote:
...Former is for the developer, latter is for DBA.

So is the OP a DBA or Developer? Has the developer taken care of the rules and the OP is now trying to optimize the storage? Or have you assumed that the OP is a developer and he just wants the column to be in a specific position so that his select * works the way he thinks it should? (I agree that this probably is the case). I was simply pointing out that the commonly made comment, that column order in a table is inconsequential, is not necessarily always true.

Re: add field at position 144 [message #253339 is a reply to message #253323] Mon, 23 July 2007 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know, do you?

Statistically speaking, yes. Wink

Quote:
the commonly made comment, that column order in a table is inconsequential, is not necessarily always true

Maybe this is because I'm a french guy and in french language we have rules that we must learn, then we have exceptions to the rules that we must learn, then we have exceptions to the exceptions that we must learn, I don't remember if you have a fourth level but it would not surprise me.
So I say the first level, if OP replies with a "but in some cases..." then I give the next deeper step and so long. If he does not I assume he does not know, does not want to know or does not care...

I so often saw people remembering just the last part because it gives an argument for not remembering the first part and say to the DBA we want this because sometimes...

Regards
Michel
Re: add field at position 144 [message #253350 is a reply to message #253299] Mon, 23 July 2007 10:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Mon, 23 July 2007 14:30
Bonus points for naming the film.


I must admit, I did not know, but after pointing people to the goodies of Google so many times...
Re: add field at position 144 [message #253510 is a reply to message #253235] Tue, 24 July 2007 02:46 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@Frank It's good to show that you practice what you preach Smile

@Michel All valid points Michel, I just thought I'd stick my big nose in Smile
Previous Topic: How to select and insert and use the value to select again
Next Topic: I don't want to raise NO-DATA-FOUND. How to do that? (split)
Goto Forum:
  


Current Time: Sun Dec 04 14:46:18 CST 2016

Total time taken to generate the page: 0.08192 seconds