代码之家  ›  专栏  ›  技术社区  ›  SWilk

需要使用sys\u connect\u by\u path函数索引吗?如何模仿?

  •  1
  • SWilk  · 技术社区  · 15 年前

    我在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
    

    我想从view by path中选择一些值,但不幸的是,这需要0,15秒,因此这是不可接受的成本。

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

    一开始我想在sys\u connect\u by\u path上建立一个函数索引,但这是不可能的,因为它还需要connect by子句。

    有什么建议可以让我在“config”视图的path列上模拟索引吗?

    1 回复  |  直到 15 年前
        1
  •  2
  •   Community CDub    5 年前

    config_tab ,你可以用 materialized view 使用与视图相同的查询。然后可以索引 path 具体化视图的列。

    CREATE MATERIALIZED VIEW config
       REFRESH COMPLETE ON DEMAND 
       AS <your_query>;
    
    CREATE INDEX ix_config_path ON config (path);
    

    因为这是一个复杂的查询,所以您需要 full refresh 每次更新基表时,使MV中的数据不会过时。

    • 你的专栏 路径 VARCHAR2(4000) . 您可以限制此列的大小以对其进行索引。在查询中,替换 sys_connect_by_path(...) 通过 SUBSTR(sys_connect_by_path(..., 1, 1000)
    • 您将无法在复杂MV上使用提交时刷新。一个简单的触发器不起作用。您将不得不修改更新基表的代码以包含刷新,我不知道这在您的环境中是否实用。
    • dbms_job ). 这更复杂,因为您必须检查每个事务只触发一次作业(例如使用包变量)。同样,这只有在不经常更新基表的情况下才实用。