topic Re: Please explain the purpose of statement Select ... parallel ..within proc sql in SAS Programming

Please explain the purpose of statement Select ... parallel ..within proc sql

wtien196838 — Fri, 22 Jul 2016 22:30:49 GMT

Hi All,

My company uses extensively the code below when sas programs access to the server.

Proc SQL;

connect to oracle as conn (user=xxx password=xxx path=xxx);

create table test_table as

select * from connection to conn

(SELECT %STR(/)%STR(*)+PARALLEL(a,8) NO_INDEX(a) %STR(*)%STR(/)

a.* , c.var1

from tableA a,

tableC c

where a.A_id =c.C-id );

quit;

What is the purpose of above BOLD statement ? I only know that it boosts up processing efficiency for sql .

Thanks for all supports.

Best Regards,

WT1968

Re: Please explain the purpose of statement Select ... parallel ..within proc sql

Reeza — Fri, 22 Jul 2016 22:37:53 GMT

You're using direct pass through to SQL and this is Oracle code not SAS code.

It would look like:

/* Parallel(a,8) No_index(a) */

From a quick google search and guessing based on the words 😉 it's saying allow parallel processing and don't use an index on table a.

Terminology wise they're called Optimizer Hints:

https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5300

You may want to find the correct reference for your version of Oracle.

Anyways, hope that helps.

Re: Please explain the purpose of statement Select ... parallel ..within proc sql

Patrick — Sat, 23 Jul 2016 08:18:43 GMT

Just on a side note: If you would use "preserve_comments" then you could write the hint directly without any macro "quoting".

Proc SQL;
  connect to oracle as conn (user=xxx password=xxx path=xxx preserve_comments);
  create table test_table as
    select * from connection to conn
    (SELECT  /*+ PARALLEL(a,8) NO_INDEX(a) */
      a.* ,  c.var1
    from tableA a,
      tableC c
    where a.A_id =c.C-id );
quit;

And on another side note:

I had to use such optimizer hints lately quite a bit and therefore than some reading about it. One thing the Oracle DBA's were often not so happy about was that once "users" learned about these hits, they've started to use them extensively even in case where it didn't make sense. The problem is: Parallelism can put quite a bit of load onto the database so if there are too many processes running in parallel at the same time then the overall effect of such parallelism can become contra-productive.