Re: Query optimization

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

[Quoted] What criteria have you used to determine that the normal cost-based-optimization is not working? You say you have verified this - but how?

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

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

[Quoted] 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. Received on Fri Jan 10 2014 - 15:44:46 CET