Re: Query optimization

[Quoted] On 13-Jan-2014, Mark <i_at_dontgetlotsofspamanymore.invalid> wrote:

> On Mon, 13 Jan 2014 03:08:35 GMT, "Ken" <ktsahl_at_yoohoo.com> wrote:
>
> >
> >On 10-Jan-2014, Mark <i_at_dontgetlotsofspamanymore.invalid> wrote:
> >
> >> On Fri, 10 Jan 2014 14:44:46 GMT, "Ken" <ktsahl_at_yoohoo.com> wrote:
> >>
> >> >
> >> >On 10-Jan-2014, Mark <i_at_dontgetlotsofspamanymore.invalid> wrote:
> >> >
> >> >> I have a question about index usage in table queries. We need to do
> >> >> fast queries on new tables. Therefore the standard method of
> >> >> generating table statistics does not work. We have verified that
> >> >> Oracle is not using the indexes.
> >> >>
> >> >> Assuming that the tables are less than 1 day old and have 1-2Mio
> >> >> records/hour written to them what is the best way to achieve this?
> >> >>
> >> >> An added complication is that the tables are referred to by synonyms
> >> >> as the user has no way to predict the actual table name.
> >> >> --
> >> >
> >> >You really haven't supplied enough information.
> >>
> >> I'm not sure what information is necessary.
> >>
> >> >What criteria have you used to determine that the normal
> >> >cost-based-optimization is not working? You say you have verified this
> >> >-
> >> >but
> >> >how?
> >>
> >> A read query is running very slowly. My client has informed my that
> >> it is not using the index. I believe him.
> >>
> >> >Operating just on what you have provided I would have to say that
> >> >your
> >> >queries must be malformed. That is normally the only reason for the
> >> >failure
> >> >of the optimizer to use statistics.
> >>
> >> It's a really simple query so it's hard to understand how it could be
> >> wrong. It used to work more quickly until the tables were dropped and
> >> recreated.
> >> The query is as simple as this:
> >>
> >> SELECT column FROM table WHERE x = 'a' AND y = 'b' AND z = 1;
> >>
> >> There is an index on x, y and z.
> >>
> >>
> >> >However, even if you can't use cost-based optimization and must go
> >> >with
> >> >rule-based optimization, there are still ways to tune queries. There
> >> >is
> >> >plenty of information on this if you do web searches, but some real
> >> >quick
> >> >rules of thumbs is that in the FROM clause, the table that supplies
> >> >the
> >> >greatest amount of data should be listed first and then joins should
> >> >be
> >> >done
> >> >to subsidiary tables in order of the amount of data that is derived
> >> >from
> >> >those tables. For the WHERE clause, apply the filters in the order of
> >> >greatest cardinality to lowest cardinality.
> >>
> >> See above.
> >>
> >> >However, you really should rewrite your queries so that they will be
> >> >able
> >> >to
> >> >use the cost-based optimizer. One of the primary rules for that is to
> >> >never
> >> >use functions on indexed columns referenced in the WHERE clause unless
> >> >you
> >> >have created function-based indexes. If ultimately you need that in
> >> >your
> >> >final query then use a on-commit materialized view to perform
> >> >transformations on the columns and then index the result on the
> >> >materialized
> >> >view. Ultimately there are probably no situations that cannot be
> >> >resolved
> >> >in
> >> >a manner that will allow the use of cost-based optimization.
> >>
> >> I've no idea what this means ;-)
> >>
> >> Anyway my client has solved the problem by setting the statistics to
> >> NULL.
> >
> >If the query was working correctly until the table was dropped and
> >re-created, were the indexes also re-created? Indexes are automatically
> >dropped when a table is dropped.
>
> The indexes were re-created correctly.
>
> >If each of those columns is indexed separately, then drop all three
> >indexes
> >and recreate a clustered index with the column in the order of
> >cardinality -
> >if you get the wrong order then performance will suffer. One note though,
> >if
> >all columns have low cardinality then indexes really are of little use
> >since
> >indexes are most effective when cardinality is high.
>
> The aforementioned index works on all those three columns, in order of
> cardinality. The first column in the index has very high cardinality.

How often are statistics refreshed? If a large percentage of the table changes frequently, the statistics will become stale. Also, when the statistics are refreshed is it done with the compute or estimate method? If it is the estimate method, what is the percentage used? Received on Mon Jan 13 2014 - 14:24:38 CET