115-在oracle数据库表现不佳的SQL,在oceanbase上会怎么样,来看看测试结果-2

老虎刘谈oracle性能优化 2024-09-25

115

接上篇 114-在oracle数据库表现不佳的SQL,在oceanbase上会怎么样,来看看测试结果-1

场景6: push_pred与row_number分析函数

select a.object_id,x.id,x.object_id,x.owner
from t1 a
left join 
(select b.*
    , row_number() over (partition by object_id order by id desc) as rn
from t10m b
)x
   on a.object_id=x.object_id and x.rn=1
order by a.created desc
offset 0 rows fetch first 20 rows only
;
复制

oracle的表现(18.83秒):

(如果要得到好的表现, 需要hint的配合, 这就是优化需要做的事情. )

OB的表现(0.04秒):

场景7: not in与union

select count(*) from 
t1 a
where object_id not in 
(select object_id from t10m 
union all
select object_id from t5m
);
复制

Oracle的表现:

预计要跑个把小时, 只能看看执行计划了:

(如果要想表现正常, 需要改写, 这就是优化需要做的事情)

OB执行时间1秒多, 这个表现才是正常的:

场景8: 连接消除

select count(*) 
from t1 a
left join (select object_id,count(*) as cnt from t10m group by object_id)b
 on a.object_id=b.object_id
;
复制

oracle的表现, 未能识别连接消除(2.49秒):

OB识别了这是一个可以消除的left join(1毫秒) :

场景9: 带过滤条件的最大/最小值

select max(object_id) from t10m where owner='SYS';
注: object_id字段上有索引;
复制

oracle的表现(1.2秒):

ob的表现(7毫秒):

场景10: exists里面包含非等值关联条件

select count(*)
FROM T10m a
WHERE EXISTS
(SELECT 1
 FROM t1 b
 WHERE b.object_name='TAB'
  AND a.object_id = b.object_id
  AND b.created>=a.created  --------非等着关联条件
);


复制