This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.
I am using outlines because I am working with Oracle 10g, so I don't have SQL Plan Management until Oracle 11g.
I am not using SQL Profiles because I am working with PeopleSoft Global Payroll. I want complete stability of execution plans rather than have the optimizer produce 'better plans' with adjusted costing. Otherwise a single employee payroll calculation can put a different plan into the library cache which can then be used for a company-wide payroll calculation. I want to guarantee just one execution plan.
I'll start by creating a table to use.
Table T has 10000 rows. Column A is numbered 1 to 10000, B is numbered 10000 to 1, and there is a third column to provide some padding.
So, the table doesn't have any indexes. I will disable autotrace in SQL*Plus, and dynamic sampling to prevent collection of additional SQL outlines.
Without any indexes, Oracle can only do a full scan on the table.
I will now add the indexes and create another stored outline for the same statement, but in a different category.
With the indexes the execution plan includes contenation of queries on each index.
I will create a third stored outline in a third category so that I have two copies of the outline for comparison.
I am going to rename the outlines to match the category to make it easier to work in the rest of this example.
Looking at Outlines
We now have three outlines
And these are the hints in the outlines
Or, I could have queried from OUTLN.OL$HINTS
Adding a Hint
I can add a new hint, thus
Changing a Hint
However, in this case I want to change an exisiting hint from USE_CONCAT to NO_EXPAND.
The unchanged outline C2 still produces the concatenation
Now, let's try the outline that I updated directly.
I get a different execution plan that doesn't do CONCATENATION. Note that the outline still doesn't contain the NO_EXPAND hint that I put into the outline.