Oracle12c以一种奇怪的方式解释SQL(内部查询)

Question

我们最近将Oracle数据库从10g迁移到12c（12.1.0.1.0）。在考虑了一些查询的问题后，我们决定进一步清理数据库并删除所有不需要的对象
因此，我编写了一个查询，在数据库DDL中搜索要显示的特定文本，其中使用了特定的视图或函数。

SELECT 
  object_name, object_type, DBMS_METADATA.GET_DDL(object_type, object_name) as ddl_txt 
FROM user_objects 
WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER') 
  AND UPPER( DBMS_METADATA.GET_DDL(object_type, object_name) ) LIKE upper('%myFunction%')

这导致以下异常：

ORA-31600: invalid input value TYPE BODY for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5746
ORA-06512: at "SYS.DBMS_METADATA", line 8333
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

之所以出现异常，是因为我们的数据库中有"Body Type"对象，而它们不提供带有DBMS_METADATA.GET_DDL()的ddl。运行下面的查询会产生与初始查询完全相同的异常。

select dbms_metadata.get_ddl('TYPE BODY', 'myBodyStringType') from dual

因此，我尝试创建一个内部列表，通过如下重写我的查询，首先将所有用户对象的列表减少到

select
  lst.*,
  DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt 
from (
      SELECT 
        object_name, object_type
      FROM user_objects 
      WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER') 
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')

有趣的是，它带来了与上面所示相同的例外。我不明白为什么会这样。

我希望Oracle首先创建内部列表，并只使用剩余值的DBMS_METADATA.GET_DLL()函数，因为相同的值会导致异常。为什么Oracle在这里做其他事情？

为了解决这个特殊的问题，我必须在内部查询中添加一个ORDER BY，这在我看来很愚蠢。为什么我必须强制Oracle首先使用ORDER BY创建内部查询？

select
  lst.*,
  DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt 
from (
      SELECT 
        object_name, object_type
      FROM user_objects 
      WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER')
      ORDER BY ROWNUM ASC
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')

提前感谢您对为什么会发生这种情况的解释？-我记得，后面的查询在Oracle10g上运行时没有出现任何问题
（我担心在其他进行计算的报告上也会有同样的行为，因为这种行为可能会出错！）。

Answer 1

这是一个错误 Oracle支持部门刚刚向我确认，该异常是由于Oracle 12.1.0版本中的一个错误引起的。1

有两个选项可供选择：
1） 更新到Oracle 12.1.0.2版本，该错误已修复
2） 请等待几周，等待Oracle即将开始工作的补丁。该修补程序将在Oracle 12.1.0版中修复此问题。1。

我们还没有决定采取哪种选择，但我非常有信心，其中一种会起作用，因为Oracle支持确实重现了我的问题。

Answer 2

很可能是谓词推送（对不起，我找不到简单的解释）

它不是先运行内部记录集，然后计算余数。它将外部的where推到内部派生表中。查询计划肯定会告诉你。

通过使用ROWNUM，您强制它首先计算内部记录集。这不是ORDER BY，而是ROWNUM。代替ORDER BY，您也可以执行AND ROWNUM > 0，它也会执行同样的操作，因为它必须先对每一行求值，然后才能对ROWNUM表达式求值。