Re: Advice on a weird query plan

From: Jonathan Lewis <>
Date: Thu, 17 Apr 2008 13:06:51 +0100
Message-ID: <>

"Dion Cho" <> 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(


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

    create index xxx on sysevents(

         nlssort (


with the predicate:

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

There is an interesting anomaly with dbms_stats, by the way. On my, 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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ 
Received on Thu Apr 17 2008 - 07:06:51 CDT

Original text of this message