oracle和金仓区别，个人睬坑
1、select中使用相关子查询，oracle中正常执行效率，在金仓中严重影响性能。以下是出现问题原SQL
修改之后，可在金仓执行效率好，但在oracle效率不好
思路将”select相关子查询“修改成left join多表连接。
注意：分页情况时，一定使用order by排序。
如果查询的是视图，视图中sql使用了order by，但是查询视图外的sql没使用，那么分页查询在oracle中不影响效率。但是在金仓严重影响，需要在查询视图后加上order by
原SQL执行语句的抛错
\n### Error querying database. Cause: com.kingbase8.util.KSQLException: An I/O error occurred while sending to the backend.\n### The error may exist in URL [jar:file:/home/scms/plis/lnplis/lnplis-2.0.0.jar!/BOOT-INF/lib/ruoyi-lnplis-0.0.1.jar!/mapper/PurchaseStatisticsQueryMapper.xml]\n### The error may involve com.ultra.plis.statistics.reportform.purchase.mapper.PurchaseStatisticsQueryMapper.findFrameContractInfoForMyBatis-Inline\n### The error occurred while setting parameters\n### SQL: SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( select * from table b where 1=1 ) TMP_PAGE) WHERE PAGEHELPER_ROW_ID <= ? AND PAGEHELPER_ROW_ID > ?\n### Cause: com.kingbase8.util.KSQLException: An I/O error occurred while sending to the backend.\n; An I/O error occurred while sending to the backend.; nested exception is com.kingbase8.util.KSQLException: An I/O error occurred while sending to the backend.
2、金仓对多表连接的条件中，使用or性能不好，所以需要把or拆分原sql
优化后
3、在kingbase中抛错For queries with named parameters you need to ust provide names for method paremeter，在oracle中不抛错
这个问题的根本原因是KingbaseES对JPA规范的要求更严格，而Oracle的驱动相对宽松。写法问题，不能省略@Param(“orderDetailIds”) ，否则在KingbaseES抛错。
@RepositorypublicinterfaceOrderDetailDaoextendsJpaRepository<OrderDetail,String>,JpaSpecificationExecutor<OrderDetail>{@Query(" from OrderDetail where id in (:orderDetailIds) and (deleteFlag = '0' or deleteFlag is null)")List<OrderDetail>findOrderDetailsByIdIn(@Param("orderDetailIds")List<String>orderDetailIds);}