why the query is retreiving very slow

select count(charegeable_duration), sum ( case when length(charegeable_duration) = 6 then
to_number((substr(charegeable_duration,1,2)*60)*60) + to_number(substr(charegeable_duration,3,2)*60) + to_number(substr(charegeable_duration,5,2))
when length(charegeable_duration) = 5 then
to_number((substr(charegeable_duration,1,1)*60)*60) + to_number(substr(charegeable_duration,2,2)*60) + to_number(substr(charegeable_duration,4,2))
when length(charegeable_duration) = 4 then
to_number(substr(charegeable_duration,1,2)*60) + to_number(substr(charegeable_duration,3,2))
when length(charegeable_duration) = 3 then
to_number(substr(charegeable_duration,1,1)*60) + to_number(substr(charegeable_duration,2,2))
when length(charegeable_duration) = 2 then
to_number(substr(charegeable_duration,1,2))
when length(charegeable_duration) = 1 then
to_number(substr(charegeable_duration,1,1))
else 0
end ) seconds, substr(incoming_rout,1,3),date_start_charge

FROM MSC_SEPTEMBER_2008
WHERE
(INCOMING_ROUT like 'AWCC%'
AND (calling_party_num not like '11009370%' AND calling_party_num not like '11070%')
AND ( calling_party_num not like '12070%' AND calling_party_num not like '13070%' AND calling_party_num not like '14070%')
AND ( calling_party_num not like '1170%' AND calling_party_num not like '1270%' AND calling_party_num not like '1370%')
AND (calling_party_num not like '1470%' AND calling_party_num not like '129370%' AND calling_party_num not like '139370%')
AND ( calling_party_num not like '119370%' AND calling_party_num not like '149370%' AND calling_party_num not like '12009370%')
AND (calling_party_num not like '13009370%' AND calling_party_num not like '14009370%' AND calling_party_num not like '12010070%')
AND (calling_party_num not like '13010070%' AND calling_party_num not like '14010070%' AND calling_party_num not like '11010070%')
AND (calling_party_num not like '1201070%' AND calling_party_num not like '1301070%' AND calling_party_num not like '1401070%')
AND (calling_party_num not like '1101070%' AND calling_party_num not like '12010009370%' AND calling_party_num not like '13010009370%')
AND (calling_party_num not like '14010009370%' AND calling_party_num not like '11010009370%' AND calling_party_num not like '120109370%')
AND (calling_party_num not like '110109370%' AND calling_party_num not like '130109370%' AND calling_party_num not like '140109370%' )
AND (call_type = 'transit'))
OR
(INCOMING_ROUT like 'ROSH%' AND
(calling_party_num not like '11009379%' AND calling_party_num not like '11079%')
AND ( calling_party_num not like '12079%' AND calling_party_num not like '13079%' AND calling_party_num not like '14079%')
AND ( calling_party_num not like '1179%' AND calling_party_num not like '1279%' AND calling_party_num not like '1379%')
AND (calling_party_num not like '1479%' AND calling_party_num not like '129379%' AND calling_party_num not like '139379%')
AND ( calling_party_num not like '119379%' AND calling_party_num not like '149379%' AND calling_party_num not like '12009379%')
AND (calling_party_num not like '13009379%' AND calling_party_num not like '14009379%' AND calling_party_num not like '12010079%' )
AND (calling_party_num not like '13010079%' AND calling_party_num not like '14010079%' AND calling_party_num not like '11010079%')
AND (calling_party_num not like '1201079%' AND calling_party_num not like '1301079%' AND calling_party_num not like '1401079%' )
AND (calling_party_num not like '1101079%' AND calling_party_num not like '12010009379%' AND calling_party_num not like '13010009379%')
AND (calling_party_num not like '14010009379%' AND calling_party_num not like '11010009379%' AND calling_party_num not like '120109379%')
AND (calling_party_num not like '110109379%' AND calling_party_num not like '130109379%' AND calling_party_num not like '140109379%' )
AND (call_type = 'transit'))


OR

(INCOMING_ROUT = 'AFTLI' AND
(calling_party_num IS NULL) AND
(call_type = 'transit'))
or

(INCOMING_ROUT like 'MED%'
AND call_type = 'transit')
or
(INCOMING_ROUT like 'PTC%'
AND call_type = 'transit')

OR


(INCOMING_ROUT like 'ETI%'
AND (calling_party_num not like '11009378%' AND calling_party_num not like '11078%')
AND ( calling_party_num not like '12078%' AND calling_party_num not like '13078%' AND calling_party_num not like '14078%')
AND ( calling_party_num not like '1178%' AND calling_party_num not like '1278%' AND calling_party_num not like '1378%')
AND (calling_party_num not like '1478%' AND calling_party_num not like '129378%' AND calling_party_num not like '139378%')
AND ( calling_party_num not like '119378%' AND calling_party_num not like '149378%' AND calling_party_num not like '12009378%' )
AND (calling_party_num not like '13009378%' AND calling_party_num not like '14009378%' AND calling_party_num not like '12010078%' )
AND (calling_party_num not like '13010078%' AND calling_party_num not like '14010078%' AND calling_party_num not like '11010078%')
AND (calling_party_num not like '1201078%' AND calling_party_num not like '1301078%' AND calling_party_num not like '1401078%' )
AND (calling_party_num not like '1101078%' AND calling_party_num not like '12010009378%' AND calling_party_num not like '13010009378%')
AND (calling_party_num not like '14010009378%' AND calling_party_num not like '11010009378%' AND calling_party_num not like '120109378%')
AND (calling_party_num not like '110109378%' AND calling_party_num not like '130109378%' AND calling_party_num not like '140109378%' )
AND (call_type = 'transit'))

GROUP BY DATE_START_CHARGE,substr(incoming_rout,1,3)

Look at the queryplan of the query! Are proper indexes set on the tables?
What is the datatype of column calling_party_num?
Is it possible for you to change the WHERE clause for this column, because a LIKE or NOT LIKE operator is a slow process. Try and use a FULLTEXT index on this column...