Re: Query optimization

[Quoted] 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.

[Quoted] 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. Received on Mon Jan 13 2014 - 04:08:35 CET