首页 » OceanBase, ORACLE 9i-23ai, PostgreSQL/GaussDB, 达梦 » Oracle、Oceanbase、Kingbase、GaussDB、达梦数据库比较系列(二十七):子查询中的函数投影裁剪

Oracle、Oceanbase、Kingbase、GaussDB、达梦数据库比较系列(二十七):子查询中的函数投影裁剪

在开发场景中如查询分页计算多少页或统计查询，有些开发是习惯基于明细的查询SQL，而外层直接加1层汇聚查询，如select count(*) from (select ….)， 但子查询中可能有一些函数或主查询根本不需要的列， 在oracle中的查询转换中如select-project-join或select list pruning， 或VIEW merge SPJ,CVM 都是为了不影响SQL结果一致性，而优化低效的SQL. 但是从oracle迁移到其它数据库中，因为CBO的差异，导致SQL性能大量衰减，需要手动改写SQL， 最近从oracle迁移到pg系国产库，发现一个view中使用了function, 而外部查询根本不关心该列值，就是一个无效的查询列，无意义的函数调用， 在后期国产库迁移需要注意。

Oracle

SQL>CREATE OR REPLACE FUNCTION TEST_SLEEP( TIME_  IN  NUMBER)
   RETURN INTEGER IS
    BEGIN
   DBMS_LOCK.sleep(seconds => TIME_);
   RETURN 1;
    EXCEPTION
      WHEN OTHERS THEN
      RAISE;
      RETURN 1;
   END TEST_SLEEP;
   /

SQL> set timing on

SQL> select min(object_id),TEST_SLEEP(10) from (select object_id,owner from anbob.tobj);

MIN(OBJECT_ID) TEST_SLEEP(10)
-------------- --------------
             2              1

Elapsed: 00:00:10.08
SQL> select min(object_id) from (select object_id,TEST_SLEEP(100),owner from anbob.tobj);

MIN(OBJECT_ID)
--------------
             2
Elapsed: 00:00:00.01

10053 trace

SPJ - select-project-join
SLP - select list pruning
CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$1 0x7fd7bc45d0a8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=5 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"

Registered qb: SEL$2 0x7fd7bc45b7b8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=181330 hint_alias="TOBJ"@"SEL$2"

****************
QUERY BLOCK TEXT
****************
select min(object_id) from (select object_id,lower(object_name),owner from anbob.tobj)

GPUA: kkqgpuaCheckValidity :******* UNPARSED QUERY IS *******
SELECT MIN("from$_subquery$_001"."OBJECT_ID") "MIN(OBJECT_ID)" FROM  (SELECT "TOBJ"."OBJECT_ID" "OBJECT_ID",LOWER("TOBJ"."OBJECT_NAME") "LOWER(OBJECT_NAME)","TOBJ"."OWNER" "OWNER" FROM "ANBOB"."TOBJ" "TOBJ") "from$_subquery$_001"
--

**************************
Query transformations (QT)
**************************

===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT MIN("TOBJ"."OBJECT_ID") "MIN(OBJECT_ID)" FROM "ANBOB"."TOBJ" "TOBJ"
kkoqbc: optimizing query block SEL$F5BB74E1 (#0)

Note:
oracle做了查询转换view merge和select list pruning ，子查询中的function被column投影裁剪掉了。

Kingbase

[kingbase@kingbase1 ~]$ ksql -U xxxx....
Password:
ksql (V8.0)
Type "help" for help.

kingbase=# create table t(id int,name varchar2(20));
CREATE TABLE
kingbase=# insert into t select rownum,'anbob'||rownum from dual connect by rownum<=10;
INSERT 0 10
kingbase=# \timing on
Timing is on.
kingbase=# select count(*) from (select id,name,pg_sleep(2) from t);
 count
-------
    10
(1 row)

Time: 20015.006 ms (00:20.015)

Note:
postgresql中是每行调用了函数。

GaussDB

anbob=# select version();
                                                      version
-------------------------------------------------------------------------------------------------------------------
 gaussdb (GaussDB Kernel 505.2.0 build 82d715e8) compiled at 2024-09-20 00:15:22 commit 9967 last mr 19883 release
(1 row)

Time: 0.568 ms
anbob=# explain analyze select id from (select id,name,pg_sleep(5)  from t limit 3);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on __unnamed_subquery__  (cost=0.00..0.37 rows=3 width=4) (actual time=5001.069..15003.188 rows=3 loops=1)
   ->  Limit  (cost=0.00..0.34 rows=3 width=680) (actual time=5001.065..15003.174 rows=3 loops=1)
         ->  Seq Scan on t  (cost=0.00..344.50 rows=3 width=680) (actual time=5001.062..15003.164 rows=3 loops=1)
 Total runtime: 15003.372 ms
(4 rows)

Time: 15004.628 ms
anbob=# show debug_print_rewritten;
 debug_print_rewritten
-----------------------
 off
(1 row)

Time: 0.473 ms
anbob=# set debug_print_rewritten=on;
ERROR:  parameter "debug_print_rewritten" cannot be changed now
Time: 0.953 ms

Note:
GaussDB同kingbase，意料之内同根PostGreSQL。 debug_print_rewritten参数不能配置，也没有10053， 像查看一下rewrite规则。

PostgreSQL

debug_print_parse: When you enable this parameter, the parse tree of incoming queries is printed to the server log. This can be useful for understanding the structure of a query and the values of any bound parameters.

debug_print_rewritten: When you enable this parameter, the rewritten forms of incoming queries are printed to the server log. This can be useful for understanding how the query planner interprets a query and the values of any bound parameters.

[postgres@anbob1 data]$ psql -c"show all"|grep parse
 debug_pretty_print                          | on                              | Indents parse and plan tree displays.
 debug_print_parse                           | off                             | Logs each query's parse tree.
 debug_print_rewritten                       | off                             | Logs each query's rewritten parse tree.
 log_parser_stats                            | off                             | Writes parser performance statistics to the server log.

postgres=# set debug_print_parse=on;
SET
postgres=# select name from (select name,num,pg_sleep(2) from tg limit 2);
2024-11-23 19:39:21.866 CST [18505] LOG:  parse tree:
2024-11-23 19:39:21.866 CST [18505] DETAIL:     {QUERY
           :commandType 1
           :querySource 0
           :canSetTag true
           :utilityStmt <>
           :resultRelation 0
           :hasAggs false
           :hasWindowFuncs false
           :hasTargetSRFs false
           :hasSubLinks false
           :hasDistinctOn false
           :hasRecursive false
           :hasModifyingCTE false
           :hasForUpdate false
           :hasRowSecurity false
           :isReturn false
           :cteList <>
           :rtable (
              {RANGETBLENTRY
              :alias <>
              :eref
                 {ALIAS
                 :aliasname unnamed_subquery
                 :colnames ("name" "num" "pg_sleep")
                 }
              :rtekind 1
              :subquery
                 {QUERY
                 :commandType 1
                 :querySource 0
                 :canSetTag true
                 :utilityStmt <>
                 :resultRelation 0
                 :hasAggs false
                 :hasWindowFuncs false
                 :hasTargetSRFs false
                 :hasSubLinks false
                 :hasDistinctOn false
                 :hasRecursive false
                 :hasModifyingCTE false
                 :hasForUpdate false
                 :hasRowSecurity false
                 :isReturn false
                 :cteList <>
                 :rtable (
                    {RANGETBLENTRY
                    :alias <>
                    :eref
                       {ALIAS
                       :aliasname tg
                       :colnames ("name" "num")
                       }
                    :rtekind 0
                    :relid 17101
                    :inh true
                    :relkind r
                    :rellockmode 1
                    :perminfoindex 1
                    :tablesample <>
                    :lateral false
                    :inFromCl true
                    :securityQuals <>
                    }
                 )
     ...
           }

2024-11-23 19:39:21.866 CST [18505] STATEMENT:  select name from (select name,num,pg_sleep(2) from tg limit 2);

达梦V8

SQL> select * from v$version;

LINEID     BANNER
---------- ---------------------------------
1          DM Database Server 64 V8
2          DB Version: 0x7000c
...

SQL> create table t(id int,name varchar2(20));
executed successfully
used time: 111.517(ms). Execute id is 14641109.

SQL> insert into t select rownum,'anbob'||rownum from dual connect by rownum<=10; 
affect rows 10 used time: 83.469(ms). Execute id is 14641110. 

SQL> select count(*) from (select id,name,TEST_SLEEP(2) from t);
LINEID     COUNT(*)
---------- --------------------
1          10

used time: 17.317(ms). Execute id is 14641111.

Note:
达梦形为同oracle， 子查询中的sleep函数并未调用。

达梦10053

SQL> ALTER SESSION 0 SET EVENTS '10053 trace name context forever';
executed successfully
used time: 69.052(ms). Execute id is 14641112.
SQL> select count(*) from (select id,name,TEST_SLEEP(10) from t);
LINEID     COUNT(*)
---------- --------------------
1          10

used time: 20.122(ms). Execute id is 14641113.
SQL> ALTER SESSION 0 SET EVENTS '10053 trace name context off';
executed successfully
used time: 1.378(ms). Execute id is 14641114.
SQL> select para_name,para_value from v$dm_ini where para_name like 'TRACE_PATH';

LINEID     PARA_NAME  PARA_VALUE
---------- ---------- ----------------------------------
1          TRACE_PATH /home/dmdba/dmdsc/data/DSC01/trace

used time: 75.690(ms). Execute id is 14641115.

SQL> explain select count(*) from (select id,name,TEST_SLEEP(10) from t);

1   #NSET2: [1, 1, 52]
2     #PRJT2: [1, 1, 52]; exp_num(1), is_atom(FALSE)
3       #FAGR2: [1, 1, 52]; sfun_num(1)

used time: 1.322(ms). Execute id is 0.

SQL> host

[dmdba@ora19c1 ~]$ cd /home/dmdba/dmdsc/data/DSC01/trace
[dmdba@ora19c1 trace]$ ls
DSC01_1124_0856_139920505114648.trc
[dmdba@ora19c1 trace]$ vi DSC01_1124_0856_139920505114648.trc
[dmdba@ora19c1 trace]$ exit
exit

*** Plan before optimized:
 project[0x7f41c80408e8] n_exp(1)
   group[0x7f41c8040280]
     base table[0x7f41c803fc48] (T, FULL SEARCH)

---------------- single table access path probe for T ----------------
*** path 1: INDEX33708453 (FULL search), cost: 0.04566

>>> best access path: INDEX33708453 (FULL search), cost: 0.04566

*** BEST PLAN FOR THIS STATEMENT ***
 project[0x7f41c80522c0] n_exp(1) (cost: 0.04566, rows: 1)
   group[0x7f41c8052928]  (cost: 0.04566, rows: 1)
     base table[0x7f41c8052f90] (T, INDEX33708453, FULL SEARCH) (cost: 0.04566, rows: 10)

-------------------------- END --------------------------

Note:
达梦虽然支持10053 trace，但trace中的内容似乎没有太多，相比Oracle还是差的多。

Oceanbase

obclient [SYS]> select * from v$version;
+---------------------------------------------------------------------------------------------------------------+--------+
| BANNER                                                                                                        | CON_ID |
+---------------------------------------------------------------------------------------------------------------+--------+
| OceanBase 4.2.1.2 (r102010012023120119-130bf91ba413a00bb696fe8853906fde1f29f83d) (Built Dec  1 2023 20:00:25) |      0 |
+---------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.154 sec)

obclient [SYS]> CREATE OR REPLACE FUNCTION TEST_SLEEP( TIME_  IN  NUMBER)
    ->    RETURN INTEGER IS
    ->     BEGIN
    ->    DBMS_LOCK.sleep(seconds => TIME_);
    ->    RETURN 1;
    ->     EXCEPTION
    ->       WHEN OTHERS THEN
    ->       RAISE;
    ->       RETURN 1;
    ->    END TEST_SLEEP;
    ->    /
Query OK, 0 rows affected (3.212 sec)

obclient [SYS]> create table t as select rownum id,'anbob'||rownum name from dual connect by rownum<=10; 
Query OK, 10 rows affected (6.127 sec) 

obclient [SYS]> select count(*) from (select id,name,TEST_SLEEP(5) from t);
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.001 sec)

obclient [SYS]> select count(*),TEST_SLEEP(5) from (select id,name  from t);
+----------+---------------+
| COUNT(*) | TEST_SLEEP(5) |
+----------+---------------+
|       10 |             1 |
+----------+---------------+
1 row in set (5.131 sec)

obclient [SYS]> explain select count(*) from (select id,name,TEST_SLEEP(5) from t);
+-------------------------------------------------------------------------+
| Query Plan                                                              |
+-------------------------------------------------------------------------+
| =============================================================           |
| |ID|OPERATOR                     |NAME|EST.ROWS|EST.TIME(us)|           |
| -------------------------------------------------------------           |
| |0 |SCALAR GROUP BY              |    |1       |5           |           |
| |1 |└─DISTRIBUTED TABLE FULL SCAN|T   |10      |4           |           |
| =============================================================           |
| Outputs & filters:                                                      |
| -------------------------------------                                   |
|   0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=16 |
|       group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))])           |
|   1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=16                  |
|       access(nil), partitions(p0)                                       |
|       is_index_back=false, is_global_index=false,                       |
|       range_key([T.__pk_increment]), range(MIN ; MAX)always true        |
+-------------------------------------------------------------------------+
14 rows in set (0.116 sec)

Note:
Oceanbase的形为和Oracle一样，也消除了子查询中的函数调用。

打赏

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：