Oracle extended statistics

Fortunately, starting from Oracle11g, the database can collect object statistics and histograms based on expressions or a group of columns to solve this problem. This new statistic is called

Fortunately, starting from Oracle11g, the database can collect object statistics and histograms based on expressions or a group of columns to solve this problem. This new statistic is called

When collecting the statistical information and histograms of a column, we usually collect the statistics of a column. When a predicate uses multiple related columns, the constraints are redundant. These related columns are also called associated columns. In this case, the Query Optimizer may make inaccurate judgments. Therefore, we must collect statistics or histograms for these related columns to describe this dependency.

Fortunately, starting from Oracle11g, the database can collect object statistics and histograms based on expressions or a group of columns to solve this problem. This new statistic is called extension statistics ).

This technique creates a Hidden Column Based on an expression or a group of columns, called extension, and then collects statistics and histograms on the Extended Column.

I. How to define extended Columns

You can call the create_extended_stats function of dbms_stats in Oracle. Expand the columns of the test table. For test table statements, see Collect table and column statistics in Oracle (). One is based on the expression upper (pad), and the other is based on a column group consisting of val2 and val3. In the test table, val2 and val3 have the same values and are highly correlated.

SELECT DBMS_STATS.CREATE_EXTENDED_STATS (OWNNAME => 'test ',
TABNAME => 'T ',
EXTENSION => '(upper (pad ))'),
DBMS_STATS.CREATE_EXTENDED_STATS (OWNNAME => 'test ',
TABNAME => 'T ',
EXTENSION => '(val2, val3 )')
From dual;

In this way, two extended columns are defined. They are expression-based and multi-column-based.

Ii. How to query Extended Column Information

You can query the Extended Column Information Based on user_stat_extensions, dba_stat_extensions, and all_stat_extensions.

SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, DATA_DEFAULT
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'T ';

COLUMN_NAME DATA_TYPE HID DATA_DEFAULT
---------------------------------------------------------------------------------------------
ID NUMBER NO
VAL1 NUMBER NO
VAL2 NUMBER NO
VAL3 NUMBER NO
PAD VARCHAR2 NO
SYS_STU0KSQX64 # I01CKJ5FPGFK3W9 VARCHAR2 yes upper ("PAD ")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 number yes SYS_OP_COMBINED_HASH ("VAL2", "VAL3 ")

From the data_default column, we can see that SYS_OP_COMBINED_HASH ("VAL2", "VAL3"), extended column statistics use the hash function, so val2 and val3 only use equal (=) the optimizer uses extended statistics only when the predicate is used.

Ii. How to delete extended statistics

The process drop_extended_stats provided by dbms_stats of Oracle is still used to delete extended statistics.

BEGIN
DBMS_STATS.DROP_EXTENDED_STATS (OWNNAME => 'test ',
TABNAME => 'T ',
EXTENSION => '(upper (pad ))');
DBMS_STATS.DROP_EXTENDED_STATS (OWNNAME => 'test ',
TABNAME => 'T ',
EXTENSION => '(val2, val3 )');
END;

Finally, the extended statistics is based on another new feature of Oracle11g-virtual columns. It does not store data, so what is its practical significance? We can imagine that in the development code, many SQL statements use upper (varchar2) and trunc (date). At this time, even though the index is created on these columns, the execution plan will not be indexed, to avoid full table scan, the best method is to rewrite the syntax. predicates should not be converted by functions as much as possible. However, sometimes a virtual column can be created when the conversion statement is not good, then create an index on the virtual column. For example, the following method:

Create table persons (
NAME VARCHAR2 (100 ),
Name_upper AS (UPPER (NAME )));

If upper (name) = 'Mike 'is used for frequent queries, name_upper = 'Mike' can be used, provided that the index is created for the virtual column. Of course, the virtual column is not bad. For example, you cannot specify all columns for inserting data because the virtual column does not store data.