What is the Oracle PL/SQL "--+rule"?

user_3519108 0
Viewed 3712

I am working with legacy SQL code and I am finding a lot of queries like the following:

SELECT --+rule
  username,
  usernotes
FROM
  userinfotable
ORDER BY
  username

I read the Oracle Optimizer Hints documentation, but I can't find an exact reference for a --+rule. I am thinking this rule is possibly an obsolete artifact from a code generation tool that may have been designed to replace "--+rule" with user or generated /*+ SQL */ hint code.

What do you think? Does the --+rule code [literally] in the above example actually do anything as-is? or can I just discard it?

Platform = Delphi 6 with Direct Oracle Access components, Oracle 10g2 with last supported updates. Most of the Legacy SQL code was developed when using Oracle 7 and 8.

plsql oracle
1 Answers

The answer is in the referenced documentation you have given:

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|MERGE|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|MERGE|SELECT|UPDATE} --+ hint [text] [hint[text]]...

The --+ hint format requires that the hint be on only one line.

So it was an allowed syntax rule for hints: but I think I have never seen it.

In Oracle SQL "rule" hint means use the Rule Based Optimizer (RBO) instead of CBO (Cost Based Optimizer): since Oracle 10 it is no more supported. So for Oracle you cannot discard it: it should be taken into account but without support ...

10.2 doc says:

Rule-based Optimization (RBO) Obsolescence

RBO as a functionality is no longer supported. RBO still exists in Oracle 10g Release 1, but is an unsupported feature. No code changes have been made to RBO and no bug fixes are provided. Oracle supports only the query optimizer, and all applications running on Oracle Database 10g Release 1 (10.1) should use that optimizer. Please review the following Oracle Metalink desupport notice (189702.1) for RBO:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_ database_id=NOT&p_id=189702.1

You can also access desupport notice 189702.1 and related notices by searching for "desupport of RBO" at:

http://metalink.oracle.com

Notice 189702.1 provides details about the desupport of RBO and the migration of applications based on RBO to query optimization.

Some consequences of the desupport of RBO are:

CHOOSE and RULE are no longer supported as OPTIMIZER_MODE initialization parameter values and a warning is displayed in the

alert log if the value is set to RULE or CHOOSE. The functionalities of those parameter values still exist but will be removed in a future release. See "OPTIMIZER_MODE Initialization Parameter" for information optimizer mode parameters. ALL_ROWS is the default value for the OPTIMIZER_MODE initialization parameter. The CHOOSE and RULE optimizer hints are no longer supported. The functionalities of those hints still exist but will be removed in a future release. Existing applications that previously relied on rule-based optimization (RBO) need to be moved to query optimization.

user_107268500
Related