Hints in Oracle are used to increase performance. We can add hints to SQL statements in SQRs. Problem comes when we have keyword distinct in SQL statement. If we add hint as below, it will not be effective.
Normal Statement:
Begin-Select
/* +PARALLEL */
W.EMPLID
With Distinct (this will not be effective with the parallel hint):
Begin-Select distinct
/* +PARALLEL */
W.EMPLID
We cannot place the distinct after the parallel hint (as shown below), because SQR consideres distinct as a field name and or it will ignore space after distinct and treats distinctw as an alias.
Begin-Select
/* +PARALLEL */ distinct
W.EMPLID
Error from SQR log file
solution is to add the parallel hint as a table as shown below.
FROM PS_JOB W
, (SELECT /*+ PARALLEL */ 'X' FROM DUAL)
Normal Statement:
Begin-Select
/* +PARALLEL */
W.EMPLID
With Distinct (this will not be effective with the parallel hint):
Begin-Select distinct
/* +PARALLEL */
W.EMPLID
We cannot place the distinct after the parallel hint (as shown below), because SQR consideres distinct as a field name and or it will ignore space after distinct and treats distinctw as an alias.
Begin-Select
/* +PARALLEL */ distinct
W.EMPLID
Error from SQR log file
(SQR 5528) ORACLE OCIStmtExecute error 904 in cursor 19:
ORA-00904: "DISTINCTW"."EMPLID": invalid identifier
SQL: SELECT /*+ PARALLEL */ DISTINCTW.EMPLID
Begin-Select/* +PARALLEL */distinctW.EMPLIDError from SQR log file
(SQR 5528) ORACLE OCIStmtExecute error 936 in cursor 19:
ORA-00936: missing expression
SQL: SELECT /*+ PARALLEL */DISTINCT, W.EMPLID,
solution is to add the parallel hint as a table as shown below.
FROM PS_JOB W
, (SELECT /*+ PARALLEL */ 'X' FROM DUAL)
