CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimization
junior
junior

Reputation: 37

How can I speed up the query?

How can I speed up this query?

 l_string varchar2(200);
 /* parameter string */
 l_string := ':27.07.2015:10.07.2015:23.07.2015:01.08.2015:'; 

 select  t3.*
  from  table1 t1, table2 t2, table3 t3
 where  t1.col1 = t2.col2 
   and  t2.col3 = t3.col4
   and  (instr(l_string, ':' || to_char(t3.col1, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col2, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col3, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col4, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col5, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col6, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col7, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col8, 'dd.mm.yyyy') || ':') > 0 OR
         instr(l_string, ':' || to_char(t3.col9, 'dd.mm.yyyy') || ':') > 0 );

Upvotes: 0

Views: 82

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21073

Your predicate with instr doesn't allow index access. So you are ending with full table scan of T3. You may formulate the predicate diferently as:

 where col1 in (to_date('27.07.2015','dd.mm.yyyy'),
           to_date('10.07.2015','dd.mm.yyyy'),
           to_date('23.07.2015','dd.mm.yyyy'),
           to_date('01.08.2015','dd.mm.yyyy')) or
  col2 in (to_date('27.07.2015','dd.mm.yyyy'),
           to_date('10.07.2015','dd.mm.yyyy'),
           to_date('23.07.2015','dd.mm.yyyy'),
           to_date('01.08.2015','dd.mm.yyyy')) or
  col3 in (to_date('27.07.2015','dd.mm.yyyy'),
           to_date('10.07.2015','dd.mm.yyyy'),
           to_date('23.07.2015','dd.mm.yyyy'),
           to_date('01.08.2015','dd.mm.yyyy')) or
 .... -- etc for columns col3 - col9

If the table T3 is large and the predicate above selectes only few records, you may profite with defining indexes on col1 to col9

 create index t3_ix1 on t3(col1);
 create index t3_ix2 on t3(col2);
 ....

The possible execution plan will perform 9 * 3 (columns * values) INDEX RANGE SCAN and BITMAP CONVERSION to get the OR result. So for realy huge T3 the 27 index range scan will be better that FULL SCAN an dyou will speed up; but it depends on your data...

Upvotes: 1

Michael Humelsine
Michael Humelsine

Reputation: 601

I do not have an Oracle instance to test, but OR conditions almost always slow down execution as does calling instr multiple times. Try doing one large concatenation and searching for the value in there. You'll notice I added a '~' to make sure you didn't get a match from data due to the concatenation:

select  t3.*
  from  table1 t1, table2 t2, table3 t3
 where  t1.col1 = t2.col2 
   and  t2.col3 = t3.col4
   and  instr(l_string,
         (':' || to_char(t3.col1, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col2, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col3, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col4, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col5, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col6, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col7, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col8, 'dd.mm.yyyy') || ':' ||
         '~:' || to_char(t3.col9, 'dd.mm.yyyy') || ':') > 0

I would explore other options, but without more information that's all I can offer.

Upvotes: 0

Related Questions