substr or like

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 Received on Thu Jul 03 2008 - 09:47:55 CDT