Oracle 19C 何时创建直方图

原创 Asher.HU 2021-02-04

1236

如果DBMS_STATS收集表的统计信息，并且查询已经引用了该表中的列，则Oracle数据库会根据以前的查询工作量，根据需要自动创建直方图。

基本过程如下：

您DBMS_STATS将METHOD_OPT参数设置为default 的表运行SIZE AUTO。
用户查询表。
数据库会记录前面查询中的谓词，并更新数据字典表SYS.COL_USAGE$。
您DBMS_STATS再次运行，导致DBMS_STATS查询SYS.COL_USAGE$以确定基于先前查询工作量的哪些列需要直方图。

该AUTO功能的后果包括：

由于查询随着时间DBMS_STATS而变化，因此可能会更改其收集的统计信息。例如，即使表中的数据没有更改，查询和DBMS_STATS操作也会导致引用这些表的查询计划发生更改。
如果您收集表的统计信息并且不查询表，则数据库不会为该表中的列创建直方图。为了使数据库自动创建直方图，必须运行一个或多个查询以填充中的列使用情况信息SYS.COL_USAGE$。

示例11-1自动创建直方图

假定该sh.sh_ext外部表包含与该sh.sales表相同的行。您创建新表sales2并使用sh_ext作为源执行批量加载，这会自动为创建统计信息sales2。您还可以如下创建索引：

SQL> CREATE TABLE sales2 AS SELECT * FROM sh_ext;
SQL> CREATE INDEX sh_12c_idx1 ON sales2(prod_id);
SQL> CREATE INDEX sh_12c_idx2 ON sales2(cust_id,time_id);

您查询数据字典以确定sales2列是否存在直方图。由于sales2尚未查询，因此数据库尚未创建直方图：

SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM 
  2  FROM   USER_TAB_COL_STATISTICS 
  3  WHERE  TABLE_NAME = 'SALES2';

COLUMN_NAME   NOTES          HISTOGRAM
------------- -------------- ---------
AMOUNT_SOLD   STATS_ON_LOAD  NONE
QUANTITY_SOLD STATS_ON_LOAD  NONE
PROMO_ID      STATS_ON_LOAD  NONE
CHANNEL_ID    STATS_ON_LOAD  NONE
TIME_ID       STATS_ON_LOAD  NONE
CUST_ID       STATS_ON_LOAD  NONE
PROD_ID       STATS_ON_LOAD  NONE

您查询sales2product的行数42，然后使用以下GATHER AUTO选项收集表统计信息：

SQL> SELECT COUNT(*) FROM sales2 WHERE prod_id = 42;

  COUNT(*)
----------
     12116

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES2',OPTIONS=>'GATHER AUTO');

现在对数据字典的查询表明，数据库prod_id根据先前查询期间收集的信息在列上创建了直方图：

SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM 
  2  FROM   USER_TAB_COL_STATISTICS 
  3  WHERE  TABLE_NAME = 'SALES2';

COLUMN_NAME   NOTES          HISTOGRAM
------------- -------------- ---------
AMOUNT_SOLD   STATS_ON_LOAD  NONE
QUANTITY_SOLD STATS_ON_LOAD  NONE
PROMO_ID      STATS_ON_LOAD  NONE
CHANNEL_ID    STATS_ON_LOAD  NONE
TIME_ID       STATS_ON_LOAD  NONE
CUST_ID       STATS_ON_LOAD  NONE
PROD_ID       HISTOGRAM_ONLY FREQUENCY

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 何时创建直方图

评论