由于涉及到一下敏感的数据,所以只能用试验环境演示,情况如下:
create table test as select * from dba_objects ;
create table test1 as select * from dba_objects ;
select *
from (select *
from test b
where created = (select max(created)
from test1 a
where a.object_name = b.object_name)
and last_ddl_time > sysdate) a,
(select *
from test b
where created = (select max(created)
from test1 a
where a.object_name = b.object_name)
and last_ddl_time < sysdate) b
where a.object_id = b.object_id;
执行计划如下:
select * from (select *
from test b,
(select object_name, max(created) created
from test1 a
group by object_name) a
where a.created = b.created
and a.object_name = b.object_name
and last_ddl_time > sysdate) a,
(select *
from test b,
(select object_name, max(created) created
from test1 a
group by object_name) a
where a.created = b.created
and a.object_name = b.object_name
and last_ddl_time > sysdate) b
where a.object_id = b.object_id;