目 录CONTENT

文章目录

【MogDB】MogDB5.2.0重磅发布第五篇-支持部分ORACLE的HINT

DarkAthena
2024-10-27 / 0 评论 / 0 点赞 / 11 阅读 / 0 字

一、背景

ORACLE的SQL优化器非常强大,但是仍然会在某些情况下,ORACLE自动生成的执行计划并不是很好,此时可以通过在SQL中增加HINT来人工控制执行计划应该怎么走。在ORACLE迁移到国产库的过程中,由于部分国产库并不支持ORACLE的HINT名称,因此以前在ORACLE加的这些HINT到了国产库并不生效,导致有些SQL执行变得很慢。一般情况下认为迁移到国产库需要重新进行SQL调优是无可避免的事,但是,如果一套应用系统使用ORACLE迭代开发十几年,积累了大量的SQL,使用了数不清的hint,一次性迁移到国产库,所有SQL都得重新进行性能分析和优化,这对SQL开发人员来说,会是一个巨大的工作量!
MogDB考虑到这种情况,决定采取以对内核影响最少的方式,尽可能多兼容一些Oracle的hint。

二、支持的hint列表

hint名称 说明
CARDINALITY 映射为rows(#)
FULL 映射为TableScan
INDEX_JOIN 映射为IndexOnlyScan
INDEX 映射为IndexScan
NO_INDEX 映射为No_Index
NO_UNNEST 映射为No_expand
OPTARAM 映射为set
PARALLEL(dop) 映射为set(query_dop dop)
QB_NAME 映射为BlockName
USE_HASH 映射为HashJoin
NO_USE_HASH 映射为 no HashJoin
USE_MERGE 映射为MergeJoin
NO_USE_MERGE 映射为 no MergeJoin
USE_NL 映射为NestLoop
NO_USE_NL 映射为 no NestLoop
APPEND 新增,仅做语法支持,不影响其他同时使用的hint

三、用法示例

DROP SCHEMA IF EXISTS test_oracle_hints CASCADE;
CREATE SCHEMA test_oracle_hints;
SET current_schema='test_oracle_hints';
DROP TABLE IF EXISTS oracle_hints_01;
CREATE TABLE oracle_hints_01(a int, b int);
CREATE INDEX hint01_index on oracle_hints_01(a);
INSERT INTO oracle_hints_01 VALUES (generate_series(1, 1000), generate_series(1, 1000));
ANALYZE oracle_hints_01;
SET smp_thread_cost = 0;

-- cardinality hints
explain (costs off) select /*+ cardinality(t1 100) cardinality(t2, 100) cardinality(t1, t2, 100) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                 QUERY PLAN
--------------------------------------------
 Hash Join
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on oracle_hints_01 t1
   ->  Hash
         ->  Seq Scan on oracle_hints_01 t2
(5 rows)

-- full hints
explain (costs off) select /*+ full(t1) */ * from oracle_hints_01 t1 where t1.a = 1;
           QUERY PLAN       
--------------------------------
 Seq Scan on oracle_hints_01 t1
   Filter: (a = 1)
(2 rows)

-- index join hints
explain (costs off) select /*+ index_join(t1, hint01_index) */ a from oracle_hints_01 t1 where t1.a > 1;
                        QUERY PLAN                    
----------------------------------------------------------
 [Bypass]
 Index Only Scan using hint01_index on oracle_hints_01 t1
   Index Cond: (a > 1)
(3 rows)

-- index hints
explain (costs off) select /*+ index(t1, hint01_index) */ * from oracle_hints_01 t1 where t1.a > 1;
                     QUERY PLAN                  
-----------------------------------------------------
 [Bypass]
 Index Scan using hint01_index on oracle_hints_01 t1
   Index Cond: (a > 1)
(3 rows)

explain (costs off) select /*+ no_index(t1, hint01_index) */ * from oracle_hints_01 t1 where t1.a = 1;
               QUERY PLAN            
-----------------------------------------
 Bitmap Heap Scan on oracle_hints_01 t1
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on hint01_index
         Index Cond: (a = 1)
(4 rows)

-- no_unnest hints
explain (costs off) select * from oracle_hints_01 t1 where t1.a in (select /*+ no_unnest() */ t2.a from oracle_hints_01 t2);
               QUERY PLAN           
----------------------------------------
 Seq Scan on oracle_hints_01 t1
   Filter: (hashed SubPlan 1)
   SubPlan 1
     ->  Seq Scan on oracle_hints_01 t2
(4 rows)

-- opt params hints
explain (costs off) select /*+ opt_param('query_dop', 4) */ a from oracle_hints_01 t1 where t1.a > 1;
               QUERY PLAN           
----------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)
   ->  Seq Scan on oracle_hints_01 t1
         Filter: (a > 1)
(3 rows)

explain (costs off) select /*+ opt_param(query_dop, 4) */ a from oracle_hints_01 t1 where t1.a > 1;
               QUERY PLAN           
----------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)
   ->  Seq Scan on oracle_hints_01 t1
         Filter: (a > 1)
(3 rows)

-- parallel hints
explain (costs off) select /*+ parallel(4) */ a from oracle_hints_01 t1 where t1.a > 1;
               QUERY PLAN           
----------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)
   ->  Seq Scan on oracle_hints_01 t1
         Filter: (a > 1)
(3 rows)

-- qb_name hints
explain (costs off) select /*+nestloop(t1 tt) */ * from oracle_hints_01 t1 where t1.a in (select /*+ qb_name(tt) */ t2.a from oracle_hints_01 t2 group by t2.a);
                        QUERY PLAN                     
-----------------------------------------------------------
 Nested Loop
   ->  HashAggregate
         Group By Key: t2.a
         ->  Seq Scan on oracle_hints_01 t2
   ->  Index Scan using hint01_index on oracle_hints_01 t1
         Index Cond: (a = t2.a)
(6 rows)

-- join hints
explain (costs off) select /*+ use_hash(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                 QUERY PLAN             
--------------------------------------------
 Hash Join
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on oracle_hints_01 t1
   ->  Hash
         ->  Seq Scan on oracle_hints_01 t2
(5 rows)

explain (costs off) select /*+ no_use_hash(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                        QUERY PLAN                     
-----------------------------------------------------------
 Merge Join
   Merge Cond: (t1.a = t2.a)
   ->  Index Scan using hint01_index on oracle_hints_01 t1
   ->  Index Scan using hint01_index on oracle_hints_01 t2
(4 rows)

explain (costs off) select /*+ use_merge(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                        QUERY PLAN                     
-----------------------------------------------------------
 Merge Join
   Merge Cond: (t1.a = t2.a)
   ->  Index Scan using hint01_index on oracle_hints_01 t1
   ->  Index Scan using hint01_index on oracle_hints_01 t2
(4 rows)

explain (costs off) select /*+ no_use_merge(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                 QUERY PLAN             
--------------------------------------------
 Hash Join
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on oracle_hints_01 t1
   ->  Hash
         ->  Seq Scan on oracle_hints_01 t2
(5 rows)

explain (costs off) select /*+ use_nl(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                        QUERY PLAN                     
-----------------------------------------------------------
 Nested Loop
   ->  Seq Scan on oracle_hints_01 t1
   ->  Index Scan using hint01_index on oracle_hints_01 t2
         Index Cond: (a = t1.a)
(4 rows)

explain (costs off) select /*+ no_use_nl(t1, t2) */ * from oracle_hints_01 t1, oracle_hints_01 t2 where t1.a = t2.a;
                 QUERY PLAN             
--------------------------------------------
 Hash Join
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on oracle_hints_01 t1
   ->  Hash
         ->  Seq Scan on oracle_hints_01 t2
(5 rows)

DROP SCHEMA test_oracle_hints;

四、差异点

对于这些和ORACLE相同名称的HINT,MogDB仍然有一些差异。
在MogDB 5.2.0版本中:

  • CARDINALITY 不支持CARDINALITY(n)的形式,即必须指定表或者表别名
  • PARALLEL 不支持PARALLEL(t n)的形式,即不能指定单表的并行度
  • USE_HASH 不支持只填写一个表,至少需要两个表
  • use_hash(t1,t2,t3,t4) 可以直接控制连接顺序,即 (t1(t2 (t3 t4)))
  • leading保留原本MogDB支持的用法,未做修改,比较明显的一个差异就是MogDB的leading里不能只放一个表

五、总结

ORACLE里支持的hint其实远不止这些,但由于底层原理上存在一些差异,任何非ORACLE数据库都不可能做到和ORACLE的hint全兼容,每种数据库都应该有自己特有的SQL优化器。因此,就算做了一些ORACLE的兼容HINT,也不能说性能就能和ORACLE一样,如果想要得到最佳性能表现,仍然离不开数据库内核自身的优化能力。

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论