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 );
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...