Home » SQL & PL/SQL » SQL & PL/SQL » How to change the structure of a table permanently
How to change the structure of a table permanently [message #246045] Tue, 19 June 2007 11:33 Go to next message
sridhar.sangubhatla
Messages: 2
Registered: June 2007
Location: Madison
Junior Member
Hello,

I have a doubt regarding the structure of a table.
Here is an example.
Let us assume that an employees table is created with columns emp_id, job_id, dept_id and salary in that order.
Also, if we need another column (emp_name) be displayed in between emp_id and job_id, we add the column emp_name by altering the table and insert the values in the emp_name column.
Now, the query: 'select emp_id, emp_name, job_id from employees' will enable me to see the emp_name column between emp_id and job_id.
I am interested to know if the emp_name column can permanently be inserted between emp_id and job_id in the table structure itself. Is there a way to get the desired result?

Thanks.

[Updated on: Tue, 19 June 2007 11:34]

Report message to a moderator

Re: How to change the structure of a table permanently [message #246049 is a reply to message #246045] Tue, 19 June 2007 11:37 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Just format your select statement listing the columns you want in the order you want them.

[Updated on: Tue, 19 June 2007 11:37]

Report message to a moderator

Re: How to change the structure of a table permanently [message #246050 is a reply to message #246045] Tue, 19 June 2007 11:37 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
yes drop the columns and recreate it with proper position.
before this save your data to the test table.

[Updated on: Tue, 19 June 2007 11:38]

Report message to a moderator

Re: How to change the structure of a table permanently [message #246054 is a reply to message #246045] Tue, 19 June 2007 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Column order is meaningless in relational model.
Columns must ALWAYS be given in any SQL statement.

Regards
Michel
Re: How to change the structure of a table permanently [message #246055 is a reply to message #246045] Tue, 19 June 2007 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
If you think it matters you could create a VIEW with the columns in the desired order, but it REALLY does NOT matter.
Re: How to change the structure of a table permanently [message #246239 is a reply to message #246055] Wed, 20 June 2007 07:07 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Possibly minor but interesting point-
It might matter (depending on what you consider to be significant:

A colleague of mine was working on an ETL project where a staging table was constructed so that there would be several columns 'at the end' of the definition that would be NULL at a specific stage (approximately 30 columns), and only the first, say 20 columns would actually hold values. This worked fine and at a later stage more columns would be populated. However, one of the DBAs also working on the sytem, decided to change the order of the columns (I don't actually know why, but I'm sure he had a reason at the time) The result of changing the column order meant that 1 populated column ended up at the end of the definition.
So, we now have a situation of 19 populated columns, 30 nulls and 1 populated column. The table grew in size quite significantly due to the fact that apparently, if a row has only NULLs as the trailing columns then these NULLS take up no space, however if there are any values to the 'right' of these nulls, each null column is assigned a size of 1 byte, for each row.
I probably haven't explained that all that well, here is a quick cut and paste from sql dev
SET ECHO ON

DROP TABLE ordertest

DROP TABLE ordertest succeeded.
DROP TABLE ordertest2

DROP TABLE ordertest2 succeeded.
CREATE TABLE ordertest (a number
                      , b number
                      , c number
                      , d number
                      , e number
                      , f number
                      , g number)

CREATE TABLE succeeded.
begin 
  for i in 1..100 loop 
    insert into ordertest (a) values(1);
  end loop;
end;

anonymous block completed
CREATE TABLE ordertest2 AS SELECT * FROM ordertest

CREATE TABLE succeeded.
ANALYZE TABLE ordertest COMPUTE STATISTICS

ANALYZE TABLE ordertest succeeded.
SELECT  AVG_ROW_LEN 
FROM user_tables 
WHERE table_name = 'ORDERTEST'

AVG_ROW_LEN            
---------------------- 
6                      

1 rows selected

UPDATE ordertest SET g = 1

100 rows updated
ANALYZE TABLE ordertest COMPUTE STATISTICS

ANALYZE TABLE ordertest succeeded.
SELECT  AVG_ROW_LEN 
FROM user_tables 
WHERE table_name = 'ORDERTEST'

AVG_ROW_LEN            
---------------------- 
14                     

1 rows selected

ANALYZE TABLE ordertest2 COMPUTE STATISTICS

ANALYZE TABLE ordertest2 succeeded.
SELECT  AVG_ROW_LEN 
FROM user_tables 
WHERE table_name = 'ORDERTEST2'

AVG_ROW_LEN            
---------------------- 
6                      

1 rows selected

UPDATE ordertest2 SET b = 1

100 rows updated
ANALYZE TABLE ordertest2 COMPUTE STATISTICS

ANALYZE TABLE ordertest2 succeeded.
SELECT  AVG_ROW_LEN 
FROM user_tables 
WHERE table_name = 'ORDERTEST2'

AVG_ROW_LEN            
---------------------- 
9                      

1 rows selected


(Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)

Previous Topic: Table changes capture through triggers
Next Topic: Deletion of record in a view reflecting in base table
Goto Forum:
  


Current Time: Sat Dec 03 14:11:40 CST 2016

Total time taken to generate the page: 0.04492 seconds