substr or like

From: <>
Date: Thu, 3 Jul 2008 07:47:55 -0700 (PDT)
Message-ID: <>

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.


Chmanu Received on Thu Jul 03 2008 - 09:47:55 CDT

Original text of this message