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. (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 column 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 have 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, 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 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.)

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 |