Oracle-L: RE: update statistics

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 09 Oct 2002 13:39:03 -0800
Message-ID: <F001.004E4F77.20021009133903@fatcity.com>

Saira

I'm assuming that Informix UPDATE STATISTICS analyzes the data in a table or multiple tables and makes fresh data available for the cost-based analyzer. If that is true, the answer depends somewhat on the version of Oracle you are using. The simple answer is to enter the statement:

ANALYZE TABLE <table_name> ESTIMATE STATISTICS; That works on all Oracle versions, but is a little difficult to automate. Do not ever analyze Oracle tables owned by SYS. On newer Oracle versions, you can take a look at the DBMS_STATS package. It can analyze all tables in a schema with a single command. It also uses fewer resources than ANALYZE. This has had a few bugs in certain Oracle versions, like analyzing SYS tables, so check out the comments that people have made about it. But if your intention is to analyze all your tables regularly, this is probably what you will want to use. For Lawson, I don't think analyzing the tables is critical. Lawson adds a hint to each query, and Oracle "takes the hint". But if you are using a report generator outside Lawson, you may want to make this a practice.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, October 09, 2002 2:50 PM To: Multiple recipients of list ORACLE-L

What is the equivalent in Oracle of the Informix UPDATE STATISTICS?

Thanks much.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Saira Somani
INET: saira_somani_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------