Force Oracle 10g to use 9.2.0 optimizer for specific query only

There is a parameter optimizer_features_enable which by default is set to 10.2.0.x in Oracle 10.2.0.x. Changing this to 9.2.0 cause that all features of 10.2.0 optimizer are disabled. If you need optimizer 9.2.0 to be applied in only one specific query (because there are no possibilities to use hints, playing with statistics also doesn't help, and execution plan from ver 9.2.0 performs much better), you can do this using stored outlines. This is how to do this:

1. alter system set use_stored_outlines = TRUE

2. create outline OUTLN_1 on <your statement>;

3. create private outline OUTLN_1_PRV from OUTLN_1;

4. select * from <schema>.ol$hints where name='OUTLN_1_PRV'

5. Now you have modify your private outline:

a) first of all you need to find in output from point 4 hint# containining OPTIMIZER_FEATURED_ENABLED and change this:
update <schema>.ol$hints set
hint_text='OPTIMIZER_FEATURES_ENABLE(''9.2.0'')'
where ol_name = 'OUTLN_1_PRV' and hint#=x;

b) also you need to reset access path, so need to find (also from output 4) line containing something like FULL, INDEX etc, and delete this:
delete from <schema>.ol$hints where ol_name='OUTLN_1_PRV'
and hint#=x;

6. exec dbms_outln_edit.refresh_private_outline('OUTLN_1_PRV');

7. create or replace outline OUTLN_1 from private OUTLN_1_PRV;