迁移Oracle到PostgreSQL一个语法报错，看看Oracle CBO Query Transformations

首页 » ORACLE 9i-23ai » 迁移Oracle到PostgreSQL一个语法报错，看看Oracle CBO Query Transformations

迁移Oracle到PostgreSQL一个语法报错，看看Oracle CBO Query Transformations

2024/11/18
ORACLE 9i-23ai
27 views
没有评论

最近在迁移oracle到基于postgresql的国产库发现了一些兼容性问题，联想到oracle对问题SQL是多么包容，我不确认已经实现oracle语法兼容的数据库，又有多少支持了Oracle在SQL查询转换中的功能，尤其是一些不必要的查询消除. 如order by elimination、Join Elimination、Common Sub-expression Elimination、subquery elimination，这里记录一下Oracle 10053 Trace中的优化器优化形为。

PostgreSQL

highgo=# select * from tg;
 name | num
------+-----
 a    |   1
 a    |   1
 b    |  23
 b    |  43
 c    |  63
(5 rows)

highgo=# select count(*) from tg order by name;
ERROR:  column "tg.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*) from tg order by name;

Oracle

SQL> @53on
alter session set events '10053 trace name context forever, level 1';
alter session set "_optimizer_trace"=all;

SQL> select count(*) from anbob.tg order by naME;
  COUNT(*)
----------
         5

SQL> @53off
alter session set events '10053 trace name context off';
Session altered.

SQL> @t
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/enmo/enmo1/trace/enmo1_ora_1367167.trc

************** Oracle 23.1 *********************
 Legend
 The following abbreviations are used by optimizer trace.
 CBQT - cost-based query transformation
 JPPD - join predicate push-down
 OJPPD - old-style (non-cost-based) JPPD
 FPD - filter push-down
 PM - predicate move-around
 CVM - complex view merging
 SPJ - select-project-join
 SJC - set join conversion
 SU - subquery unnesting
 OBYE - order by elimination
 OST - old style star transformation
 ST - new (cbqt) star transformation
 CNT - count(col) to count(*) transformation
 JE - Join Elimination
 JF - join factorization
 CBY - connect by
 SLP - select list pruning
 DP - distinct placement
 VT - vector transformation
 AAT - Approximate Aggregate Transformation
 ORE - CBQT OR-Expansion
 LORE - Legacy OR-Expansion
 qb - query block
 LB - leaf blocks
 DK - distinct keys
 LB/K - average number of leaf blocks per key
 DB/K - average number of data blocks per key
 CLUF - clustering factor
 NDV - number of distinct values
 Resp - response cost
 Card - cardinality
 Resc - resource cost
 NL - nested loops (join)
 SM - sort merge (join)
 HA - hash (join)
 CPUSPEED - CPU Speed
 IOTFRSPEED - I/O transfer speed
 IOSEEKTIM - I/O seek time
 SREADTIM - average single block read time
 MREADTIM - average multiblock read time
 MBRC - average multiblock read count
 MAXTHR - maximum I/O system throughput
 SLAVETHR - average slave I/O throughput
 dmeth - distribution method
 1: no partitioning required
 2: value partitioned
 4: right is random (round-robin)
 128: left is random (round-robin)
 8: broadcast right and partition left
 16: broadcast left and partition right
 32: partition left using partitioning of right
 64: partition right using partitioning of left
 256: run the join in serial
 0: invalid distribution method
 sel - selectivity
 ptn - partition
 AP - adaptive plans
 BJ: Bushy Join
 CSE: Common Sub-expression Elimination
 DCL: Decorrelation of lateral view
 GBP: Group by placement
 JE[V2]: Join Elimination (version 2 ?)
 PJE: Partial Join Evaluation
 PPU: (Filter) Predicate Pull-up
 RSW: Remove subquery
 SHRD: Sharding
 SQE: subquery elimination
 SVM: Simple View Merge
 TE: Table expansion
 VE: Vector Encode
 ZM: Zone Map

Query transformations (QT)
 **************************
 JF: Checking validity of join factorization for query block SEL$1 (#0)
 JF: Bypassed: has order-by clause.
 ST: not valid since star transformation parameter is FALSE
 TE: Checking validity of table expansion for query block SEL$1 (#0)
 TE: Bypassed: No partitioned table in query block.
 ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
 VT: Initial VT validity check for query block SEL$1 (#0)
 VT: Bypassed: No joining conditions.
 BJ: Checking validity for bushy join for query block SEL$1 (#0)
 invalid because Not enabled by hint/parameter
 BJ: Bypassed: Not enabled by hint/parameter.
 GPUA: Checking validity of union-all group-by pushdown for query block SEL$1 (#0)
 GPUA: kkqgpuaCheckValidity - initial check SEL$1 (#0)
 GPUA: kkqgpuaCheckValidity :******* UNPARSED QUERY IS *******
 SELECT COUNT(*) "COUNT(*)" FROM "ANBOB"."TG" "TG" ORDER BY "TG"."NAME"
 GPUA: Failed validity - no view SEL$1 (#0)
 GPUA: Bypassed: invalidated.
 CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
 CBQT: Validity checks failed for 2y5dzrgmbp5ks.
 CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
 *************************
 Common Subexpression elimination (CSE)
 *************************
 CSE: CSE not performed on query block SEL$1 (#0).
 OBYE: Considering Order-by Elimination from view SEL$1 (#0)
 ***************************
 Order-by elimination (OBYE)
 ***************************
 OBYE: OBYE performed.
 CVM: Considering view merge in query block SEL$1 (#0)
 OJE: Begin: find best directive for query block SEL$1 (#0)
 OJE: End: finding best directive for query block SEL$1 (#0)
 CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
 *************************
 Count(col) to Count(*) (CNT)
 *************************
 CNT: COUNT() to COUNT(*) not done.
 query block SEL$1 (#0) unchanged
 Considering Query Transformations on query block SEL$1 (#0)

*************************
 Common Subexpression elimination (CSE)
 *************************
 CSE: CSE not performed on query block SEL$1 (#0).
 AAT: Considering Approximate Aggregate Transformation on query block SEL$1 (#0)
 *******************************************
 Approximate Aggregate Transformation (AAT)
 *******************************************
 AAT: no exact aggregates transformed
 SQE: Trying SQ elimination.
 SVS: Query before subsumption:******* UNPARSED QUERY IS *******
 SELECT COUNT(*) "COUNT(*)" FROM "ANBOB"."TG" "TG" ORDER BY "TG"."NAME"
 SVS: Query after subsumption:******* UNPARSED QUERY IS *******
 SELECT COUNT(*) "COUNT(*)" FROM "ANBOB"."TG" "TG" ORDER BY "TG"."NAME"
 SU: Considering subquery unnesting in query block SEL$1 (#0)
 ********************
 Subquery Unnest (SU)
 ********************
 SU: Considering bottom-up subquery unnesting
 SJC: Considering set-join conversion in query block SEL$1 (#0)
 *************************
 Set-Join Conversion (SJC)
 *************************
 SJC: not performed
 DCL: Checking validity of group-by elimination SEL$1 (#0)
 DCL: Result of group-by elimination: Invalid
 PM: Considering predicate move-around in query block SEL$1 (#0)
 **************************
 Predicate Move-Around (PM)
 **************************
 PM: PM bypassed: Outer query contains no views.
 PM: PM bypassed: Outer query contains no views.
 isReduceGrByValid: Group By Validation (Failed).
 isReduceGrByValid: Group By Validation (Failed).
 WCFPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
 WCFPD: Current where clause predicates ??

GPUA: kkqgpuadrv: CBQT invalid SEL$1 (#0)
 SQT: Considering Statistic-based Query Transformation from query block SEL$1 (#0)
 ***************************
 Statistic-based Query Transformation (SQT)
 ***************************
 SQT: SQT bypassed: Disabled by parameter.
 query block SEL$1 (#0) unchanged
 FPD: Considering simple filter push in query block SEL$1 (#0)
 ??
 apadrv-start sqlid=3392828708044314200
 CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
 *************************
 Common Subexpression elimination (CSE)
 *************************
 CSE: CSE not performed on query block SEL$1 (#0).

===================================
 SPD: END context at statement level
 ===================================
 Final query after transformations:******* UNPARSED QUERY IS *******
 SELECT COUNT(*) "COUNT(*)" FROM "ANBOB"."TG" "TG"
 kkoqbc: optimizing query block SEL$1 (#0)

Oracle 使用了order by 消除，并未影响SQL结果。可见oracle在优化转换中的功能是对于低质SQL有多友善，是国产数据库学习的真正方向，而不仅仅是语法兼容。

一些样例,可以查看Riyaj Shamsudeen 在2008年的一个白皮书COST BASED QUERY TRANSFORMATIONS CONCEPT AND ANALYSIS USING 10053 TRACE,没错08年。

我在做10053 trace时，又发现了oracle一个考虑周到的细节。trace文件输出未完，最后一行输出为“*** TRACE SUPPRESSED, REACHED DISK THRESHOLD 1024 MB ***”， trace被暂停了，因为发现ORACLE Home所在的文件系统使用可用空间低于1G. 这是12.1以后的功能,清理空间后可以正常输出。

Filesystem           Size  Used Avail Use% Mounted on
devtmpfs              16G     0   16G   0% /dev
tmpfs                 16G  1.3G   15G   9% /dev/shm
tmpfs                 16G  1.6G   14G  11% /run
tmpfs                 16G     0   16G   0% /sys/fs/cgroup
/dev/mapper/ol-root   89G   89G  263M 100% /
/dev/sda1           1014M  336M  679M  34% /boot
tmpfs                3.2G   12K  3.2G   1% /run/user/42
tmpfs                3.2G     0  3.2G   0% /run/user/54325
tmpfs                3.2G     0  3.2G   0% /run/user/54322
tmpfs                3.2G     0  3.2G   0% /run/user/0

“TRACE SUPPRESSED, REACHED DISK THRESHOLD xxx MB” Message Printed In Trace File (Doc ID 2999532.1)

打赏

CBO, Query Transformations

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

我要评论

上一篇： MySQL ERROR 2027(HY000):Malformed packet due to GoldenGate Replication Slave

下一篇： Alert: not null Defining Integrity Constraints or Check( xx is not null) Constraint 性能差异

ANBOB™