Oracle learning Summary (2)

Document directory

• Oracle learning Summary (2)

Oracle learning Summary (2)

The main content of this article includes: Implement Auto-incremental ID in Oracle and delete repeated records in the data table
I. Auto-incrementing Id 1. First create Sequence
Create sequence seqmax increment by 1
2. Get an ID
Select seqmax. nextval ID from dual
3. to delete a sequence
Drop sequence seqmax;

2. Delete duplicate records in a data table 1. Create a table create table "apptest "(
"ID" integer primary key not null,
"Mobile" nvarchar2 (50) not null
);

2. Assume that there are a large number of duplicate mobile phone numbers. To delete duplicate records, you can use either of the following methods:
(1) Delete from apptest A where rowid not in (select max (rowid) from apptest B where a. Mobile = B. Mobile) using rowid );

It is said that this method is inefficient when the data volume is large.
(2) Use the analysis function Delete apptest where rowid in (
Select rid from
(Select rowid RID, row_number () over (partition by mobile order by id desc) Rn from apptest)
Where rn> 1 );

(3) create a temp table