Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Forcing index usage...

Re: Forcing index usage...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 25 Apr 2006 08:06:49 +0100
Message-ID: <OeudnWZX-Y4OV9DZnZ2dnUVZ8qudnZ2d@bt.com>

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:e2isnq$so7$1_at_nntp.fujitsu-siemens.com...
> Hi!
> (9.2.0.6, linux)
> I'm in a situation where oracle spatial doesn't use a domain index even if
> I use the index hint.
> In fact, as long as the spatial operator (sdo_relate) is the only
> predicate in the where
> clause, the index gets used, so the index is okay.
> But as soon as I add more predicates or nest the query into another one,
> the index doesn't get used and the query takes forever.
>
> Is there anything I can do apart from the hint?
>
> I'm seriously considering splitting my query into two, the first one
> putting the spatial related results into a temporary table and the second
> one
> doing the rest with the intermediate result.
>
> Any thoughts on that?
>
> Lots of Greetings!
> Volker

Could you give us an example of the SQL
that uses the index, and an example that doesn't. Complete with execution plans.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Tue Apr 25 2006 - 02:06:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US