topic Re: Does Oracle use an index for an inner join? in SAS Programming

Does Oracle use an index for an inner join?

ScottBass — Thu, 06 Mar 2014 23:05:07 GMT

Hi,

This post might best be posted to an Oracle forum, but I thought I'd test the waters here. Also, I hope this is the correct SAS Communities forum - please advise if you disagree. I don't see a forum for "access to external database" questions.

I'm modifying an existing SQL pass through query to Oracle. The previous query returned either one, or only a small number, of rows from a large Oracle table. The query was coded as a code generated where clause and then passed to Oracle. There are two variables in the query (where) criteria, and Oracle is indexed on these variables.

I have to modify the query to get the list of records to return from another SAS dataset. I've chosen to upload that dataset to Oracle temp space, then use an inner query to filter the data (via Oracle pass through).

In the previous query, even though the code generation looked "klunky", it would return say 5 records in say 1-2 seconds. My query is now returning about 1000 records in say 20 minutes.

Some example code may help to illustrate the issue. You'll have to use your imagination for the Oracle pass through bit, but this will run on your system and hopefully this helps explain things better:

* simulate Oracle temporary space, which I can write to via SAS/Access to Oracle ;

libname tmpora "%sysfunc(pathname(work))";

* this simulates the large table in Oracle ;

data work.class;

set sashelp.class;

run;

* this simulates the upload of the filter criteria to Oracle temp space ;

data tmpora.filter;

set sashelp.class;

where name le "L";

run;

* option 1- this simulates the existing query ;

* this actual code is a bit klunkier than this, but the same approach applies ;

* however, this approach is actually quite performant ;

* my query could generate 1000's of name/age pairs - I'd have to worry about hitting the macro variable length limit ;

* I'd need a different code generation approach. ;

proc sql noprint _method;

select catx(" AND ", "(" || cats("NAME=",quote(strip(name))), cats("AGE=",age) || ")") into :where separated by " OR "

from tmpora.filter

;

%put &where;

create table test1 as

select *

from work.class

where &where

;

quit;

* option 2 - this simulates my query rewrite ;

* this approach performs much worse than the first approach ;

* I suspect, but can't be sure, that Oracle is sorting the large table ;

* in order to do the inner join. ;

* Is there a "hint" I can give to force Oracle to use the existing index ;

* on the large table (name+age)??? ;

* Also, is there the equivalent of _method in Oracle SQL to get diagnostic ;

* information about how the query is executed??? ;

proc sql noprint _method;

create table test2 as

select a.*

from work.class a

inner join

tmpora.filter b

on a.name=b.name and a.age=b.age

;

quit;

* the two approaches yield the same results ;

proc compare base=test1 compare=test2;

run;

As above, two questions:

1) Is there a "hint" I can give to force Oracle to use the existing index (name+age) on the large table, rather than sorting it?

2) Is there the equivalent of _method in Oracle SQL to get diagnostic information about how the query is executed?

Thanks,

Scott

Re: Does Oracle use an index for an inner join?

FriedEgg — Fri, 07 Mar 2014 04:45:40 GMT

In Oracle, the syntax equivalant of the _method option is referred to as an explain plain and requested as follows:

EXPLAIN PLAN FOR

SQL_Statement;

proc sql;

connect to oracle();

execute by oracle( EXPLAIN PLAN SET statement_id = 'my_explain_plan1' FOR

SQL_Statement;

);

disconnect from oracle;

quit;

Another step will be to analyze not your written sql, but the sql which SAS will actually generate to execute within oracle. To get this information you will need to run it first with the sastrace system options set:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

In the log, you can grab the SQL executed inside Oracle based on your implicit SAS SQL statement, paste it into the explicit sql above and get your explain plan returned.

One you have run your explain plan, you will need to collect and print it from the plan table.

proc print data=ora.plan_table; where statement_id='my_explain_plan1'; run;

*then cleanup the plan table;

proc sql;

connect to oracle();

execute by oracle( delete from plan_table where statement_id='my_explain_plan1'; commit;);

disconnect from oracle;

quit;

So, clearly not as straight forward or simple, but possible...

In response to your inquiry about Oracle hint, there is a index hint, which you can use. Given the specific circumstances at hand you will need to determine the best optimization plan for your query. Here is a link for reference the this: Oracle Hints SQL PL/SQL

Re: Does Oracle use an index for an inner join?

ScottBass — Mon, 10 Mar 2014 23:47:40 GMT

Thanks Fried Egg, much appreciated.

My code is something like:

proc sql;

connect to oracle (user=&ora_userid password="&ora_passwd" path=&ora_path);

create table workspde.my_sas_table as

select * from connection to oracle

(

select

blah, blah, blah

from

foo a

left join

bar b

a.key1=b.key1

and

a.key2=b.key2

left join

blah c

c.key3=b.key3

and

c.key4=b.key4

where

c.somedate >= a.anotherDate

);

disconnect from oracle;

quit;

I assume in this scenario SAS is not generating the SQL for Oracle, but rather passing the code as written directly to Oracle?

Nevertheless, I'll try what you've suggested and see if I can track down the performance issue. You've also given me some good searches for Google.

Regards,

Scott