FBI Not Used - Oracle Spatial
- From: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Mar 2006 09:38:39 -0800 (PST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
