I managed to solve it by adding an additional column ipbucket in which i store the trunc(clientip_value/1000000) and also storing in the ip lookup table trunc(FROM_IP_VALUE /1000000). Then we do a equi join based on that. So the range scan has to be done only on a lesser volume. To do this i had split the rows in IP_ADDRESS_DETAILS so that one row does not span in more than one bucket (i.e.) say if the range was from 10000000 to 25000000 then value from 10000000 to 19999999 in one bucket and 20000000 to 25000000 with the same location details. So new query will be

SELECT cv.visit_id,cv.client_id ,ida.country,ida.city,ida.area,
ida.latitude,ida.longitude
FROM   client_visit cv,ip_address_details ida
WHERE  cv.visit_date  >= trunc(sysdate-2)
AND cv.visit_date  < trunc(sysdate-1)
AND cv.ipbucket = ida.ipbucket
AND (cv.clientip_value >=  ida.from_ip_value) 
AND (cv.clientip_value < ida.to_ip_value)