Oracle 参数化动态查询

嗨，

我正在尝试提高生产环境中某些查询的性能。

当前情况是:

1) 从“ParentTable“。在软件代码中，查询是根据指定的搜索条件动态组成的 (是的，我知道这很糟糕，这是我正在更改它的方式;-) )。因此，如果指定了不同的搜索条件，则查询会更改:

SELECT parentField1, parendField2
  FROM ParentTable
 WHERE column1 = :paramSearch1 --added dynamically only if needed
   AND column2 = :paramSearch2 --added dynamically only if needed
   ...
   AND columnX = :paramSearchX --added dynamically only if needed

这个查询可以返回很多记录 (甚至超过100万)

2) 此查询的结果用于对其他表执行一组查询，以检索相关实体。假设先前的查询已返回2500记录，则 (动态) 构建以下查询以检索所需的实体数据:

--FIRST CHILD TABLE
SELECT *
  FROM MyChildTable1
 WHERE ((childCol1,chialdCol2) IN ((:parentValueCol1_1,:parentValueCol2_1), (:parentValueCol1_2,:parentValueCol2_2), ..., (:parentValueCol1_1000,:parentValueCol2_1000)))
       OR  ((childCol1,chialdCol2) IN ((:parentValueCol1_1001,:parentValueCol2_1001), (:parentValueCol1_1002,:parentValueCol2_1002), ..., (:parentValueCol1_2000,:parentValueCol2_2000)))
       OR ((childCol1,chialdCol2) IN ((:parentValueCol1_2001,:parentValueCol2_2001), (:parentValueCol1_2002,:parentValueCol2_2002), ..., (:parentValueCol1_2500,:parentValueCol2_2500)))

--SECOND CHILD TABLE
SELECT *
  FROM MyChildTable2
 WHERE ((childCol1,chialdCol2) IN ((:parentValueCol1_1,:parentValueCol2_1), (:parentValueCol1_2,:parentValueCol2_2), ..., (:parentValueCol1_1000,:parentValueCol2_1000)))
       OR  ((childCol1,chialdCol2) IN ((:parentValueCol1_1001,:parentValueCol2_1001), (:parentValueCol1_1002,:parentValueCol2_1002), ..., (:parentValueCol1_2000,:parentValueCol2_2000)))
       OR ((childCol1,chialdCol2) IN ((:parentValueCol1_2001,:parentValueCol2_2001), (:parentValueCol1_2002,:parentValueCol2_2002), ..., (:parentValueCol1_2500,:parentValueCol2_2500)))
    
-- AND SO ON, FOR NEEDED CHILD TABLES
...

添加或条件动态地将它们分成1000块 (由IN子句施加的限制)。因此，如果第一次查询检索到的记录数发生变化，则查询会发生变化，Oracle"does not recognize"查询，阻止从以前的运行中获得的任何优化。(在这一点上我错了吗？？)。此外，查询不是很快... ;-)

因此，随着我正在进行的优化，我正在重写查询，以便查询始终相同，无论在第一个查询中使用的搜索条件如何，对于后续查询，无论从第一个查询返回的结果数量如何。

第一个查询变成了:

SELECT parentField1, parendField2
  FROM ParentTable
 WHERE (:paramSearch1 IS NULL OR :paramSearch1 = column1)
   AND (:paramSearch2 IS NULL OR :paramSearch2 = column2)
   ...
   AND (:paramSearchX IS NULL OR :paramSearchX = columnX)

通过这种方式，查询始终是相同的，因此Oracle将选择必要的优化，因为此查询将随着时间的推移而使用。(对吗？)

其他查询，以实现相同的目标，从而避免动态连接或的各种条件，已经以这种方式重新定义:

首先连接从第一个查询获得的结果:

  ('parentValueCol1_1', 'parentValueCol2_1')#('parentValueCol1_2', 'parentValueCol2_2')#...#('parentValueCol1_2500', 'parentValueCol2_2500')

然后我将这个字符串作为参数传递给查询:

  SELECT *
  FROM MyChildTable1
  WHERE    :concatList IS NULL
       OR ('(''' || childCol1 || ''',' || childCol2 || ')' IN (    SELECT REGEXP_SUBSTR (:concatList
                                                                                                   , '[^#]+'
                                                                                                   , 1
                                                                                                   , LEVEL)
                                                                                 FROM DUAL
                                                                           CONNECT BY REGEXP_SUBSTR (:concatList
                                                                                                   , '[^#]+'
                                                                                                   , 1
                                                                                                   , LEVEL)
                                                                                         IS NOT NULL));

查询工作...但是，当然，现在限制是concatList参数的长度，限制为4000 (我不确定性能是否比以前版本的查询更好... :-))。在这种情况下，我应该拆分连接字符串，并动态添加或条件。但是，当然，最初的问题再次出现。

然后:
1) 我对始终执行相同的参数化查询的想法是否正确，并且Oracle会随着时间的推移而优化其执行？
2) “重言式技巧” 是首次查询的最佳方法吗？
3) 是否有关于第二个查询的实现以及连接字符串的限制的解决方案？
4) 解决整个问题的其他解决方案或遵循的策略？

提前谢谢。

1) Is it correct what I think about always executing the same parameterized query, and that Oracle optimizes its execution as it runs over time?

不完全是。对于adhoc搜索，您想为优化器提供绝对最佳的信息，我将继续使用您的初始方法，即仅根据需要添加谓词。(我假设您每秒没有运行数百次这些查询)。

2) Is the "tautology technique" the best approach for first query?

见上文。

3) Is there a solution regarding the implementation of the second query, and the limit on the concatenated string?
4) Other solutions, or strategies to follow, to solve the whole problem?

如果从query1返回的行数是任意的，我认为连接字符串或类似的是一个坏主意。不像你已经看到的那样缩放和达到限制。

我会将结果存储在全局临时表中。所以你的第一个查询变成:

插入我的gtt
选择parentField1
...

您的后续查询是:

选择 *
从我的表
where parent_id in ( 选择parentField1 from my_gtt );

在12.2中，您可以利用gtt的一些新功能和会话级别的统计信息，但在此之前，您可以自定义解决方案 (如果需要) 添加显式提示，以根据gtt中的行数 (您将通过sql % rowcount直接从插入中了解) 来管理第二个查询的执行;