版本

OS

版本

OS版本

[omm@mogdb-01 ~]$ lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.6.1810 (Core) 
Release:	7.6.1810
Codename:	Core

DB版本

stone=> select version();
                                                                     version                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 3.0.1 build eb4f387f) compiled at 2022-08-19 18:14:41 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

背景

为客户优化Oracle查询语句时，遇到一个分页语句。想测试在MogDB中的场景，因此在MogDB中做了模拟优化

案例模拟

所用数据

模拟数据来自Oracle中的字典表 dba_objects dba_users

原始语句

explain analyze
select * From
(
select rownum as rn,t.*
  from
(
select u.username,u.user_id,u.account_status,o.object_name,o.object_type
  from t_users u
  left join 
  (
  select o.owner,
         o.object_name,
         o.object_type,
         row_number() over (partition by o.owner order by o.object_id) as rn
    from t_objects o
  )o on (o.rn = 1 and o.owner = u.username)
 order by (case when u.account_status= 'LOCKED' then 1 else 0 end),u.username
)t
where rownum <= 10
)
where rn >= 6;