105-跟专家学习SQL优化-1 原创老虎刘老虎刘谈oracle性能优化

〇〇 · 发表于 2023-7-18 19:21

idno contno validstate的不同值各有多少

newkid · 发表于 2023-7-18 21:51

sql_tigerliu 发表于 2023-7-18 13:58
这种情况, 12.2 以上版本的改写也很简单, 之前的版本稍复杂一点.

我想这么改：
select min(priority)
from XXXPERSON t
where (t.idno,t.validstate) in
  (
         select m.idno,'1'
         from LCAPPNT m
         where m.contno = '880037131628'
         union all
         select n.idno,'2'
         from LCINSURED n
         where n.contno = '880037131628'
  )
and t.validstate in ('1','2') ---- 这个条件也可以不加

不知道你说的简单和复杂的改法又是怎样的？

sql_tigerliu · 发表于 2023-7-18 22:18

newkid 发表于 2023-7-18 21:51
我想这么改： select min(priority) from XXXPERSON t where (t.idno,t.validstate) in ( ...

苏老师的改写比较巧妙

12.2+ 把 t.validstate 从子查询里面提出来, 加上or_expand 就可以
  select /*+ or_expand */*
from XXXPERSON t
where
   ( t.validstate = '1'  and
      exists
         (select 1
         from LCAPPNT m
         where m.idno  = t.idno
         and m.contno = '880037131628'
         )
   )
   or
   (  t.validstate = '2'  and
      exists
         (select 1
         from LCINSURED n
         where  n.idno  = t.idno
         and n.contno = '880037131628'
         )
   );

12.2- , 这种写法稍复杂, 但是通用性更强一些, 如果主表对两个子查询用的不都是t.validstate 字段, 也适用,这个也是or_expand的查询转换逻辑:
select  min(priority)
from
(
select priority
from XXXPERSON t
where
      exists
         (select 1
         from LCAPPNT m
         where m.idno  = t.idno
         and m.contno = '880037131628'
         and t.validstate = '1'
         )
union all
select priority
from XXXPERSON t
where exists
         (select 1
         from LCINSURED n
         where  n.idno  = t.idno
         and n.contno = '880037131628'
         and t.validstate = '2'
         )
   and lnnvl( exists
            (select 1
               from LCAPPNT m
               where m.idno  = t.idno
               and m.contno = '880037131628'
               and t.validstate = '1'
            )
            )
);

〇〇 · 发表于 2023-7-19 05:12

lnnvl的操作其他数据库没有

sql_tigerliu · 发表于 2023-7-19 07:47

〇〇发表于 2023-7-19 05:12
lnnvl的操作其他数据库没有

其他数据库没有的东西还挺多. 按照这个思路, 再改改总是可以的

〇〇 · 发表于 2023-7-19 08:10

造数据测试了一下，各种写法都差不多
create table xxxperson(idno int , validstate char(1),priority int,idtype int);
create index idx_xxxperson_idno on xxxperson(idno);

create table lcappnt(idno int,idtype int,contno varchar(30));
create index idx_lcappnt_contno on lcappnt(contno);

create table lcinsured(idno int,idtype int,contno varchar(30));
create index idx_lcinsured_contno on lcinsured(contno);

insert into xxxperson select i,i%7,i%3,i%3 from range(1000000)t(i);
insert into lcappnt select i,i%3,i+880037100000 from range(1000000)t(i);
insert into lcinsured select i,i%3,i+880037100000 from range(1000000)t(i);

--liu 1
  select min(priority)
from XXXPERSON t
where
  (
      exists
         (select 1
         from LCAPPNT m
         where m.idno  = t.idno
         and m.contno = '880037131628'
         and t.validstate = '1'
         )
   or exists
         (select 1
         from LCINSURED n
         where  n.idno  = t.idno
         and n.contno = '880037131628'
         and t.validstate = '2'
         )
  );

---newkid
select min(priority)
from XXXPERSON t
where (t.idno,t.validstate) in
  (
         select (m.idno,'1')
         from LCAPPNT m
         where m.contno = '880037131628'
         union all
         select (n.idno,'2')
         from LCINSURED n
         where n.contno = '880037131628'
  );

--liu 2
select /*+ or_expand */min(priority)
from XXXPERSON t
where
   ( t.validstate = '1'  and
      exists
         (select 1
         from LCAPPNT m
         where m.idno  = t.idno
         and m.contno = '880037131628'
         )
   )
   or
   (  t.validstate = '2'  and
      exists
         (select 1
         from LCINSURED n
         where  n.idno  = t.idno
         and n.contno = '880037131628'
         )
   );

--liu 3
select  min(priority)
from
(
select priority
from XXXPERSON t
where
      exists
         (select 1
         from LCAPPNT m
         where m.idno  = t.idno
         and m.contno = '880037131628'
         and t.validstate = '1'
         )
union all
select priority
from XXXPERSON t
where exists
         (select 1
         from LCINSURED n
         where  n.idno  = t.idno
         and n.contno = '880037131628'
         and t.validstate = '2'
         )
   and ( exists
            (select 1
               from LCAPPNT m
               where m.idno  = t.idno
               and m.contno = '880037131628'
               and t.validstate = '1'
            ) is null
         or not
            exists
            (select 1
               from LCAPPNT m
               where m.idno  = t.idno
               and m.contno = '880037131628'
               and t.validstate = '1'
            )
            )
);

sql_tigerliu · 发表于 2023-7-19 08:35

〇〇发表于 2023-7-19 08:10
造数据测试了一下，各种写法都差不多create table xxxperson(idno int , validstate char(1),priority int, ...

卢大师, 我不知道你用的是什么数据库, 我们之前的讨论范围还是局限在oracle数据库. 在oracle数据库中, 这些不同的写法, 执行效率的差别是比较大的. 我没有造数据, 根据表的大小, 通过执行计划基本就能看出好坏. 你说的差不多我不知道是都快还是都慢, 如果是都快, 那么恭喜, 这个数据库的优化器比oracle还强. 如果都慢也正常, 不过100万的数据量, 也慢不到哪里. **是能比较一下执行计划.

sql_tigerliu · 发表于 2023-7-19 08:36

最x好居然也成了敏感词, 日了鬼了

〇〇 · 发表于 2023-7-19 08:46

本帖最后由〇〇于 2023-7-19 08:47 编辑

sql_tigerliu 发表于 2023-7-19 08:35
卢大师, 我不知道你用的是什么数据库, 我们之前的讨论范围还是局限在oracle数据库. 在oracle数据库中, 这些 ...

duckdb数据,1000万也还可以，这个不了解原始数据分布，造的和实际没法比较

insert into xxxperson select i,i%7,i%3,i%3 from range(9000000)t(i);
insert into lcappnt select i,i%3,i+880037100000 from range(9000000)t(i);
insert into lcinsured select i,i%3,i+880037100000 from range(9000000)t(i);

--liu 1
Run Time (s): real 9.256 user 17.113310 sys 6.068439
---newkid
Run Time (s): real 0.122 user 1.279208 sys 0.000000
--liu 2
Run Time (s): real 0.10 1 user 1.138807 sys 0.000000
--liu 3
Run Time (s): real 2.806 user 30.248594 sys 5.**233

sql_tigerliu · 发表于 2023-7-19 10:02

〇〇发表于 2023-7-19 08:46
duckdb数据,1000万也还可以，这个不了解原始数据分布，造的和实际没法比较insert into xxxperson select i, ...

这个明显能看出区别了. liu1是原始写法,只能走filter的执行计划,效率不行; 苏老师和liu2(duckdb也能自动做or_expand, 优化器水平还可以) 这两种写法的效率是可以的. liu3的写法, 可能你只加个not exists就行了, 不用再exists了, lnnvl其实就是求反, 只不过增加了null的处理)

[转载] 105-跟专家学习SQL优化-1 原创 老虎刘 老虎刘谈oracle性能优化

[转载] 105-跟专家学习SQL优化-1 原创老虎刘老虎刘谈oracle性能优化