Oracle 12c新特性之如何检测有用的多列统计信息详解

什么是多列统计信息

在Oracle数据库中，统计信息是优化器进行SQL执行计划选择的基础，而多列统计信息则是在多个列上的统计数据，可以帮助优化器更准确地选择最优的执行计划。Oracle 12c中新增了一些功能来方便检测和使用多列统计信息。

如何检测有用的多列统计信息

1. 使用DBMS_STATS.REPORT_MULTICOLUMN_COLUMN_STATS

这个过程可以用来报告一个或多个列的统计信息。在Oracle 12c中，该过程增加了一些新的参数来获取多列统计信息的相关信息。

下面是一个示例，报告表MY_TABLE上3个列的统计数据：

SET SERVEROUTPUT ON
DECLARE
    v_report clob;
BEGIN
    DBMS_STATS.REPORT_MULTICOLUMN_COLUMN_STATS(
        ownname=>'MY_SCHEMA',
        tabname=>'MY_TABLE',
        colnames=>SYS.ODCIVARCHAR2LIST('COLUMN1','COLUMN2','COLUMN3'),
        report=>v_report);
    dbms_output.put_line(v_report);
END;
/

这段代码将在控制台输出一个包含多列统计信息的报告。报告中包含各列的基数、不同值、直方图数据等。

2. 使用DBMS_STATS.SET_MULTICOLUMN_STATS

该过程可以用来设置多列统计信息。在Oracle 12c中，该过程增加了一些新的参数来设置多列统计信息的相关信息。

下面是一个示例，设置表MY_TABLE上3个列的多列统计信息：

BEGIN
    DBMS_STATS.SET_MULTICOLUMN_STATS(
        ownname=>'MY_SCHEMA',
        tabname=>'MY_TABLE',
        colnames=>SYS.ODCIVARCHAR2LIST('COLUMN1','COLUMN2','COLUMN3'),
        method_opt=>'FOR COLUMNS (COLUMN1, COLUMN2 SIZE AUTO, COLUMN3 SIZE 10)',
        no_invalidate=>FALSE);
END;
/

这段代码将设置MY_TABLE上3个列的多列统计信息，并指定了计算直方图数据的相关选项。

示例说明

示例1

假定我们有一个包含以下列的表：

• ID:数字类型
• NAME:字符串类型
• AGE:数字类型
• GENDER:字符串类型

现在我们希望在AGE和GENDER两列上创建多列统计信息，以优化查询“查询所有年龄为30岁的女性”。我们可以使用以下代码进行设置：

BEGIN
    DBMS_STATS.SET_MULTICOLUMN_STATS(
        ownname=>'MY_SCHEMA',
        tabname=>'MY_TABLE',
        colnames=>SYS.ODCIVARCHAR2LIST('AGE','GENDER'),
        method_opt=>'FOR COLUMNS (AGE,GENDER SIZE AUTO)',
        no_invalidate=>FALSE);
END;
/

这会在AGE和GENDER两列上创建多列统计信息，并启用直方图数据。

示例2

假定我们有一个包含以下列的表：

• ID:数字类型
• NAME:字符串类型
• IMAGE:二进制类型

现在我们希望在NAME和IMAGE两列上创建多列统计信息，以优化查询包含IMAGE列的子句。我们可以使用以下代码进行设置：

BEGIN
    DBMS_STATS.SET_MULTICOLUMN_STATS(
        ownname=>'MY_SCHEMA',
        tabname=>'MY_TABLE',
        colnames=>SYS.ODCIVARCHAR2LIST('NAME','IMAGE'),
        method_opt=>'FOR COLUMNS (NAME SIZE AUTO, IMAGE SIZE 1000)',
        no_invalidate=>FALSE);
END;
/

这将在NAME和IMAGE两列上创建多列统计信息，并指定了IMAGE列的大小为1000字节。这有助于优化查询包含IMAGE列的子句的执行计划选择。