Oracle DB のファンクション索引を作成すると、拡張統計(式統計)のエントリが自動で作成される。(※Oracle Database 11gR2 で検証) #oracle

超マニアックなネタ彡(ﾟ)(ﾟ)

Oracle Database のファンクション索引を作成して、作成したファンクション索引を使用する
SQLが実行されると、拡張統計(式統計)のエントリが作成されて COL_USAGE$ に格納されます。

まずは準備／確認から。この時点では拡張統計のエントリはありません。

-- COL_USAGEの確認
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
：
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
###############################################################################

ファンクション索引を作成しますが、この時点でも拡張統計のエントリはありません。

-- Function索引の作成
CREATE INDEX TBL_B_I1 ON TBL_B(TO_CHAR(P_DATE, 'YYYYMMDD'));

Index created.

-- COL_USAGEの確認 ※この時点では拡張統計のエントリは作成されていない。
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
：
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
###############################################################################

ファンクション索引を使用するSQLを実行してみます。

-- SQL実行
SET AUTOTRACE TRACEONLY;
SELECT /*+ MONITOR */
       A.*
  FROM TEST_TABLE_A A
     , TBL_B B
 WHERE A.P_NO2  = B.P_NO
   AND A.P_CHAR = B.P_CHAR
   AND TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801';
SET AUTOTRACE OFF;

1102 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4015380392

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    81 |  2187 |     7   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |    81 |  2187 |     7   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | TEST_TABLE_A |    26 |   416 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| TBL_B        |   300 |  3300 |     4   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TBL_B_I1     |   120 |       |     3   (0)| 00:00:01 | ★ファンクション索引
---------------------------------------------------------------------------------------------

ここでCOL_USAGEを確認すると……拡張統計のエントリがあるやね彡(^)(^)

-- COL_USAGEの確認
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
：
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
3. SYS_NC00004$                        : EQ ★拡張統計のエントリ
###############################################################################

エントリの中身をDBA_STAT_EXTENSIONSで確認すると、ファンクション索引の拡張統計(式統計)なのが解るやで彡(ﾟ)(ﾟ)

-- 拡張統計のエントリの確認
SET PAGESIZE 100;
COLUMN EXTENSION FORMAT A30;
SELECT OWNER, TABLE_NAME, EXTENSION_NAME, EXTENSION FROM DBA_STAT_EXTENSIONS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_B';

OWNER      TABLE_NAME   EXTENSION_NAME  EXTENSION
---------- ------------ --------------- ------------------------------
AYSHIBAT   TBL_B        SYS_NC00004$    (TO_CHAR("P_DATE",'YYYYMMDD'))

バージョンは11gR2(11.2.0.4.x)で検証したやで彡(ﾟ)(ﾟ)

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

11gR1 や 11gR2 の拡張統計エントリは、通常は拡張統計を明示的に作成したり、
SEED_COL_USAGE で明示的に収集しない限りは作成されんのですが、
この記事で紹介したような例外ケースも有るということで彡(ﾟ)(ﾟ)

12cR1の動きを知りたい方は、去年の DDD 2016資料を見てクレメンス彡(-)(-)

DD2-5 オラクル・コンサルが語る！ SQL性能を最大限に引き出す
DB 12cクエリー・オプティマイザ新機能活用と統計情報運用の戦略
https://www.slideshare.net/shibataayumu/ddd-2016-db-12c