需要使用 sys_connect_by_path 函数进行索引吗？如何效仿呢？

Question

我在 Oracle 9i 中有一个自引用表，以及一个从中获取数据的视图：

CREATE OR REPLACE VIEW config AS
SELECT c.node_id,
       c.parent_node_id,
       c.config_key,
       c.config_value,
       (SELECT c2.config_key 
          FROM vera.config_tab c2 
         WHERE c2.node_id = c.parent_node_id) AS parent_config_key,
       sys_connect_by_path(config_key, '.') path,
       sys_connect_by_path(config_key, '->') php_notation
  FROM config_tab c
CONNECT BY c.parent_node_id = PRIOR c.node_id
 START WITH c.parent_node_id IS NULL
 ORDER BY LEVEL DESC

该表存储 PHP 应用程序的配置。现在我需要在 oracle 视图中使用相同的配置。

我想通过路径从视图中选择一些值，但不幸的是这需要 0.15 秒，所以这是不可接受的成本。

SELECT * FROM some_table
 WHERE some_column IN (
   SELECT config_value FROM config_tab WHERE path = 'a.path.to.config'
 )

起初我想到了 sys_connect_by_path 上的函数索引，但这是不可能的，因为它还需要 CONNECT BY 子句。

有什么建议如何从“配置”视图模拟路径列上的索引吗？

Answer 1

如果 config_tab 中的数据不经常更改，您可以使用 具体化视图与您的视图具有相同的查询。然后，您可以为物化视图的 path 列建立索引。

CREATE MATERIALIZED VIEW config
   REFRESH COMPLETE ON DEMAND 
   AS <your_query>;

CREATE INDEX ix_config_path ON config (path);

由于这是一个复杂的查询，您需要执行 完全刷新物化视图，以便 MV 中的数据不会过时。

更新

• 您的列path 将被定义为VARCHAR2(4000)。您可以限制该列的大小以便对其建立索引。例如，在查询中，将 sys_connect_by_path(...) 替换为 SUBSTR(sys_connect_by_path(..., 1, 1000)。
• 您将无法使用 REFRESH ON COMMIT 对复杂的 MV 不起作用，您必须修改更新基表的代码以包含刷新，我不知道这在您的环境中是否实用
• 。提交将刷新 MV 的作业的触发器。该作业将在您提交后执行（这是 dbms_job 的一项功能），因为您必须检查是否只触发该作业。每个事务执行一次作业（例如使用包变量），这仅在您不经常更新基表时才实用。