表a(datetime date primary key)
表b(字段1 varchar2(20) primary key,
datetime date,
bigfiled blob)
假设表a有八万表记录
表b有24万条记录
现在我想实现的delete表a的datetime字段值在表b中不存在的记录。必须考虑到效率的问题。
表b(字段1 varchar2(20) primary key,
datetime date,
bigfiled blob)
假设表a有八万表记录
表b有24万条记录
现在我想实现的delete表a的datetime字段值在表b中不存在的记录。必须考虑到效率的问题。
delete from a where not exists(select 1 from b where a.datetime=b.datetime)
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
WHERE u2.date=u1.date );
-- 就算搞错了,还有必要问题吗?不是一样:表a、表b调个个儿就OK了?
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
WHERE u2.date(+)=u1.date and u2.date is null);
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);DELETE FROM b u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM a u2
WHERE u2.datetime=u1.datetime );
create table a as select distinct datetime as datetime from b;
alter table b add primary key a_pk on a(datetime);
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);DELETE FROM b u1
WHERE NOT EXISTS ( SELECT DISTINCT u2.datetime FROM a u2
WHERE u2.datetime=u1.datetime );