Re: indexing a "path"

What happened when you forced Oracle to use the index? Did it go slower? If it did, the optimizer made the right decision not to use the index.

Jaap.

On Fri, 15 Mar 2002 13:41:41 +0100, "Steffen Ramlow" <s.ramlow_at_gmx.de> wrote:

>we are managing a folder structure in a table like this
>
>id parentid path
>1 null \1
>2 1 \1\2
>
>etc.
>
>a index is created on path and we exceute a "deep" search in a folder and
>its subfolders via:
>
>where path like '\1\...\%'
>
>the field id is a varchar and always contains 10 characters (sample above is
>simplified)
>
>so, is it a problem that many values on path start with the same string?
>
>and why doesn't oracle 8.1.7 use the index (statistics are up to date) but
>performs a table scan - even an optimizer hint does not change this, only
>switching to optimizer mode "rule" helps (and we really don't want to set or
>instance to "rule"...)
>
>p.s.
>
>in a query like: path = '\1\...' the index is used
>
>
Received on Sat Mar 16 2002 - 05:30:07 CST