Oracle DB のファンクション索引を作成すると、拡張統計(式統計)のエントリが自動で作成される。(※Oracle Database 11gR2 で検証)
超マニアックなネタ彡(ﾟ)(ﾟ)
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
Qiitaにも書いたやで彡(^)(^)
Oracle DB のファンクション索引を作成すると、拡張統計(式統計)のエントリが自動で作成される。(※Oracle Database 11gR2 で検証)
http://qiita.com/ora_gonsuke777/items/11f95ea41372270c8864
