substr or like
in [Oracle]
|
Prev: Before turning to oracle support.......component 'DBMS_SYS_SQL' must be declared
Next: What is "automated batching of data from computer to computer" in Named User License?
From: chmanu on 3 Jul 2008 10:47 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
From: gym dot scuba dot kennedy at gmail on 3 Jul 2008 10:57 <chmanu(a)gmail.com> wrote in message news:772b4783-ff9c-4eba-abe7-b1fe1942d5f9(a)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
From: Mark D Powell on 3 Jul 2008 12:20 On Jul 3, 10:47 am, chm...(a)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 --
From: chmanu on 3 Jul 2008 12:44
> 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 -- Thanks for your responses, i have launched the statistic script cause they re was not up todate and now, the two queries take the same time : 14 second. All my tests are done many times and i take those when pre load is ok. The most cost is a table full scan on the substr query which increase the time but without be more than with like operator query (using a index). I will use a substr index and it will be better. Chmanu |