Re: SQL subquery or PL/SQL

In article <3MAY199314315099_at_author.gsfc.nasa.gov> (comp.databases.oracle), chamblej_at_author.gsfc.nasa.gov (JEFF CHAMBLEE (512)) writes:
> Hello folk,
>
> The following SQL works but is inefficient because the
> subquery is repeated twice. I've tried a few different
> approaches using PL/SQL, but I only know enough PL/SQL to
> be a menace to society.
>
> How do you avoid doing that subquery twice?
>
> *********************************************************
> column statement format 99999
> column instruction format a35 word_wrapped
> column location format a6
> column obj_code format a8
> column symbol format a6
> column seq format 99999
>
> accept LOC number prompt 'Enter memory location: '
>
> select seq, location,loc_abs_rel,obj_code,obj_abs_rel,symbol,instruction
> from source_code_lst
> where seq + 5 >=
> (select seq
> from source_code_lst
> where location = &&LOC and loc_abs_rel = 'A')
> and seq - 5 <=
> (select seq
> from source_code_lst
> where location = &&LOC and loc_abs_rel = 'A')
> order by seq;
>
> undefine LOC
> ******************************************************
>
Apparently, you're trying to select values OUTSIDE the range assigned to &&LOC. If that's true, I see two ways to optimize the query: 1:

   select seq, location,loc_abs_rel,obj_code,obj_abs_rel,symbol,instruction

      from source_code_lst
      where seq + 5 >= 
           (select MAX(seq)           
              from source_code_lst
              where location = &&LOC and loc_abs_rel = 'A')
         and seq - 5 <=                                       
           (select MIN(seq)           
              from source_code_lst
              where location = &&LOC and loc_abs_rel = 'A')
      order by seq;

The use of MAX and MIN will prevent all rows from being returned. This will  increase the query speed greatly, especially if you are presently returning  a long list of values for seq.

2:
  select MIN(seq) - 5, MAX(seq) + 5 into hivalue, lovalue     from source_code_lst
    where location = &&LOC and loc_abs_rel = 'A'   select seq, location,loc_abs_rel,obj_code,obj_abs_rel,symbol,instruction     from source_code_lst
    where seq NOT BETWEEN lovalue and hivalue     order by seq

I hope these work for you.

    David Scott ...how can I keep from singing?

      SnailMail: 463 Windol Court, Marietta, GA 30066
          Phone: 404-514-0122
     Compuserve: 71740,3321
       Internet: dscott_at_cars.com