BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wtien196838
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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. 

 

 

 

 

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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. 

 

 

 

 

 

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2337 views
  • 1 like
  • 3 in conversation