Re: Advice on a weird query plan

"Dion Cho" <ukja.dion_at_gmail.com> wrote in message news:7ac1133a-e35f-4ccb-a6b3-
exec dbms_stats.gather_table_stats(..., method_opt=> 'for all columns size auto', ...);

alter session set query_rewrite_enabled = true; alter session set query_rewrite_integrity = trusted;

select * from t where check_decode(state) = 'processed' -- this will scan table
select * from t where check_decode(state) = 'ready' -- this will seek index

Works same, but shows the result of more efficient use of space.

Dion Cho

You beat me to it - the change in index was going to be a follow-up suggesting if the NLS stuff worked out.

There is a better option than your pl/sql, though, the nullif() function:

    create index xxx on sysevents(

        nullif(status,'processed')
    );

or in the case of the original poster, the rather messier NLS equivalent:

    create index xxx on sysevents(

         nlssort (
              nullif(status,'processed'),
              'nls_sort=''BINARY_CI'''
         )

with the predicate:

     nullif(status,'processed') = 'ready'

There is an interesting anomaly with dbms_stats, by the way. On my 10.2.0.3, when you try to call gather_table_stats with a method opt that tries to collect histograms for columns, the call fails with a spurous ORA-00979 (not a group by column) - but only if you try to gather with the nls_comp and nls_sort set (as the OP has). I think this may be a known bug.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html