Re: Query Performance Problem

jimmy.liew_at_ogilvy.com (Jim) wrote in message news:<e7d269ce.0207290026.4749b76e_at_posting.google.com>...
> I have indexes on the relevant columns.
>
> But is it really better to have indexes rather than do a FTS? I
> remember keep reading somewhere that says when the query retrieves
> more than a certain percentage of the table, then the FTS is to be
> preferred.
>
>
> "Steve M" <steve.mcdaniels_at_vuinteractive.com> wrote in message news:<ahn9ab$t69$1_at_spiney.sierra.com>...
> > Which version of Oracle?
> >
> >
> > You need to "trick" the optimizer into using the indexes and tables in
> > the order you want.
> >
> > Some ways to do this:
> >
> > 1) Evaluate your execution plan with and without the tables being analyzed.
> > 2) use hints to suggest indexes to use first, second, etc.
> > 3) change the order of the tables in your FROM clause.
> > 4) change the multiblock_read_count (initSID.ora)
> > 5) force the optimzer into believing that indexes are more likely to be in
> > cache
> > (see documentation on "optimizer_index_cost_adj" and
> > "optimizer_index_caching")
> >
> > I suggest these things believing that you've alread built indexes on the
> > relevant fields.
> > If you haven't, well, then as Gilda would say: Never Mind.
> >
Okay, just my two cents worth. When I looked at your query, the line that jumped out at me was:

   and ind.ind_oup_code || ind.ind_ctrl_cell is null

If you have, for example, one index of ind_oup_code and one on ind_ctrl_cell, it likely won't use the index to answer the query... it can't totally null entries aren't made in an index.

What you might try a composite index (possibly two)

   on (ind_oup_code, ind_ctrl_cell)

                  and/or

   on (ind_ctrl_cell, ind_oup_code)

This may not solve the entire problem, but just a thought. Received on Mon Jul 29 2002 - 21:13:17 CEST