Re: substr or like

On Jul 3, 10:47 am, chm..._at_gmail.com wrote:
> 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

Run the built-in SQL trace facility on the two SQL statements or use auto-trace. Remember the effect that having some of the data buffered from a prior run will have on subsequent runs so if possible pre-load the data or take an average of the followup executions of the SQL to base you analysis on.

Parse information is available via the trace. See Performance and Tuning manual for instructions on use.

HTH -- Mark D Powell -- Received on Thu Jul 03 2008 - 11:20:56 CDT