利用sql改写去掉执行计划中的filter

由于涉及到一下敏感的数据，所以只能用试验环境演示，情况如下：

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;