Default Value of estimate_percent and cascade when dbms_stat is used to collect statistics

The dbms_stats package can be used to collect statistics. The following syntax is usually used: exec dbms_stat.gather_table_stats (ownname => 'xxx', tabname => 'xxx', estimate_percent => xxx, method_opt => 'xxx', cascade => xxx );

Among them, estimate_percent indicates the selected sampling ratio. If it is too low, the collection speed will be fast, but it may not be very accurate. If it is too high, the collection speed will be slow, but it is relatively accurate, and each has its own advantages and disadvantages. However, the default method exec dbms_stat.gather_table_stats (ownname => 'xxx', tabname => 'xxx') is also supported. What is the sampling ratio here?

Make an experiment:

SQL> create table t2 as select * from dba_objects;
Table created.

SQL> select count (*) from t2;
COUNT (*)
----------
11218

SQL> create index idx_t2 on t2 (object_id );
Index created.

SQL> exec dbms_stats.gather_table_stats (ownname => 'sys ', tabname => 't2 ');
PL/SQL procedure successfully completed.

Query the dba_tables table. The NUM_ROWS value is 11218, indicating that the sampling ratio is 100%.

Query the dba_indexes table and check that the index IDX_T2 has a value in the related statistical column. The index is also analyzed, that is, the default value of CASCADE is TRUE.

Query the dba_ind_columns table again and you can see that the column has a value, such as COLUMN_POSITION and COLUMN_LENGTH. It also proves that the default value of CASCADE is TRUE.

Summary:

Use dbms_stats.gather_table_stats (ownname => 'sys ', tabname => 't2') to collect statistics,

1. The default estimate_percent value is 100% (in fact, estimate_percent can be set to NULL, which is the same as 100%. I understand that the value of NULL is the same as the value of not set. It is passed according to the three-field theory, if no estimate_percent is set, the default value is 100% ).

2. The default value of CASCADE is TRUE. This means that statistical information is collected from tables, indexes, and columns.