Re: Query optimization

[Quoted] On Fri, 10 Jan 2014 08:24:45 -0800 (PST), ddf <oratune_at_msn.com> wrote:

>On Friday, January 10, 2014 6:14:23 AM UTC-7, Mark 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.
>>
--snip--
>
>If Oracle determines that more than 30% of the table data will be returned then the cost of an index access path is far greater than the cost for a table scan which is why the second run used full table scans rather than the indexes. Note that when statistics were NOT gathered dynamic sampling caused Oracle to use the indexes. This example was run using 11.2.0.3; you may be running an older release (a fact you did not present in your original post) so please report which release of Oracle you are using.

All the selects should only return one row.

Version is 11.1.0.7.0.

>Also, please report how you determined that the indexes are not being used; you posted no execution plans to prove your assertion.

I was told this by the DBA. I don't know how to do this with Oracle. I usually work with Ingres.

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