Archives of the TeradataForum
Message Posted: Wed, 20 Aug 2003 @ 23:17:56 GMT
we are currently under V2R22.214.171.124.
Since V126.96.36.199 there is a new internal parameter "Rowid_enabled", which is FALSE by default.
This means: "select rowid, t01.* from t01" does not work anymore. The construction of a join index using rowid is still possible and will work.
If you set "Rowid_enabled" to TRUE, you can still "select rowid ..."
NCR plans are not to allow a "select rowid" from V2R5.1 and upwards for compatibilty reasons ..... One should use the new identity column, which is introduced in V2R5.
Many large tables are Multiset (performance reasons, etc ... ). Alter table to add an identity column is a.) not possible and b.) would suck for performance reasons anyway.
I think, the only way to add an identity column is an "insert into select *", which may not possible because of lack of free diskspace.
With rowid not possible anymore and no identity column, the only way to get rid of unwanted dupes would be:
1.) create table large_table_dupe as identity_col, *.large_table.
2.) Get the dupe records into large_table_dupe:
insert into large_table_dupe select identity_col , all_cols from ( select all_cols , count(*) from large_table group by all_cols having count(*) > 1 );
3.) delete all dupes in large_table_dupe:
delete from large_table_dupe where identity_col not in ( select max_id from ( select max(identity_col) max_id , all_cols from large_table_dupe group by all_cols ) t01 );
4.) delete all dupes from large_table:
delete from large_table where all_cols in ( select all_cols from large_table_dupe );
5.) Get some rows in again:
insert into large_table select all_rows from large_table_dupe;
My question: What do you think of it? This solution is not very straight forward.
I may be wrong ....
Any feedback appreciated....
makeITdone IT Services
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|