FBI Not Used - Oracle Spatial

Hi All,

I have a JDBC batch job which executes a series of
inserts into an Oracle Spatial schema.  There are a
number of triggers which get fired during this load,
and one piece of SQL is particularly time-consuming:

SELECT a.value_id
  FROM mdsys.rdf_value$ a WHERE a.value_name.getURL()
= :v_subject AND a.value_type = :sv_type

This should be using a function-based index, and it
does suggest that it will do so when you execute an
"EXPLAIN PLAN FOR" the statement:

SQL> explain plan for
  2  SELECT a.value_id FROM mdsys.rdf_value$ a WHERE
a.value_name.getURL() = :v_
subject AND a.value_type = :sv_type
  3  /

Explained.

SQL> select operation,object_name
  2  from plan_table
  3  /

OPERATION                      OBJECT_NAME
------------------------------
------------------------------
SELECT STATEMENT
TABLE ACCESS                   RDF_VALUE$
INDEX                          RDF_VAL_NAMETY_IDX
 

Yet, tracing the JDBC job (run as same user), it
reports a full table scan instead:

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 46     (recursive depth: 1)

Rows     Row Source Operation
------- 
---------------------------------------------------
       1  TABLE ACCESS FULL RDF_VALUE$ (cr=378 pr=4
pw=0 time=269679 us)


This FTS is making things very slow.  This is a 10gR2
database so "QUERY REWRITE" should not be necessary (I
tried it anyway - no help).  

If anyone could suggest why the JDBC job is not using
the index, but explain plan is suggesting it should,
I'd be immensely grateful.

(I've checked the stuff I can think of: no stats
changes, no DDL changes, same user account).

Many thanks
Charlotte

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: