Create stored outlines for changing explain plans
                                                 Last update (2012-11-26 15:02:31)
                                                                                                                   Date added (2012-11-26 12:11:18)

Summary
It is very likely in some cases the explain plan proposed by Oracle optimazer not to be the best. You can change it, without changing the statistics or the query hints with a stored outline which is matched with a specific plan of your choice.
1. First create the good query
CREATE OR REPLACE OUTLINE OUTLINE_2_GOOD ON 
SELECT /*+ index(A) */ * FROM TABLE A 
WHERE ID = 1;

2.Second define what is the bad query
CREATE OR REPLACE OUTLINE OUTLINE_2_BAD ON 
SELECT * FROM TABLE A 
WHERE ID = 1;

3. Switch explain plans.
UPDATE OUTLN.OL$HINTS SET OL_NAME = DECODE(OL_NAME, 'OUTLINE_2_BAD', 'OUTLINE_2_GOOD', 'OUTLINE_2_GOOD', 'OUTLINE_2_BAD') 
WHERE OL_NAME IN ('OUTLINE_2_BAD', 'OUTLINE_2_GOOD');

COMMIT;

4. Set the parameter in order to use stored_outlines
ALTER SYSTEM SET use_stored_outlines = TRUE;

Following the previous example, every time that is executed the sql
SELECT * FROM TABLE A WHERE ID = 1;
will always use the index hint.
Reviews
Categories
Filters
Search