Re: LIKE performance

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 24 Feb 2005 18:05:22 -0800
Message-ID: <1109296941.329694@yasure>

lh wrote:

> "GreyBeard" <Fuzzy.GreyBeard_at_gmail.com> wrote in message 
> news:pan.2005.02.25.00.48.10.865706_at_gmail.com...
> 

>>On Fri, 25 Feb 2005 09:43:15 +1000, lh wrote:
>>
>>
>>>>Depending on the version of Oracle, and the frequency of the specific
>>>>query, you might consider a funcion based index.
>>>>
>>>>However, you leave us with far too little info to go beyond smarmy
>>>>guesses.
>>>>
>>>>FGB
>>>
>>>
>>>My smarmy apologies your Greybeardness.
>>
>>Sorry 'bout that. I never should type what I'm thinking <g>
>>
>>>Oracle 8i, the query is executed in a loop, the function is reading
>>>directories for files then checking the table to check that the file has
>>>been logged. The function is run regularly and with 10s of thousands of
>>>files it is taking way too long. This query is the bottleneck with a
>>>few seconds per query the current time. So I need a faster way to check
>>>for filenames in the table.
>>
>>Whatcha doing with the files? IOW, is there a way that intermedia could
>>help you, or the content management SDK?
>>
>>As a first shot, though, I'd try a fbi.
>>
>>FGB

> 
> 
> I did experiment with substr as an index, but it doesn't seem to use the 
> index after i add it.  It just does a table scan. 

Why is that a problem?

Is there a measured performance issue? If so what is it? Are your statistics current? Created how? What percentage of the rows are being retrieved? What is the cardinality?
etc. etc.

The fact that an index is not being used might mean nothing more than that not using the index is appropriate.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Feb 24 2005 - 20:05:22 CST