deterministic function index
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