SQL等价改写核心思想概述.pptx

SQL等价改写核心思想概述;;1;1：1 关系 两表关联返回1的关系，常见于对表做垂直拆分之后的两表关联(主键关联) 1：N 关系 两表关联返回N的关系 最常见的应用，比如一个商品(1)对应多个订单(N) N：N 关系 两表关联返回局部范围笛卡尔积，常见于半连接和反连接中(in,exists/not in,not exists);select count(*) from a left join b on a.id=b.id; 某运营商，2012年，Oracle10gR2 a：b是1：1关系，a和b都是上千万条数据，原始SQL要跑12秒 因为是left join，不管a和b是否能关联上，都会返回a的所有数据，b属于1的关系 不管有没有关联上，不影响最终count(*)的结果，所以SQL被改写为： select count(*) from a; 最终0.05秒就能返回结果 其实，只要b是1的关系，不管a是属于1的关系还是N的关系，都能这样改写 注：到了Oracle11g之后，CBO会自动改写上面SQL;1;;select * from dept where deptno in (select deptno from emp); dept与emp是1：N关系，上面查询只返回dept表的数据，也就是说返回的是1的关系 将上面查询改写为内连接的时候，可以将emp变成1的关系，再与dept关联;select * from dept where deptno in (select deptno from emp); dept与emp是1：N，上面查询只返回dept表的数据，也就是说返回的是1的关系 可以先让dept与emp关联变成N，再对N GROUP BY，将N变为1 select d.deptno, d.dname, d.loc from dept d, emp e ---1：N where d.deptno = e.deptno group by d.deptno, d.dname, d.loc; ---GROUP BY N 变成 1;select * from dept where deptno in (select deptno from emp); select d.deptno,d.dname,d.loc from (select d.*, (select 1 from emp e where d.deptno = e.deptno and rownum = 1) status from dept d) d where status = 1; 注：在实际工作中不要这样改写，这里只是为了演示;select * from dept where deptno in (select deptno from emp); select d.* from dept d left join (select deptno from emp group by deptno) e on d.deptno = e.deptno where e.deptno is not null; 注：在实际工作中不要这样改写，这里只是为了演示;create table a as select * from dba_objects;;;select count(distinct owner), count(distinct object_name) from a where owner in (select owner from b); 通过分析执行计划发现原始SQL被CBO改写为如下SQL： select count(distinct a.owner), count(distinct a.object_name) from a, b where a.owner = b.owner;;select count(distinct a.owner), count(distinct a.object_name) from a, b where a.owner = b.owner; 两个7W行的表关联要跑几十分钟只有一种可能，就是两个表是N：N关系(N：N关联返回局部范围笛卡尔积) 两表关联列是owner，确实是N：N关系，两表关联之后数据量有几十亿条 对几十亿条数据进行count(distinct)肯定要跑几十分钟;为避免出现N：N，将半连接从N的关系变成1的关系，所以将原始SQL改写： select count(distinct owner), count(distinct object_name) from a where owner in (select owner from b group by owner); select count(disti