| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
 |
 |