There are times when Oracle puts restrictions to save us from our own bad practices. For instance, committing in triggers is forbidden so we do not make a mess, among other things.

And then there is “ORA-20008: Number of extensions in table ….. already reaches the upper limit (20)”. We can create thousands of tables, each of them with thousands of columns, but when we create column groups/extended stats, we face severe limitations – 20 or 10% of the number of columns. Why?!? It takes little disk space to accommodate those statistics. The incremental impact on the time to gather stats would be quite small as well.

In short, this restriction has no technologies or business sense, it is probably trivial to correct, but it is still there (as of 11.2.0.3.0).

Some would think that this restriction would almost never affect a “real” system. I disagree! A fact table in a data-warehouse environment could be queried in many different ways – and that’s the beauty of the Kimball approach. I have personally seen this situation in my systems a couple of times so far.

Then what can we do about it?

If the extended statics were generated automatically using the methods here

, then we can weed out the extended statistics that do not help the CBO a lot, and put in place only those extended stats that make a significant difference.

Since CBO can use column groups with more columns than are present in predicates (http://www.hotsos.com/sym12/sym_speakers_matuszyk.html) ,we can eliminate extended stats groups that are covered by another extended stats group.

Another way is to measure the dependent columns error (*err)/*correlation strength* (*https://iiotzov.wordpress.com/2011/07/05/getting-the-best-of-oracle-extended-statistics*) *and eliminate those column groups with smallest dependent columns error.

Here is an example:

`create table test_tab as`

`with v1 as (select rownum n from dual connect by level <= 10000) `

`select`

`rownum id , mod (rownum , 100) corr_gr1_col1, mod (rownum , 25) corr_gr1_col2 , mod (rownum ,`

`50) corr_gr1_col3 ,`

`200 + mod (rownum , 11) corr_gr2_col4 , 200 + mod (rownum , 22) corr_gr2_col5 , 200 + mod (`

`rownum , 33) corr_gr2_col6`

`from`

`v1, v1`

`where`

`rownum <= 1000000`

` ;`

`Table created.`

` select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2)') from dual `

`--------------------------------------------------------------------------------`

`SYS_STU_4QATWX9PNVNKREP$YZIDOX`

… 19 more CREATE_EXTENDED_STATS statements

`select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2,corr_gr2_col4,corr_gr2_col5,corr_gr2_col6)') from dual ;`

`select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2,corr_gr2_col`

`*`

`ERROR at line 1:`

`ORA-20008: Number of extensions in table SCOTT.TEST_TAB already reaches the`

`upper limit (20`

`ORA-06512: at "SYS.DBMS_STATS", line 8415`

`ORA-06512: at "SYS.DBMS_STATS", line 32587`

We can now score each column group and decide whnich ones will remain and which ones will be dropped.

`select (count(distinct(corr_gr1_col1) ) * count(distinct(corr_gr1_col3)))/count(distinct(sys_op`

`_combined_hash(corr_gr1_col1,corr_gr1_col3))) from TEST_TAB ;`

`(COUNT(DISTINCT(CORR_GR1_COL1))*COUNT(DISTINCT(CORR_GR1_COL3)))/COUNT(DISTINCT(S`

`--------------------------------------------------------------------------------`

`50`

… a few column group queries

` select (count(distinct(corr_gr1_col1) ) * count(distinct(corr_gr2_col4)))/count(distinct(sys_op`

_combined_hash(corr_gr1_col1,corr_gr2_col4))) from TEST_TAB ;

`(COUNT(DISTINCT(CORR_GR1_COL1))*COUNT(DISTINCT(CORR_GR2_COL4)))/COUNT(DISTINCT(S`

`--------------------------------------------------------------------------------`

`1`

The column group (corr_gr1_col1,corr_gr2_col4) does not add any value to the CBO, so we can drop it and create (corr_gr1_col1,corr_gr1_col2,corr_gr2_col4,corr_gr2_col5,corr_gr2_col6) instead.