Oracle 19C 创建在工作负载监视期间检测到的列组

例如，运行以下查询：

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;

输出示例如下：

###########################################################################
EXTENSIONS FOR SH.CUSTOMERS_TEST
................................
1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                     :SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###########################################################################

数据库创建了两个列组customers_test：一个列组用于过滤谓词，一个列组用于GROUP BY操作。

运行GATHER_TABLE_STATS以收集以下统计信息customers_test：

EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

检查USER_TAB_COL_STATISTICS视图以确定数据库创建了哪些其他统计信息：

SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
ORDER BY 1;

部分样本输出如下所示：

CUST_CITY                               620 HEIGHT BALANCED
...
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_          145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N          620 HEIGHT BALANCED

本示例显示了从DBMS_STATS.CREATE_EXTENDED_STATS函数返回的两个列组名称。在CUST_CITY，CUST_STATE_PROVINCE和COUNTRY_ID上创建的列组具有高度平衡的直方图。

以下示例显示了customers_test表中两个查询的解释计划：

EXPLAIN PLAN FOR
  SELECT *
  FROM   customers_test
  WHERE  cust_city = 'Los Angeles'
  AND    cust_state_province = 'CA'
  AND    country_id = 52790;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
 
EXPLAIN PLAN FOR
  SELECT   country_id, cust_state_province, count(cust_city)
  FROM     customers_test
  GROUP BY country_id, cust_state_province;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

新计划显示了更准确的基数估计：

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |  1093 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |  1093 |
----------------------------------------------------
 
8 rows selected.
 
Plan hash value: 3050654408
 
-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
9 rows selected.