problem with extermely slow process..

Hi All,

   I have a problem with performance.I am trying to generate a report by calling some packages in oracle. I have ascertained where exactly the bottleneck is.

I have a main program which calls a sub program.

within this suprogram (procedure) I have a driving cursor which is cursor
select *

   from sdms_nav_points
   where
   sdmsdsnav_id = p_id1;

my second cursor is

cursor c_dup is

   select count(*) kount from sdms_nav_points    where
   sdmsdsnav_id = p_id2
  and
(

( r_latitude = lv_latitude
  and
  r_longitude = lv_longitude));

where lv_latitude and lv_longitude are values i get from the c_initi cursor.

now my problem is for the second cursor if there query was exactly as shown above it would be fine ( there are indexes on r_latitude , r_longitude and sdmsnav_id).

so my second cursor becomes

cursor c_dup is

   select count(*) kount from sdms_nav_points    where
   sdmsdsnav_id = p_id2
  and
 abs(r_latitude-lv_latitude) < nvl(p_tolerance,0)  and
abs(r_longitude-lv_longitude) < nvl(p_tolerance,0);

now p_id1, p_id2 and p_tolerance are inputs into this procedure.

As soon as my query changes my process becomes painfully slow ( understandably as the indexes on r_latitude and r_longitude are not being used). I did try doing a hack which is as follows :

cursor c_dup is

   select count(*) kount from sdms_nav_points    where
   sdmsdsnav_id = p_id2
  and
(

( r_latitude = lv_latitude
  and
  r_longitude = lv_longitude));
  or
( r_latitude = lv_latitude + nvl(p_tolerance,0)
  and
  r_longitude = lv_longitude + nvl(p_tolerance,0) )   or
( r_latitude = lv_latitude - nvl(p_tolerance,0)
  and
  r_longitude = lv_longitude - nvl(p_tolerance,0) ))   ;

This way I do end up using the indexes on r_latitude and r_longitude , but the ors are also almost half as slow as the very first query.

I have over a million rows in my sdms_nav_points tables.

What do u guys recommend. It is taking almost 3 hrs or more to process a very small data set . What are function based indexes.

I have to maybe process arround more than 1000 pairs of p_id1 and p_id2 in one single report run.

Also for running explain_plan does each schema have to have thier own plan_table.

Also if someones using TOAD 6.3.3.1 how do you enable the plan_table within the tool.

Thanks in anticipation.

-- 
Ravi Alluru
Independent IS Consultant
796299 Alberta LTD
PH (403) 298-4805
Cell (403)607-8568
mailto:ravi_alluru_at_yahoo.com