Re: Indexes not used

This is intriguing. I have found the cost based optimiser usually gets things right unless the data is very skewed or the query is horrendously complex. Can you please post the query and the table sizes?

In the meanwhile, to force use of index you can use the hint

/* INDEX (table_name index_name) */

in your query

Ashish

"Anamika" <hemanir_at_yahoo.com> wrote in message news:5130f039.0203071401.2928935d_at_posting.google.com...
> Ashish,
> Yes, the second instance is taking more time and that is why I
> want to make it use the already existing valid indexes.
>
> -A
>
> "Ashish Mittal" <mittalashish_at_yahoo.com> wrote in message
news:<z5qh8.22371$e07.4587_at_sccrnsc01>...
> > Why do you want to use the index instead of the hash join? Is the query
> > taking more time?
> > "Anamika" <hemanir_at_yahoo.com> wrote in message
> > news:5130f039.0203050929.f10bc82_at_posting.google.com...
> > > hi,
> > > I have a ora db migrated from 8.0.5 to 8.1.6.1.
> > > Have schemas created by a third party application.
> > > In one instance, tkprof/explain plan output shows that indexes for
> > > tables being used. Nested loops used.
> > > In a different oracle instance (schema created by same third party
> > > software), the indexes are not being used. Full table scans are
> > > happening. Hash joins are used.
> > >
> > > Is it possible to make the second instance use the indexes for the
> > > same sql query ?
> > >
> > > Thanks,
> > > -A
Received on Sat Mar 09 2002 - 10:14:18 CST