OceanBase 金融项目优化案例

领导让我帮忙支持下其他项目的SQL优化工作，呦西，是收集案例的好机会。😍

下面SQL都是在不能远程的情况下，按照原SQL的逻辑等价改写完成发给现场同学验证。

---

案例一

慢SQL，4.32秒：

SELECT MY_.*, RM
FROM (SELECT ROWNUM RM, V_.*
      FROM (SELECT *
            FROM (select count(1)        processidnum,
                         t.processid,
                         t.proc_name_ as procname
                  FROM tkdkdkdk t
                  WHERE 1 = 1
                    and (t.ASSIGNEE_ = 'server' or exists(select 1
                                                          FROM pepepep p
                                                          WHERE p.task_ = t.ID_
                                                            and (p.agent_userid_ = 'server' or
                                                                 (substr(p.groupid_, 6) in
                                                                  (select role_code
                                                                   FROM upupupup
                                                                   WHERE user_code = 'server') or
                                                                  p.userid_ = 'server'))))
                  GROUP BY t.processid, t.proc_name_)) V_
      WHERE ROWNUM <= 100000) MY_
WHERE RM >= 1;

慢SQL执行计划：

---

 改写优化，445ms：

SELECT *
FROM (SELECT *
      FROM (SELECT a.*,
                   rownum rn
            FROM (SELECT count(1)        processidnum,
                         t.processid,
                         t.proc_name_ AS procname
                  FROM tkdkdkdk t
                           LEFT JOIN
                       (SELECT distinct p.task_
                        FROM pepepep p
                                 LEFT JOIN
                             (SELECT role_code
                              FROM upupupup
                              WHERE user_code = 'server'
                              GROUP BY role_code) tsu
                             ON (substr(p.groupid_, 6) = tsu.role_code)
                        WHERE (p.agent_userid_ = 'server'
                            OR (tsu.role_code is NOT null
                                OR p.userid_ = 'server'))) x
                       ON t.ID_ = x.task_
                  WHERE 1 = 1
                    AND (t.ASSIGNEE_ = 'server'
                      OR x.task_ is NOT NULL)
                  GROUP BY t.processid, t.proc_name_) a)
      WHERE rownum <= 100000)
WHERE rn >= 1; 

改写优化后执行计划：

 优化思路：

　　1、原SQL有很多子查询，可能会导致计划走NL，改成JOIN后让CBO自动判断是否走HASH还是NL。

　　2、换了个标准的分页框架。

---

 案例二

慢SQL，2.6秒：

SELECT MY_.*, RM
FROM (SELECT ROWNUM RM, V_.*
      FROM (SELECT *
            FROM (select t.*, t.org_code || '-' || t.org_name as codename
                  FROM (select tc.*
                        FROM tgtgtgtg tc
                        start with TC.ORG_ID = '6000001'
                        connect by prior ORG_ID = tc.parent_id) t
                  WHERE org_level <= 3
                  ORDER BY CASE
                               WHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASE
                                                                                                            WHEN length(nvl(org_order, '')) = '9'
                                                                                                                then org_order || ''
                                                                                                            else '1' || org_code end
                               when length(nvl(org_order, '')) = '9' then '99999999' || org_order || ''
                               else '999999991' || org_code end)) V_
      WHERE ROWNUM <= 10) MY_;
WHERE RM >= 1;