Need index with sys_connect_by_path function? How to emulate it?

Question

Hi,

I have a self referencing table in Oracle 9i, and a view that gets data from it:

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

The table stores configuration for PHP application. Now I need to use same config in oracle view.

I would like to select some values from the view by path, but unfortunately this takes 0,15s so it's unacceptable cost.

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

At first I thought of a function index on sys_connect_by_path, but it is impossible, as it needs also CONNECT BY clause.

Any suggestions how can I emulate an index on the path column from the 'config' view?

Answer 1

Hi SWilk,

If your data doesn't change frequently in the config_tab, you could use a materialized view with the same query as your view. You could then index the path column of your materialized view.

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

CREATE INDEX ix_config_path ON config (path);

Since this is a complex query, you would need to do a full refresh of your materialized view every time the base table is updated so that the data in the MV doesn't become stale.

Update

• Your column path will be defined as a VARCHAR2(4000). You could limit the size of this column in order to index it. In your query, replace sys_connect_by_path(...) by SUBSTR(sys_connect_by_path(..., 1, 1000) for example.
• You won't be able to use REFRESH ON COMMIT on a complex MV. A simple trigger won't work. You will have to modify the code that updates your base table to include a refresh somehow, I don't know if this is practical in your environment.
• You could also use a trigger that submits a job that will refresh the MV. The job will execute once you commit (this is a feature of dbms_job). This is more complex since you will have to check that you only trigger the job once per transaction (using a package variable for example). Again, this is only practical if you don't update the base table frequently.