DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, July 15, 2017

Automated Gather Stats on Index Creation.

Whenever index is created , Oracle automatically gathers optimizer statistics for that index. The database piggybacks the statistics gather on the full data scan and sort operation necessary for the index creation. This approach has worked so well since it was introduced in earlier verison,Some people already know about it but I am sharing becuase this for the new dba's.

Demo:

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') WALL_CLOCK_TIME
FROM dual;

WALL_CLOCK_TIME
-----------------------------
15-JUL-2017 14:29:44

SELECT index_name,
       TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS')
       last_analyzed,
       leaf_blocks,
       distinct_keys,
       clustering_factor
FROM   user_indexes;

no ROWS selected

CREATE INDEX t_s_idx ON items (s);

INDEX created.

SELECT index_name,
       TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS')
       last_analyzed,
       leaf_blocks,
       distinct_keys,
       clustering_factor
FROM   user_indexes;

INDEX_NAME LAST_ANALYZED LEAF_BLOCKS DISTINCT_KEYS  CLUS_FACT
---------- -------------------- ----------- ------------- ----------
T_S_IDX    15-JUL-2017 14:29:45    6135      1000000        999839

No comments: