Re: Indexes on views

Steve Jelfs wrote:
>
> bialik_at_wis.weizmann.ac.il wrote:
> >
> > Hi.
> >
> > I got similar problem when joining 9-10 tables. In my case
> > hint /*+ INDEX ( <table_name> <index_name> ) */ solved the
> > problem.
> >
> > Good luck. Michael.
> >
> > In article <353F3C4A.36B9_at_nospambtinternet.com>,
> > Steve Jelfs <s.jelfs_at_nospambtinternet.com> wrote:
> > >
> > > I have a view on several (14) tables. All the tables are the same (they
> > > have the same columns etc, it's just they are organised into weeks) and
> > > they have an index which when I query the view I make sure I hit.
>
> Thanks, but the index names are changed weekly because the tables are
> cycled on a rolling 14 weeks.
>
> It clearly seems to be a bug. Just a thought, but could I get the index
> name and then use dbms_sql to build the query?
>
> Sj

Have the tables been modified? Don't forget to do an ANALYZE TABLE. The optimizer can do unexpected things when the statistics are stale.

One of the few clear rules for tuning SQL in ORACLE is ANALYZE, ANALYZE, ANALYZE. good luck

  ed Received on Fri Apr 24 1998 - 14:05:29 CDT