I've been starting to work with Extended statistics to help the optimizer find the best plan. This is a great feature that is outlined by @sqlmaria (Maria Coogan) here.
But once you create extended statistics, how do you know what is there ? I wrote this query to find out what function based indexes, what extended statistics, and what their definition are.
Here is my script.
and this is what the output looks like..
Notice the Function colunmn. This is a "YES" or "NO" depending on if this is a function based index, or just extended statistics.
This should help tell where your extended statistics are in your database.
But once you create extended statistics, how do you know what is there ? I wrote this query to find out what function based indexes, what extended statistics, and what their definition are.
Here is my script.
column table_owner alias "owner" format a15
column table_name alias "Table Name" format a30
column function_index alias "F Index" format a8
column Index_name alias "Index Name" format a30
column data_default alias "Definition" format a50
set pagesize 1000
select table_owner,
table_name,
nvl2(index_name,'YES','NO') function_index,
index_name,
data_default
from
(
select owner table_owner,table_name,
(select distinct index_name from dba_ind_columns b where a.column_name=b.column_name and a.owner=b.index_owner and a.table_name=b.table_name) index_name
,data_default
-- , DBMS_LOB.SUBSTR( to_lob(data_default),100,1)
from dba_tab_cols a
where virtual_column='YES' and hidden_column='YES' and (owner not in ('SYS','WMSYS','XDB','SYSMAN','MDSYS','EXFSYS','PR_MDS') and owner not like 'APEX_%')
)
order by table_owner,table_name;
and this is what the output looks like..
TABLE_OWNER TABLE_NAME FUNCTION INDEX_NAME DATA_DEFINITION
--------------- ------------------------------ -------- ------------------------------ --------------------------------------------------
BGRENN TAB_SCHR_PERD NO COALESCE("COL1","COL2")
BGRENN TAB2 YES IDX_TAB2 "COL1"||' '||"COL2"
BGRENN TAB3 NO COALESCE("COL1","COL2")
BGRENN TAB4 YES IDX_TAB4 COALESCE("COL1","COL2",0)
BGRENN TAB4 YES IDX_TAB4 COALESCE("COL3",0)
BGRENN TAB5 YES IDX_TAB5 COALESCE("COL1","COL2",0)
BGRENN TAB6 YES IDX_TAB6 NVL("COL1",(-1))
BGRENN TAB6 YES IDX_TAB6 NVL("COL2",(-1))
BGRENN TAB6 YES IDX_TAB6 NVL("COL3",(-1))
BGRENN TAB6 YES IDX_TAB6 NVL("COL4",'x')
BGRENN TAB7 YES IDX_COMPOSITE "COL1"
BGRENN TAB7 YES IDX_COMPOSITE "COL3"
Notice the Function colunmn. This is a "YES" or "NO" depending on if this is a function based index, or just extended statistics.
This should help tell where your extended statistics are in your database.