Re: Advice on a weird query plan

On Apr 16, 6:31 pm, Dion Cho <ukja.d..._at_gmail.com> wrote:

>
> Congratulation for problem being solved.
> But, why you're not using dbms_stats yet? Just from curiostiy. :-)
>
> Besides, i don't think that state column is appropriate for index.
> Actually it's the most inefficient index imagineable from the point of
> view of space abuse.
>
> As an alternative, how about following technique?
>
> create or replace function check_decode(state in varchar2) return
> varchar2
> deterministic
> is
> begin
> if lower(state) = 'processed' then
> return null;
> else
> return lower(state);
> end if;
> end;
> /
>
> create index i on t(check_decode(state));
>
> 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 ...
>
> read more »

Thanks Dion for the advice.

In regards to your question, no, I haven't learned the syntax for dbms_stats particularly well yet, so if I'm testing stuff, I tend to use the syntax I know which is the analyze table syntax. As you can probably gather from my other posts, I'm not very up on the newer Oracle releases. I've kind of been thrust into trying to optimize this particular application without a whole lot of recent Oracle experience (and I was far from an expert back in the day).

To ask the stupid question though:

Is there anything dbms_stats does that the old analyze table doesn't do? I always though it was just a convenience function, but if I'm missing something there, I'd like to know what I'm missing :).

As to your suggestion, I really only have control over what happens below the query horizon here, so I can index all I want, move tables around, etc. What I can't do is modify the structure of the inbound queries b/c thoe are being generated by application code I don't have control over. If I could control that, I'd have made state an int instead of a varchar2 in the first place.

Is there any way I could benefit from the kind of index you describe w/ o rewriting the inbound query? Received on Wed Apr 16 2008 - 22:33:48 CDT