Нужен индекс с функцией sys_connect_by_path? Как его эмулировать?

У меня есть самоссылающаяся таблица в 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. Теперь мне нужно использовать ту же конфигурацию в представлении оракула.

Я хотел бы выбрать некоторые значения из представления по пути, но, к сожалению, это занимает 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.

Любые предложения, как я могу эмулировать индекс в столбце пути из представления «config»?


oracle indexing self-reference
person SWilk    schedule 09.03.2010    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Если ваши данные не часто меняются в 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). Это более сложно, так как вам нужно будет убедиться, что вы запускаете задание только один раз за транзакцию (например, используя переменную пакета). Опять же, это практично только в том случае, если вы не часто обновляете базовую таблицу.
person Vincent Malgrat    schedule 09.03.2010
comment
Спасибо, это кажется хорошей идеей. Я попробовал, но у меня есть две проблемы: 1. Я не могу создать индекс в столбце пути: ORA-01450: превышена максимальная длина ключа (3118). Кажется, что столбец слишком велик для индексации... Есть идеи, как это обойти? 2. Я не могу обновить mv в триггере после вставки, обновления или удаления, потому что транзакция выполняется (я редактирую конфигурацию от разработчика pl/sql). Есть ли способ обновить mv после фиксации? предложение ON COMMIT не работало (ORA-12054: невозможно установить атрибут обновления ON COMMIT для материализованного представления) - person SWilk; 09.03.2010
comment
Спасибо. Конфиг редко меняется, так что ваше решение подходит очень хорошо, но мне нужно убедиться, что никто не забудет обновить mv. Я думаю, что наконец-то сделаю веб-интерфейс для изменения конфига, но я посмотрю тему вакансий, это может потребовать меньше работы. Еще раз спасибо :) - person SWilk; 09.03.2010