Date: Fri, 21 Nov 2008 14:48:49 -0600
Reply-To: Mary <email@example.com>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Mary <mlhoward@AVALON.NET>
Subject: Re: table lookup with inequalties in predicate
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.
connect to oracle( );
execute (create table temp1 as select ....) by oracle;
disconnect from oracle();
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.
----- Original Message -----
Sent: Friday, November 21, 2008 2:34 PM
Subject: table lookup with inequalties in predicate
Oracle table t1 contains grp and 4 scores.
Number of recs in t1 is 3 billion.
Oracle table t2 contains grp and MIN and MAX vales for each of the 4
Number of recs in t2 is 200K
I am performing a table lookup of segment using the following
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?