Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the difference between dbms_stat and analyze". The explanation in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "what is the difference between dbms_stat and analyze" together!

[IT168 Knowledge Base]

Since Oracle 8.1.5 introduced the dbms_states package, Experts have recommended dbms_states instead of analyze. following reasons

dbms_stats can be parsed in parallel

dbms_stats has automatic analysis function (alter table monitor )

analyze inaccuracies in statistical information some times

[@more@]

1,2 Easy to understand, and the second point is actually the most attractive in VLDB;3 was vague before, read metalink236935.1 explanation, analyze When analyzing the Partition table, sometimes inaccurate Global statistics are calculated.

The reason is that dbms_stats actually analyzes table global statistics (when parameters are specified); analyze summarizes table partition (local) statistics into table global statistics , which may lead to errors.

If you want to analyze the entire user or database, you can also use toolkits, which can be analyzed in parallel

Dbms_utility(kit before 8i)

Dbms_stats(toolkits available after 8i)

as

dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);

dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

This is a summary of commands and toolkits

For partitioned tables, it is recommended to use DBMS_STATS instead of the Analyze statement.

a) Can be done in parallel, for multiple users, multiple tables

b) You can get data for the entire partition table and data for individual partitions.

c) Compute Statistics can be calculated at different levels: single partition, subpartition, full table, all partitions

d) statistics can be poured out

e) Automatic collection of statistical information by users

Disadvantages of DBMS_STATS

a) Cannot Validate Structure

b) Cannot collect CHAINED ROWS, cannot collect CLUSTER TABLE information, these two still need to use the Analyze statement.

c) DBMS_STATS does not analyze indexes by default because Cascade is False by default and needs to be manually specified as True.

3. For External Table in oracle 9, Analyze cannot be used, only DBMS_STATS can be used to collect information.

Thank you for reading, the above is "dbms_stat and analyze what is the difference" of the content, after the study of this article, I believe that we have a deeper understanding of the difference between dbms_stat and analyze this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.