New Optimizer Feature with 11g



Last updated on December 21st, 2015 at 07:04 am

New Optimizer Feature with 11g

1) Invisible indexes

With 11g release,index can be made invisible. They will be maintained all the time and can be made visible any time

alter index K invisible;

Optimizer will not use the index if it is invisible

alter index K visible

If want one query to use invisible index,we can set this parameter in the session
optimizer_use_invisible_index=true
Then the query in the session will use the invisible index

2)Extented stats on the table

If we use any function on the predicate in the query,the optimizer is not able to get the selectivity properly.With 11g we can create extented stats on the column for the function .

exec dbms_stats.create_extented_stats(ownname => ‘OWN’,tab_name=> ‘TAB’ ,extension=> ‘func(col)’);

Then optimizer will be able to find the correct selectivity


Leave a Reply