| add field at position 144 [message #253235] |
Mon, 23 July 2007 04:23  |
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 #253299 is a reply to message #253272] |
Mon, 23 July 2007 07:30  |
pablolee
Messages: 2882
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 #253323 is a reply to message #253312] |
Mon, 23 July 2007 09:10 |
pablolee
Messages: 2882
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | I don't know, do you?
|
Statistically speaking, yes. 
| 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 |
Frank
Messages: 7901
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...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
