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
                                                                                                   , '[^#]+'
                                                                                                   , LEVEL)
                                                                                 FROM DUAL
                                                                           CONNECT BY REGEXP_SUBSTR (:concatList
                                                                                                   , '[^#]+'
                                                                                                   , LEVEL)
                                                                                         IS NOT NULL));