How to add hint for SQL using oracle view

A developer asked me to tune a SQL recently. It turned out that the column is very skewed, and Oracle cannot find the optimized plan. Analyzing the table column with histograms may solve the problem, may not solve the issue because it’s quite sensitive to data selected, and it may have side impacts to other SQLs. As the developer can change the SQL easily, I choose to add hints for the SQL. The SQL is based on view which is different from adding hints on tables. Here is the steps to add hints for a SQL on view:

Step 1. Get the sub-query name of the table we want to add hint on:

SQL>  explain plan for select parent_task_id,task_id, title, status_label from bugtracking where BUGTRACKING_STATUS <5;

Explained.

ARADMIN@TRACE:prod SQL> select * from table(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2453567761

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 25202 |  2928K|  3280   (5)| 00:00:19 |
|   1 |  NESTED LOOPS OUTER|       | 25202 |  2928K|  3280   (5)| 00:00:19 |
|*  2 |   TABLE ACCESS FULL| T508  | 25202 |  2534K|  3272   (5)| 00:00:19 |
|*  3 |   INDEX UNIQUE SCAN| IB508 |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$E213D7C1
   2 - SEL$E213D7C1 / T508@SEL$3  <-- this is the subquery name in SQL/view
   3 - SEL$E213D7C1 / B508@SEL$2

....

We want to add an index hint on table T508. It’s in query block SEL$3.

Step 2. Add hints at the subquery

SQL>  explain plan for       select/*+ index(@SEL$3 T508 I508_660000000_1) */ parent_task_id,task_id, title, status_label from bugtracking where BUGTRACKING_STATUS <5;

Explained.

ARADMIN@TRACE:prod SQL> select * from table(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2546157666

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  | 25202 |  2928K|  9024   (1)| 00:00:51 |
|   1 |  NESTED LOOPS OUTER          |                  | 25202 |  2928K|  9024   (1)| 00:00:51 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T508             | 25202 |  2534K|  9016   (1)| 00:00:51 |
|*  3 |    INDEX RANGE SCAN          | I508_660000000_1 | 25202 |       |    40   (3)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | IB508            |     1 |    16 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$E213D7C1
   2 - SEL$E213D7C1 / T508@SEL$3
   3 - SEL$E213D7C1 / T508@SEL$3
   4 - SEL$E213D7C1 / B508@SEL$2

As you can see, the index hint on view format is /*+ index(SUBQUERY_NAME TABLE_NAME INDEX_NAME) */