DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

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

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


Accepted Solutions
Solution
‎07-28-2016 08:36 AM
Super User
Posts: 17,819

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

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 Smiley Wink 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. 

 

 

 

 

 

View solution in original post


All Replies
Solution
‎07-28-2016 08:36 AM
Super User
Posts: 17,819

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

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 Smiley Wink 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. 

 

 

 

 

 

Respected Advisor
Posts: 3,890

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

[ Edited ]

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 250 views
  • 0 likes
  • 3 in conversation