Re: substr or like

<chmanu_at_gmail.com> wrote in message
news:772b4783-ff9c-4eba-abe7-b1fe1942d5f9_at_s50g2000hsb.googlegroups.com...
> Hello everyone,
>
> I'm looking for increasing my database performance, I ve just noticed,
> in a heavy query that using substr instead of like is very faster :
> 350ms vs 30s !!
>
> But in the other hand, when i count all the element in the table with
> the 2 method, the like is better :
>
> select count(*) from a_t_c where col2 like 'P%' ==> 188ms
> end select count(*) from a_t_c where substr(col2, 1, 1) = 'P' ==> 1s
>
> I wonder if it is not due to the execution plan building because
> there's many index with col2 but full scan is needed, in the second
> case, oracle know that it has to scan table because others conditions
> are more restrictive.
>
> How can i know what is the time to build the execution plan ?
>
> What do you think about these results ? i think i will use a index on
> substr(col2, 1, 1) but i'd like to understand the 2 behaviours.
>
> Thanks
>
> Chmanu

An index on col2 will be able to handle the case of like 'P%' and like 'Pi%'... An function based index of substr(col2,1,1) will only handle what is the 1st character of the column. I would do the more common query in this case.
Jim Received on Thu Jul 03 2008 - 09:57:15 CDT