Re: Does table monitoring handle all statistics for you?

Your example demonstrates, perhaps, of why generic advice is not necessarily helpful -

If the table data is timestamped, and the queries usually for very recent times, and is indexed on the timestamp.

Then consider the table at the end of Jan when the table has been analyzed for data ranging

    1st Jan to 31st Jan.

A query for 24th to 31st Jan will cover a large fraction of the data (in the eyes of the CBO). So the query will do a tablescan.

Now jump to 7th feb, without analyzing the table again, The data covers

    1st Jan to 7th Feb
The typical query is now for 1st Feb to 7th Feb.

The optimizer thinks that the data stops at 31st Jan, so decides that this query will return (virtually) no rows. Consequently it uses the index.

To decide on the requirements for analysis, you need to investigate every important query individually, to decide what aspects of the data distribution will affect it.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html




Brian Tkatch wrote in message <3b390a0b.349477375_at_news.alt.net>...



>

>There is one table with over 532,000 records right now, and it gets

>anywhere between a few hundred to a few thousand additional records

>per day. If I let it go a week without updating statistics, a specific

>query takes noticeably longer. Does it make sense to update statistics

>on that one table daily?

>