Basic issues such as Oracle de-weight

--Go to re-query method one: based on ID

SELECT * from Sxe where ID in (the Select min (id) from SXE Group by username) Order by ID ASC;
--Go to re-query method two: according to RowNum
SELECT * FROM (select S.*,rownum rn from SXE s) x where X.rn in (select min (rownum) from SXE Group by username) Order BY ID ASC;
--Go to re-query method three: according to rowID
SELECT * FROM (select S.*,rowid rids from Sxe s) x where X.rid in (select min (rowid) from SXE Group by username) Order by I d ASC;
Select S.*,rowid from Sxe s where rowID in (select min (rowid) from SXE Group by username) Order by ID ASC;


--Deduplication method one: based on ID
Delete from Sxe where ID not in (the Select min (id) from SXE Group by username);
--Deduplication method two: according to RowNum
--delete from (select S.*,rownum from Sxe s) x where rownum not in (select min (rownum) from SXE Group by username);
--Deduplication method Three: according to rowID

Delete from Sxe where rowID not in (select min (rowid) from SXE Group by username);

DISCTINCT keyword Multi-column problem:

About data migration, how to deal with big data volume duplication problem? For Oracle

CREATE TABLE table1 Selet * from table2; --Create Table1 according to table2 structure and import table2 data into table1;

CREATE TABLE table1 SELECT * from table2 where 1 = 2; --Create Table1 According to the structure of table2, but do not import data;

In the development process, deletion can be performed with simple SQL if the amount of data involved is small. But the data volume is very big migration, the millions level data quantity, the performance is the bottleneck occurrence point;

Because the query takes time, the deletion takes time, and the execution of the transaction takes time, so the performance is basically zero, the database is not fake, even the computer suspended animation.

My experience with big data migrations is:

CREATE TABLE temp_table SELECT * FROM table2 where ID not in (the Select min (id) from the Table1 Group by Coln) Order by coln ASC;

drop table table2;

Rename temp_table to Table2;

About database table structure editing, for Oracle:

Add Columns:

ALTER TABLE table1 add column_name column_type;

To modify the column size:

ALTER TABLE table1 Modify COLUMN_NAME new_column_type;

To modify a column name:

ALTER TABLE table1 Rename column cln1 to CLN2;

To delete a column:

ALTER TABLE TABL1 drop column cln1;

Basic issues such as Oracle de-weight