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

What this article shares with you is about the extended statistical information characteristics of Oracle. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Oracle 11g adds the feature of extended statistics in statistics collection. It can collect statistics on related columns in a table or on function expressions. So that the selection rate, cost estimation is more accurate, but also easier to follow the correct implementation plan. The benefits of collecting statistics on relevant columns are obvious. For example, there is a logical relationship between the two columns, but if only the statistics of the two columns are collected separately, the selection rate of {(An AND B) is calculated according to the multi-conditional selection rate: OPSEL [a] * An OR B [b]; (OPSEL [a] + OPSEL [b]-OPSEL [b]) (the selection rate of NOT An is: 1-OPSEL [a])}, the estimated selection rate may deviate greatly.

You can collect extended statistics for associated columns or expressions. The associated column means that, assuming that there is a world population table, using the predicate country = 'Denmark' and language =' Danish', these two restrictions are likely to apply to the same batch of records for most of the records in this table. In fact, most Danish speakers live in Denmark, and most people living in Denmark speak Danish. In other words, these two constraints are almost redundant, and such columns are often called associative columns (correlated column), which also poses a problem for the optimizer. This is because there are no object statistics or histograms to describe the dependencies between data. In other words, the query optimizer actually assumes that the data stored on different columns is irrelevant.

The following tests:

DB Version:11.2.0.4

-generate test data

Drop table scott.test01 purge

Create table scott.test01 as select * from dba_objects

Update object_name to be the same as object_type for testing.

Update scott.test01 set object_name=object_type

Commit

1. Collect individual column statistics and view the execution plan

-- collect single-column statistics

Exec dbms_stats.gather_table_stats ('SCOTT','TEST01')

-- View the number of rows in the table

Select table_name,num_rows from dba_tables where owner = 'SCOTT' and table_name =' TEST01'

TABLE_NAME NUM_ROWS

TEST01 87048

-- generate the execution plan of the statement

Explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'

SQL > select * from table (dbms_xplan.display ())

As you can see here, the estimated number of rows returned is 41, which is obviously a far cry from the actual number.

-- estimate the number of rows

Select rpad (column_name, 30,') column_name

Rpad (num_distinct, 8,') num_distinct

Rpad (utl_raw.cast_to_varchar2 (low_value), 15,') low_value

Rpad (utl_raw.cast_to_varchar2 (high_value), 10,') high_value

Rpad (num_nulls, 8,') num_nulls

Rpad (avg_col_len, 6,') avg_col_len

Rpad (density, 20,') density

Histogram

From dba_tab_col_statistics

Where owner = 'SCOTT'

And table_name = 'TEST01'

And column_name in ('OBJECT_NAME',' OBJECT_TYPE')

SQL > col COLUMN_NAME for A15

SQL > col LOW_VALUE for A15

SQL > col HIGH_VALUE for A15

SQL > select rpad (column_name, 30,') column_name

2 rpad (num_distinct, 8,') num_distinct

3 rpad (utl_raw.cast_to_varchar2 (low_value), 15,') low_value

4 rpad (utl_raw.cast_to_varchar2 (high_value), 10,') high_value

5 rpad (num_nulls, 8,') num_nulls

6 rpad (avg_col_len, 6,') avg_col_len

7 rpad (density, 20,') density

8 histogram

9 from dba_tab_col_statistics

10 where owner = 'SCOTT'

11 and table_name = 'TEST01'

12 and column_name in ('OBJECT_NAME',' OBJECT_TYPE')

The estimated number of rows returned is 41, which is obtained by multiplying the density of two columns by the number of rows of the table, 0.021739130434782602173913043478268704841.13799621641

two。 Collect multi-column extension statistics and view the execution plan

-- collect multi-column extended statistics

Exec dbms_stats.gather_table_stats ('scott','test01',method_opt = >' for columns (object_name,object_type)')

-- generate the execution plan of the statement

Explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'

SQL > select * from table (dbms_xplan.display ())

-- the actual number of rows returned by the query:

SQL > select count (*) from scott.test01 where object_name='INDEX' and object_type='INDEX'

COUNT (*)

-

5078

As you can see here, the estimated number of rows returned by the execution plan is 4986, which is basically close to the actual number of 5078 rows returned.

PS:

1. To expand the collection of statistics, you can use

Select dbms_stats.create_extended_stats ('scott','test01',' (object_name,object_type)') from dual creates extended statistics columns, and then dbms_stats.gather_table_stats ('scott','test01') collects statistics; you can also create extended statistics and collect statistics at the same time in the method_opt attribute in dbms_stats.gather_table_stats. For example, the following:

Dbms_stats.gather_table_stats ('scott','test01',method_opt = >' for columns (object_name,object_type)')

2.oracle 11g can collect not only multi-column extension statistics, but also function and expression extension statistics.

These are the extended statistical information features of Oracle, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.