Re: EXPLAIN Plan

fitzjarrell_at_cox.net wrote:
> Dereck L. Dietz wrote:
> > Earlier today my manager sent an EXPLAIN PLAN to a number of us for help
> > with a query. In the EXPLAN PLAN there was an INDEX RANGE SCAN which the
> > off-site DBA said was caused by the table not being analyzed.
> >
> > The INDEX RANGE SCAN was using an index - maybe not the one I'd expect but
> > nonetheless was using an index on the table being EXPLAINED.
> >
> > Am I missing something or does what the DBA not really make sense?
>
> Your DBA doesn't make sense:
>
> SQL> create table mytest as select * from all_objects where rownum <
> 50001;
>
> Table created.
>
> SQL> create index mytest_ctd on mytest(created);
>
> Index created.
>
> SQL> set autotrace on

[snip]
>
> The same plan is returned whether or not statistics were gathered (this
> is for 10.2.0.2) as dynamic sampling was employed when no statistics
> existed. The index range scan was employed because the WHERE condition
> included a range on an indexed value.
>
> I'd ask him to guess again.

Does what you say apply when there are incorrect statistics, like after a mass delete or mass load?

I'd say both there is not enough information to make such a judgement, and that explain plan may lie. "Not being analysed" does not necessarily mean "has never been analysed."

The real question may be, does "help with the query" mean, "analyse all the tables and see if it doesn't get better," or "drop everything else and immediately start researching traces in depth?" (Or, "get a dba on site?")

jg

--
@home.com is bogus.
"[Winning the Nobel Prize is] a great thrill. It's not quite the same
thrill as making the discovery." - George F. Smoot