SQL & PL/SQL

Hello,

I have a problem with a query that I have. I just cant get the query to use the indexes. which cause a big problem for me. I would be really greatful if someone could help me figure out how to get the query to use the index.

Regards,
Jonas


Facts:

Oracle 10g Enterprise Edition 10.2.0.3.0 64-bit

Tables are:
------------------------------------------------------------------
MCSDATA (contains 1 617 389 329rows Data is from 20041001 until today)
ds_reference varchar2
detector_number number
timestamp date,
rest are data values.

partitioned on timestamp
have local indexes
MCSDATA_IDX0: timestamp
MCSDATA_IDX1: TO_NUMBER(TO_CHAR("TIMESTAMP",'YYYYMMDD')), ds_reference, detector_number


MCS_AGGR_DAY_TO_PROCESS (contains 900 rows, last days data)
ds_reference varchar2
detector_number number
datum varchar2
ctsid number

Then the query:

select
ctsid,
to_number(to_char(timestamp, 'YYYY')) ar,
to_number(to_char(timestamp, 'MM')) manad,
to_number(to_char(timestamp, 'DD')) dag,
...
madtp.ds_reference,
madtp.detector_number,
datum
from mcsdata md, mcs_aggr_day_to_process madtp
where TO_NUMBER(TO_CHAR("TIMESTAMP",'YYYYMMDD')) = to_number(madtp.datum)
and md.ds_reference = madtp.ds_reference
and md.detector_number = madtp.detector_number
and timestamp between to_date(datum,'YYYYMMDD') and (to_date(datum,'YYYYMMDD')+1)
group by
madtp.ds_reference,
madtp.detector_number,
ctsid,
datum,
to_number(to_char(timestamp, 'YYYY')) ,
to_number(to_char(timestamp, 'MM')) ,
to_number(to_char(timestamp, 'DD'))