After my Masterclass on indexes at the UKOUG Tech2016 conference this morning I got into a conversation about creating * extended stats* on a table. I had pointed out in the masterclass that each time you dropped an index you really ought to

*(specifically a*

**be prepared to create a set of extended stats***) on the list of columns that had defined the index just in case the optimizer had been using the*

**column group***statistic from the index to help it calculate cardinalities.*

**distinct_keys**Unfortunately there is a limit on the number of column groups (or any other type of extended stats) you can have on a table and that limit is the larger of 20 and *ceiling(number of columns / 10)* – so you typically run into a problem if you want to take defensive action after dropping more than twenty (multi-column) indexes from a single table. (And you wonder how Oracle’s adaptive dynamic stats process that * silently creates column groups overnight* handles the problem of needing far more column groups than are allowed.)

The conversation led on to the oddity that the count includes the virtual columns representing the column groups so, for example, if you have 253 columns in your table you can create 26 column groups; but if you add 26 column groups that means you have a total of 279 columns, so you can actually create a total of 28 groups (an extra 2); but if you create those two column groups you now have a total of 281 columns in the table which means you’re allowed a total of 29 column groups so you can add one more column group for a total of 282 columns. Here’s some code (which I’ve run only on 11.2.0.4) to play with. To keep things very simple I’ve generated some trivial extended stats rather than column groups:

rem rem Script: extended_stats_limit2.sql rem Author: Jonathan Lewis rem Dated: Dec 2016 rem drop table t1 purge; begin for i in 2..253 loop execute immediate 'alter table t1 add (c' || to_char(i,'FM000') || ' number)'; end loop; end; / desc t1 prompt ============================================================================================ prompt This will raise an error on the 30th addition prompt ORA-20008: Number of extensions in table TEST_USER.T1 already reaches the upper limit (28.2) prompt ============================================================================================ declare ext_string varchar2(20); begin for i in 1..30 loop ext_string := '(c001 + ' || i || ')'; dbms_output.put_line( dbms_stats.create_extended_stats( ownname => user, tabname => 'T1', extension => ext_string ) ); end loop; end; / column column_name format a32 select column_name, hidden_column, virtual_column, segment_column_id, internal_column_id from user_tab_cols where table_name = 'T1' order by internal_column_id ;

This code results in a table with 253 segment columns (keeping below the critical 255), and 29 hidden, virtual columns (with names like * SYS_STU0#$2X$X1M4NFZVM2O_5A3FC*) representing the extended stats. What if I want more extended stats ? There is no limit on virtual columns in general, beyond the inherent table limit of 1,000 columns in total, so what if I create a few virtual columns (another 39, say, taking my total column count to 321): would this allow me to increase the number of extended stats to 33 – and if so, what would happen if I then dropped the virtual columns:

prompt ============================================ prompt Now we add some virtual columns after which prompt we will be able to add more extended stats prompt and drop the virtual columns prompt ============================================ begin for i in 1..39 loop execute immediate 'alter table t1 add (virt' || to_char(i,'fm000') || ' generated always as ( c002 + ' || i || ') virtual)' ; end loop; end; / select column_name, hidden_column, virtual_column, segment_column_id, internal_column_id from user_tab_cols where table_name = 'T1' order by internal_column_id ; prompt ============================================================================================ prompt We can now get up to 33 extended stats prompt This will raise an error on the attempt to add the 34th set prompt ORA-20008: Number of extensions in table TEST_USER.T1 already reaches the upper limit (32.5) prompt ============================================================================================ declare ext_string varchar2(20); begin for i in 30..34 loop ext_string := '(c001 + ' || i || ')'; dbms_output.put_line( dbms_stats.create_extended_stats( ownname => user, tabname => 'T1', extension => ext_string ) ); end loop; end; / select column_name, hidden_column, virtual_column, segment_column_id, internal_column_id from user_tab_cols where table_name = 'T1' order by internal_column_id ; select column_name, internal_column_id from user_tab_cols where table_name = 'T1' and hidden_column = 'YES' and virtual_column = 'YES' order by internal_column_id ; prompt ============================ prompt Now drop the virtual columns prompt ============================ begin for r in ( select column_name from user_tab_cols where column_name like 'VIRT%' ) loop execute immediate 'alter table t1 drop column ' || r.column_name; end loop; end; / select column_name, internal_column_id from user_tab_cols where table_name = 'T1' and virtual_column = 'YES' order by internal_column_id ;

When I ran this code I ended up with a table consisting of 286 columns, of which 253 were my original columns and 33 – with internal column ids of 254 to 286 inclusive – were the extended stats. It seems there is a way to bypass the limit if you really want to – though I’m not sure I’d really want to do it on a production system.

### Left as Exercise for the Reader:

Create a table with 5 real columns and the 26 column groups needed to represent all (multi-column) combinations of those five columns. (Remember that the order of columns in a column group is not really significant). (The 26 groups consist of: 1 x 5 column, 5 x 4 column, 10 x 3 column, 10 x 2 column – this may remind some of you of binomial expansions, others may remember it as a row from Pascal’s triangle, you could also view it as a particular subset of the binary representations of the integers from 1 to 31.)

### Update (May 2021)

A * recent note on the MOS Community Forum* (needs a login) highlighted a threat if you use this trick to get excess extended stats. If you export and re-import the table without stats, and then try to use the

*and*

**dbms_stats.export_xxx_stats()***procedures to add the stats – including the extended stats – to the table you’re probably going to see Oracle raise the exception:*

**dbms_stats.import_xxx_stats()**

*“ORA-20008: Number of extensions in table XXX already reaches the upper limit (NN)”*
Hi Jonathan,

That’s a clever way to get around the 20/10% restriction for the number of extended stats.

When I was facing a similar issue, I tried to figure out which extended stats did not provide significant help to CBO and drop them, saving the valuable spots for extended stats that could really help.

Doing so is not that difficult – all we need to do is see if the columns in a group are correlated. If they are then the extended stats on then might provide benefits. Here are the details:

https://iiotzov.wordpress.com/2012/10/28/ora-20008-number-of-extensions-what-to-do-about-this-unnecessary-error/

Regards,

Iordan Iotzov

Comment by iiotzov — December 7, 2016 @ 8:32 pm GMT Dec 7,2016 |

Iordan,

I managed to miss this comment when you made it – so I’ve just tweeted the link.

Comment by Jonathan Lewis — March 27, 2017 @ 10:55 am BST Mar 27,2017 |

[…] Extended Stats (Dec 2016): A dirty workaround to the limited number of column groups allowed. […]

Pingback by Column Group Catalog | Oracle Scratchpad — September 27, 2018 @ 5:16 pm BST Sep 27,2018 |

[…] to use idx1 instead; and it’s probably a good idea to create (or be prepared to create) column group statistics on the (c1, c2) combination so that the optimizer doesn’t lose the information about the […]

Pingback by Indexing partitions | Oracle Scratchpad — November 11, 2020 @ 11:35 am GMT Nov 11,2020 |

[…] count/10), and the way the arithmetic is applied is a little odd so there are ways to hack around the […]

Pingback by Optimizer Tip | Oracle Scratchpad — September 20, 2021 @ 9:04 am BST Sep 20,2021 |