In IDS?..SE?.. SQL Server, Oracle, MySQL yet others instantly place new rows in to the appropiate location within the datafile to keep the clustering.
The way in which Informix handles clustered indexes is as simple as repairing the table (and index) to ensure that the information within the table is incorporated in the correct physical sequence for that index at that time once the index is produced. After that, rows are placed wherever appears most suitable, which doesn't still preserve the clustered order. It has been the situation since (
Informix-SQL 1.10 in 1985) Informix-SQL 2.10 from 1986 (possibly 2.00 I do not possess a manual for your still) through Informix Dynamic Server 11.70 this year.
ALTER INDEX idxname TO NOT CLUSTERED;
is definitely extremely swift. The complementary statement:
ALTER INDEX idxname TO CLUSTERED;
is frequently a sluggish process, including developing a full latest version on the table and also the index before shedding that old table and index.
The ISQL 1.10 manual doesn't have ALTER INDEX the two.10 manual does has ALTER INDEX.
I can not answer for IDS however i can for many you pointed out.
It is dependent around the platforms: will it use pages and will it separate data from index tree?
Generally, physical ordering of rows isn't maintained: only logical ordering could be
Reason: you cannot "make room" on the fixed size page (as Bohemian recommended)
If you extend a row (eg increase the data to some lengthy varchar) or place among (
ID=3 between rows
id IN (2,4)) then your among the following happens
- row is removed to a different page with pointers
- row overflows (SQL Server 2005+ for instance)
- page is split
This leads to logical/index fragmentation and reduced data density (per page): and that's why we've index maintenance to get rid of this.
The reply is "yes". Anything of the clustered index would be that the DBMS keep your rows in physical order on disk that suits the index order.
Observe that this could make card inserts/updates costly.
The advantage is the fact that table scans (eg
select * from table) are came back in index order (usually), which could aid certain programs. Generally, they are not worthwhile IMHO.
EDIT: Apparently I wasn't obvious enough...
Like a natural results of following anything, should you produce a clustered index after the table is packed with data, the DBMS will reorganise the rows as needed. For big tables, this could take hrs. I labored with informix once and attempted this on the large table... it had been a tragedy.
Another natural consequence is when you place a rows that do not order in the finish on the table, the DBMS makes room within the I/O page it goes and puts it there. This might be costly indeed as theoretically every row might need to be shuffled along. Because of this, I'd only get a clustered index on either incrementing id column or perhaps a timestamp column that defaults to "now", to ensure that new rows belong in the finish from the data with no ordering is needed to place them but still obey anything.
Edited: I'll try to return to yourself on this. I understand about page splits - I had been certain informix reordered everything. Of course, it had been a couple of years back, but out-of-order place basically introduced the machine lower - it had been a tale. Obviously, we eventually changed it with mysql for production transactions and postgres for warehouse confirming.