Is this the SQL if large amount of data, how to optimize efficiency?
The delete from table1 where pid not in (select id from table2)
Or
Select * from table1 where pid not in (select id from table2)

CodePudding user response:

Select * from table1 a where not eists (select 1 from table2 b where Amy polumbo id=b.i d)

CodePudding user response:

Select * from table1 a where not the exists (select 1 from table2 b where Amy polumbo id=b.i d)

CodePudding user response:

The delete mytable
Where pid in (
Select the id from t2
Minus
Select the pid from mytable
)

PS: multiple methods must be repeated testing,

CodePudding user response:

Create a temporary table, go index
The create table temp_1 as
The select/* + parallel (20), a */
Anderson, d
The from table2 a;

The create index idx_id on temp_1 (id) the parallel 20;

*/select/* + index (b)
*
The from table1 a
Where Amy polumbo id not the exists (select 1 from temp_1 b where Amy polumbo id=b.i d)

CodePudding user response:

Data in larger amounts in the not exists with a higher efficiency of oh
Select * from table1 a where not the exists (select 1 from table2 b where Amy polumbo id=b.i d)

CodePudding user response:

The total number of two tables according to quantity, also accord with the not in TABLE1 in TABLE2 this condition the amount of data of how many?

CodePudding user response:

Select * from table1 a where not the exists (select 1 from table2 b where Amy polumbo id=b.i d)
1. * replace the field name in table

CodePudding user response:

Use the left connection, very fast retrieval,
Select a t1. *
The from table1 t1
Left the join table2 t2
On t2. Id=t1. Pid
Where t2. The id is null

CodePudding user response:

Key to see the specific scene, clear was slow in what place,
Including data environment at that time, and then the execution plan, etc.,

CodePudding user response:

For the rowid delete data, if the data quantity is large,
1. Recommends that the index of other tables or delete the invalid
2. Through the rowid delete
3. All special data can consider direct access to the create table and then rname into the original table name
Delete from table1 t where rowid not in (select t2. The rowid from table2 t1, table1 t2 where t1. The pid=t2. The pid)
Or
Select * from table1 where pid not in (select id from table2)
If the table2 do not duplicate data, outer join directly by the
Select * from t. table1 t, table2 t1 where t.p id=t2. The id (+) and t2. Id is null.