sql 去重并统计总数_记一次sql优化案例的模拟

         偶然间看到老猫多年前的优化视频，按照惯例，模拟走起。

---

• 构建测试环境及数据(oracle 11.2.0.4)       

drop table t; create table t(location varchar2(20),amount number,c1 number,c2 number,c3 number,salename varchar2(20)); begin   for i in 1 .. 1000000 loop     insert into T(location,amount,c1,c2,c3,salename) values (        trunc(i/140),        i,        trunc(i/10001),        trunc(i/10001),        trunc(i/10001),        i        );    commit;   end loop; end; / begin    for i in 1 .. 7000 loop       update  T set location=i where amount=i;       commit;    end loop; end; / exec dbms_stats.gather_table_stats(ownname=>'cbo',tabname=>'T',estimate_percent=>100,no_invalidate=> false);

select column_name,num_distinct from user_tab_columns where table_name = 'T'; select num_rows from user_tables where table_name='T';

好了，接下来我们执行一条sql语句：

select * from t a       where amount > (select avg(amount) from t b where a.location = b.location and c1 = 0 and c2 = 0 and c3 = 0) and c1 = 0 and c2 = 0 and c3 = 0;

       当看到这个真实的执行计划时，很明显，该sql语句仅返回2790行，却要花费5分多钟，并且执行计划第2、4步中的全表扫描，oracle评估的行数都是1，而这和真实的行数差1万倍。哦既然是oracle对cardinality评估不准，那么应该是统计信息的问题，然而手动收集统计信息之后，再次执行依然是这个结果。

       我们再好好看看这个执行计划，最耗时的步骤是第四步，这是一个filter类型的执行计划，他的特点是外层返回多少行，内层就要被执行多少次，而执行计划第二步真实返回行数为10000行，那么就意味着第四步要被重复执行10000次，但filter和NL的区别在于，filter会额外做一个去重的操作，这也是为什么外层返回10000行，内层重复启动只有7000次的原因，下面我们验证一下：

      其实每次我在看到filter类型的执行计划时，我都在想，卧槽真倒霉，怎么走了filte