Thanks for the reply. The ending condition can be any length. That's why I thought about using a UDF to rearrange to string. Even if it is not optimizable, shouldn't it take less time with the index rather than rushmore creating an index on the fly?





>>I have a table with 750,000 records containing product information. One of the fields is "description". I have written an autodownloader that retrieves the vendors files electronically and imports everything into one centralized product catalog.
>>
>>I have been given a new requirement to filter the import process based on user definable conditions, i.e. manufacturer part no begins with 'XXXX' or description ends with ACAD.
>>
>>I can optimize most expressions by creating indexes on the condition, but the "ends with " condition is not optimizable. If I have the condition "description ends with 'ACAD'", the command: RIGHT(ALLTRIM(UPPER(DESCRIPTION)), 4) = 'ACAD' is not optimizable and takes nearly 4 minutes to update the visiblity flag.
>>
>>I thought that if i can reverse the description string and the condition, I could use a simple "=" and make it optimizable, i.e. Reverse(ALLTRIM(UPPER(DESCRIPTION))) = 'DACA'.
>>
>>Is there a way to index a table on a UDF? How does one do it?
>>
>>Thanks..
>
>You can index on most functions, including user-defined functions. However, any index using UDFs are not optimzable by Rushmore. If you know you are always going to use the last 4 characters then you can build an index using the first expression above. Then to make sure Rushmore kicks in, use the exact same expression for the comparison. If you just have an index on upper(Description), it doesn't match and Rushmore will not be invoked.
>
>Ex.
>select * from mytable where RIGHT(ALLTRIM(UPPER(DESCRIPTION)), 4) = 'ACAD' into cursor mycursor
>
>That is an optimizable expression if it matches the index.
>
>HTH.