Desktop productivity for business analysts and programmers

How can I use Oracle Hint in EG?

Reply
N/A
Posts: 1

How can I use Oracle Hint in EG?

I wanted to use a Oracle hint in my query in EG but don't know how to approach. Any suggestion? Thanks!
N/A
Posts: 0

Re: How can I use Oracle Hint in EG?

This can be done. I have done this, years ago.

1) You have to use the proc sql pass-through form of connection to Oracle

[pre]
proc sql;
connect to oracle as mycon (user=testuser password=testpass preserve_comments);
select *
from connection to mycon
(select /* +indx(empid) all_rows */
count(*) from employees);
quit;
[/pre]

2)
In the Oracle connection string, there is a required option

from "help" for SAS/ACCESS for Oracle, pass-through facility

PRESERVE_COMMENTS
enables you to pass additional information (called hints) to Oracle for processing. These hints might direct the Oracle query optimizer to choose the best processing method based on your hint.

You specify PRESERVE_COMMENTS as an argument in the CONNECT statement. Then you specify the hints in the CONNECTION TO component's Oracle SQL query. The hints are entered as comments in the SQL query and are passed to and processed by Oracle.
Ask a Question
Discussion stats
  • 1 reply
  • 220 views
  • 0 likes
  • 2 in conversation