deterministic function index

From: <chmanu_at_gmail.com>
Date: Fri, 9 May 2008 08:36:48 -0700 (PDT)
Message-ID: <46a7a9e2-6a53-4c38-9235-28780c540370@p25g2000hsf.googlegroups.com>


Hello,

I explain my pb : i've created a table with 3 columns ( t ( col1, col2, col3) )

For performance reasons, i've created a function get_col3(v1, v2 ) returning v3 which is col3%type type and is used in a view.

My queries are very much less cost than previously.

Now, when i want to query with a filter on get_col3(v1, v2 ) field, i have to create a function index (with get_col3 function). A problem appeared when i want to update or insert into my table t : ora-04091 (table string.string is mutating, trigger/function may not see it)
I understand that the select query into the index function can't know which value to take. So, I 'm now using PRAGMA AUTONOMOUS_TRANSACTION parameter.
The problem now is that the sql execution plan doesn't indicate what is the indexes using in the view statement and my query is quite long (but not as long as when there was no index). How can i know it ?

Best regards,

Chmanu Received on Fri May 09 2008 - 10:36:48 CDT

Original text of this message