sys.col_usage$を基にヒストグラム取得対象カラムが決まります
Oracle任せで列統計情報の取得を行った場合、sys.col_usage$（ディクショナリビュー）の値を元に取得対象カラムが決まります。sys.col_usage$にはかつて実行されたSQLのWHERE句に指定された条件が記録されているため、この情報を判断材料としています。この内容を確認するために専用のファンクションDBMS_STATS.REPORT_COL_USAGE()が用意されていますので今回はこれを使ってみました。
DBMS_STATS.REPORT_COL_USAGE()の利用
以下はSOONI.SYAINテーブルと、SOONI.TAB_INFテーブルに対してかつてどんな問い合わせがあったのか確認します。
set pages 1000
set loboffset 1
set long 80000
set longchunksize 2000
select DBMS_STATS.REPORT_COL_USAGE ('SOONI','SYAIN') from dual
;
利用例
SQL> set loboffset 1
SQL> set long 8000
SQL> set longchunksize 2000
SQL> select DBMS_STATS.REPORT_COL_USAGE ('SOONI','SYAIN') from dual
2 ;
DBMS_STATS.REPORT_COL_USAGE('SOONI','SYAIN')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
DBMS_STATS.REPORT_COL_USAGE('SOONI','SYAIN')
--------------------------------------------------------------------------------
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SOONI.SYAIN
...................................
1. ID : EQ
2. ROMAJI : EQ
###############################################################################
DBMS_STATS.REPORT_COL_USAGE('SOONI','SYAIN')
--------------------------------------------------------------------------------
SQL> select DBMS_STATS.REPORT_COL_USAGE ('SOONI','TAB_INF') from dual;
DBMS_STATS.REPORT_COL_USAGE('SOONI','TAB_INF')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
DBMS_STATS.REPORT_COL_USAGE('SOONI','TAB_INF')
--------------------------------------------------------------------------------
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SOONI.TAB_INF
.....................................
1. COLUMN_POSITION : EQ_JOIN
2. INDEX_NAME : EQ_JOIN
3. TABLE_NAME : EQ LIKE EQ_JOIN
DBMS_STATS.REPORT_COL_USAGE('SOONI','TAB_INF')
--------------------------------------------------------------------------------
4. TABLE_OWNER : EQ EQ_JOIN
###############################################################################
SQL>※sys.col_usage$への反映にはタイムラグがあります。直ぐにSQLで使った条件の反映をしたい場合、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO() を実行すると良いです。
専用ファンクションを使わず直接sys.col_usage$を参照してみた結果が以下です
sys.col_usage$を直接表示
select
s.obj#
,u.name schema
,o.name tablename
,c.name colname
,s.intcol#
,s.equality_preds
,s.equijoin_preds
,s.nonequijoin_preds
,s.range_preds
,s.like_preds
,s.null_preds
from sys.col_usage$ s
inner join sys.obj$ o on s.obj# = o.obj#
inner join sys.user$ u on o.owner# = u.user#
inner join sys.col$ c on s.obj# = c.obj#
and s.intcol# = c.intcol#
where u.name='SOONI'
and (o.NAME in ('SYAIN','TAB_INF'))
order by u.NAME,o.NAME,c.NAME
;
表示結果
| | | | | EQUALITY| EQUIJOIN|NONEQUIJOIN|RANGE| |
OBJ#|SCHEMA |TABLENAME |COLNAME |INTCOL#| PREDS| PREDS| PREDS|PREDS|LIKE_PREDS|NULL_PREDS
---------|----------|----------|----------------|-------|----------|----------|-----------|-----|----------|----------
100305|SOONI |SYAIN |ID | 1| 2| 0| 0| 0| 0| 0
100305|SOONI |SYAIN |ROMAJI | 3| 2| 0| 0| 0| 0| 0
100721|SOONI |TAB_INF |COLUMN_POSITION | 6| 0| 5| 0| 0| 0| 0
100721|SOONI |TAB_INF |INDEX_NAME | 2| 0| 3| 0| 0| 0| 0
100721|SOONI |TAB_INF |TABLE_NAME | 4| 4| 5| 0| 0| 1| 0
100721|SOONI |TAB_INF |TABLE_OWNER | 3| 5| 5| 0| 0| 0| 0
SQL>SQL*Plus表示用
set lin 200 col obj# for '99999999' col schema for a8 col tablename for a10 col colname for a16 col INTCOL# FORMAT 990 col RANGE_PREDS FORMAT 990 set colsep '|' COLUMN EQUALITY_PREDS HEADING "EQUALITY|PREDS" COLUMN EQUIJOIN_PREDS HEADING "EQUIJOIN|PREDS" COLUMN NONEQUIJOIN_PREDS HEADING "NONEQUIJOIN|PREDS" COLUMN RANGE_PREDS HEADING "RANGE|PREDS"
