Re: substr or like

From: gym dot scuba dot kennedy at gmail <>
Date: Thu, 03 Jul 2008 14:57:15 GMT
Message-ID: <fN5bk.178$rb1.97@trndny08>

<> wrote in message
> 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

Original text of this message