Re: Query optimization

On Mon, 13 Jan 2014 13:24:38 GMT, "Kirby Grant" <kgrant_at_whatzit.org> wrote:

>
>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?

All I know is that the table/indexes were created with these values as defaults.

-- 
(\__/)  M.
(='.'=) If a man stands in a forest and no woman is around
(")_(") is he still wrong?