Date:         Fri, 21 Nov 2008 14:48:49 -0600
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: table lookup with inequalties in predicate
Comments: To: pscb100@GMAIL.COM
Content-Type: text/plain; charset="iso-8859-1"

One thing you might strongly consider is to do them in Oracle; if you need to use SAS then use pass-through
SQL rather than SAS SQL, as this will do the query on the server where Oracle resides,  not on your own machine.

proc sql;
    connect to oracle( );
    execute (create table temp1 as select ....) by oracle;
    disconnect from oracle();
quit;

If you have Oracle's SQL Developer on your desktop, then you could also put in the Oracle SQL code directly using 
that product.   You'd want to create a  table on Oracle rather than passing back a result.

The code looks OK to me; if you have Oracle SQL Developer, you may attempt to do an EXPLAIN to see if your code will hit the indexes
on the appropriate variables; you'd at least need an index on grp on both tables.

-Mary
  ----- Original Message ----- 
  From: pscb100@GMAIL.COM 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Friday, November 21, 2008 2:34 PM
  Subject: table lookup with inequalties in predicate


  Oracle table t1 contains grp and 4 scores.
  grp
  grr1
  grr2
  grr3

  Number of recs in t1 is 3 billion.

  Oracle table t2 contains grp and MIN and MAX vales for each of the 4
  scores.
  grp
  grr1_min_sc
  grr1_max_sc
  grr2_min_sc
  grr2_max_sc
  grr3_min_sc
  grr3_max_sc
  grr4_min_sc
  grr4_max_sc

  Number of recs in t2 is 200K


  I am performing a table lookup of segment using the following
  statement.

  select t2.segment
  from t1, t2
  where  t1.grp = t2.grp and
         (t1.grr1 >= t2.grr1_min_sc and
          t1.grr1 < t2.grr1_max_sc) and
          (t1.grr2 >= t2.grr2_min_sc and
          t1.grr2 < t2.grr2_max_sc) and
          (t1.grr3 >= t2.grr3_min_sc and
          t1.grr3 < t2.grr3_max_sc) and
          (t1.grr4 >= t2.grr4_min_sc and
          t1.grr4 < t2.grr4_max_sc)

  The lookup table (t2) is set up such that each row of t1 will lookup
  exactly one segment in t2.

  Is there a more effecient way of performing the lookup? The above code
  may run for a couple of days even when running parallel lookups
  against a partitioned t1 table.

  Is this kind of lookup a candidate for the SAS HASH method or any
  other SAS method?

  thanks