Re: indexing a "path"

Steffen Ramlow 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
>
>
>

Steffen,

where path like '\a..\...\%' asumes a index range scan but path= is a table access by rowid (from index lookup). Have you analyzed the table and the index? Seems the optimizer calculates a full tale scan cheaper than index range scan + table access and it might be right (few rows) and db_file_multiblock_read_count set to high makes table scans *look*/*calculate* cheap. /svend Received on Sat Mar 16 2002 - 06:28:10 CST