对于rowid删除数据的,如果数据量比较大,
1.建议把其他表的索引删除或者无效
2.通过rowid删除
3.如数数据量特别大的时候可以考虑直接获取create table 然后rname成原来的表名
delete from table1 t where rowid not in (select t2.rowid from table2 t1,table1 t2 where t1.pid = t2.pid )
或
select * from table1 where pid not in(select id from table2)
如果table2没有重复数据的话,直接外连接就好了
select t.* from table1 t,table2 t1 where t.pid = t2.id(+) and t2.id is null;
创建临时表,走索引
create table temp_1 as
select /*+ parallel(a,20)*/
a.id
from table2 a;
create index idx_id on temp_1(id) parallel 20;
select /*+ index (b)*/
*
from table1 a
where a.pid not exists (select 1 from temp_1 b where a.pid = b.id)