This content has been marked as final. Show 13 replies
Don't think so. Why does the column order matter?
to change the order of columns in an existing tableYou can SELECT them in whatever order you need. Re-arranging them physically is pointless. (SELECTing them in some order is pointless too).
Unfortunately I have had to do this in the past:
Don't drop your table of course, but I wanted this to be complete:
DROP TABLE REORDER;
CREATE TABLE REORDER
Now if you select it the columns come up in the order it was created
SELECT * FROM REORDER;
You can simply select the columns in the order you want:
SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;
You can create a view that the users can use if you wish:
CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;
SELECT * FROM ORDERED_REORDER;
DROP VIEW ORDERED_REORDER;
I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn't do this without some thorough testing.
change the name of the table
RENAME REORDER TO ORIG_REORDER;
create a view to look like the original table
CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER;
Now the view looks the way you want:
SELECT * FROM REORDER;
You can insert etc:
INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6)
Some apps won't allow this, they need tables and won't allow views.
Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn't a better way to handle the request.
Not in all cases. If we have chained rows and we have frequent queries which select a subset of columns across multiple blocks but which would theoretically fit into a single block then a physical re-arrangement might not be pointless.
Re-arranging them physically is pointless.
I agree that the number of circumsatnces in which this applies is few but maybe the OP is in that pinch. The only option beside drop and recreate is online redefinition but I'm not sure whether that's practical with 100m rows. Never done anything like that myself. Might be worth a punt though...
Thanx for quick reply!
Some background information. I am using IOT in Oracle 10. The table I am talking about could potentially have more than 255 columns.
As far as I know populated columns in IOTs after column number 255 are automatically written to the table's overflow segment. For optimal IO performance I would like to re-order the tables' column such that columns with number >=255 are not populated.
Pls correct me if my assumption is wrong.
You have a multi-multi-million row IOT with more than 255 columns, not all of which are used????
That's right, more than 255 columns oracle consider it as one more segment.
Not in all cases.OK.
My current client had such a problem a while back. The way they solved it was to create a new tablespace with a larger block size.
Behind the scenes redef must create another table last I used it.
It's not segment level however. It's seperate Row pieces.
Even that, there's no IO impact if your whole row fit into one block as concept mentioned.
Row Format and Size
Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.
When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.
also something about column order
The column order is the same for all rows in a given table. Columns are usually stored in the order in which they were listed in the CREATE TABLE statement, but this is not guaranteed. For example, if a table has a column of datatype LONG, then Oracle always stores this column last. Also, if a table is altered so that a new column is added, then the new column becomes the last column stored.
In general, try to place columns that frequently contain nulls last so that rows take less space. Note, though, that if the table you are creating includes a LONG column as well, then the benefits of placing frequently null columns last are lost.
WOW GREAT explaination yingkuan, I also didn't know about this "intra-block chaining" funda and its really good to know this piece too.
lol, same here.
I won't research about the stuff if the OP haven't asked.
Haven't used any table has more than 255 columns anyway.
I'm sure they had some lively debates over that one ;)
My current client had such a problem a while back. The way they solved it was to
create a new tablespace with a larger block size.