Re: Indexes with substr

In article <3B6A9171000245EB_at_ims2a.cp.tin.it>, "mpinzuti" says...
>
>I'd like to know whether the function SUBSTR() on a field uses the index
>eventually defined on that column.
>
>Thanks in advance
>
>

if the index is defined using the substr -- yes (function based index) else, no. In order to use an index on that field (and the index was on the field -- not on the substr of the field), you could

where field like 'ABC%'

instead of

where substr(field,1,3) = 'ABC'

this only works for "leading edge" situations of course (the index is pretty much not useful if you are looking for "where substr( field, 5, 2 ) = 'AB'"

See
http://osi.oracle.com/~tkyte/article1/index.html for more info on function based indexes.

>
>
>
>
>
>
>
>
>--
>Posted from vsmtp4.tin.it [212.216.176.224]
>via Mailgate.ORG Server - http://www.Mailgate.ORG

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp