Re: Query optimization

[Quoted] [Quoted] 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.
[Quoted] The query is as simple as this:

[Quoted]    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 ;-)

[Quoted] [Quoted] Anyway my client has solved the problem by setting the statistics to NULL.

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