Database Tuning (MOSC)

MOSC Banner

Question regarding automatic histogram creation

edited Jan 2, 2019 3:27PM in Database Tuning (MOSC) 6 commentsAnswered

I am working in a 12.1 Oracle database. I am trying to understand how the automatic histogram creation mechanism works.

I create a table with < 254 NDVs and I make sure it's uniform data distribution, i.e. not skewed. I then run select * from table where col=b, to populate the SYS.COL_USAGE$ data dictionary table and then I collect stats using the DBMS_STATS and all default options.

After that I see in the DBA_TAB_COL_STATISTICS view that a FREQUENCY histogram is automatically created, but there is no skew. I tried multiple data distribution combinations and always FREQUENCY. So I decided to insert more than 254 NDVs into one of this table's columns which are skewed and Oracle then automatically creates HYBRID histogram type, ok that's fine. Then I make that table data not skewed and I get NONE for histogram. So I get what I expect when NDV are more than 254 in the table, i.e. I get a histogram automatically created when the data is skewed and

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center