Re: problem with extermely slow process..

From: Jim Kennedy <kennedy-family_at_home.com>
Date: Sun, 25 Nov 2001 00:35:14 GMT
Message-ID: <61XL7.71572$XJ4.39826977@news1.sttln1.wa.home.com>

In the last query have you tried using between? It looks like you are trying to find points or places that are near a specified point. (that's how I am reading tolerance). Do you really want to consider points that are near (based upon a specified tolerance or 0 if no tolerance is specified) instead of exactly equal?

e.g.
( 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) ))

becomes
(
( r_latitude> = lv_latitude + nvl(p_tolerance,0)
  and
  r_longitude> = lv_longitude + nvl(p_tolerance,0) )  and
( r_latitude <= lv_latitude - nvl(p_tolerance,0)
  and
  r_longitude <= lv_longitude - nvl(p_tolerance,0) )) )

A function based index helps when you are transforming the data in a column or columns via some function (the formal definition of a function - in Math - is a process that maps values that are one to one and on to other values. so f(x)=sqrt(x) is not a function because it is not one to one.). So if you were using sine(r_longitude) then you could create a function based index e.g. create index my_index on myTable(sine(r_longitude))...; You have to have query_rewrite=trusted for this to work. Jim

  "Ravi Alluru" <ravica_at_quixnet.net> wrote in message news:9tpa57$e9o$1_at_newstest.laserlink.net...   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 Received on Sat Nov 24 2001 - 18:35:14 CST