SQL Hints and Table Aliases

When a hint is added to a query, the Oracle optimizer uses it to choose an execution plan, unless some condition exists that prevents the optimizer from doing so. This post draws your attention to one specific condition that will definitely prevent the optimizer from using the hint.

Note that it is recommended that hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using EXPLAIN PLAN.

If you have to use hints, however, you need to pay attention to this very basic rule if the hint requires a tablespec:

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.

The following is an example that demonstrates the above rule.

eddie@DB112> SELECT banner FROM v$version;

BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

eddie@DB112> CREATE TABLE my_table
  2  NOPARALLEL
  3  AS
  4     SELECT *
  5       FROM all_objects
  6      WHERE ROWNUM <= 10000;

Table created.

eddie@DB112> EXECUTE dbms_stats.gather_table_stats(user,'MY_TABLE');

PL/SQL procedure successfully completed.

In the following query, the PARALLEL hint instructs the optimizer to use a parallel operation. Notice that the table has no alias and the hint uses the table name.

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

As expected, the plan shows that a parallel operation was indeed used. Now let’s add an alias to the table and keep everything else the same:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

Oops! No parallel operation was performed. The hint was totally ignored. The plan is the same as if the hint was not there:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

But, when we use the table alias instead of the table name, the optimizer obeys the hint:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (t) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Imagine for example a data warehouse process that relies on the PARALLEL hint to finish its SQL aggregation just before the data is fed to a sales report used by management every morning. A developer changes an aggregation query adding an alias to the table but forgetting to change the hint. Oops!

Yes, this is a very basic rule but could have serious consequences if forgotten.

Related articles:

• Keep first, Keep last
• DUAL behavior change
• Oracle SQL tip

Filed in Oracle, Tips on 26 Oct 10 | Tags: hint, sql

---