Oracle Scratchpad

September 25, 2013

Extended Stats

Filed under: CBO,Oracle,Statistics — Jonathan Lewis @ 5:06 pm BST Sep 25,2013

Here’s a little demo cut-n-pasted from a session running Oracle (it works on 11g, too). All it does is create a table by copying from a well-known table, gather extended stats on a column group, then show you the resulting column names by querying view user_tab_cols.

SQL> create table t1 as select * from all_source where rownum <= 1000; 

Table created.
SQL> execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns (name, type) size 1')

PL/SQL procedure successfully completed.

SQL> select column_name from user_tab_cols where table_name = 'T1';


7 rows selected.

The last column is the name of the virtual column created for the column group.

Look carefully at the output – I didn’t make a call to create_extended_stats, the column group was created implicitly with the gather_table_stats call.

I discovered this oddity by accident while first checking over the slides that Maria Colgan had sent me for our joint presentation at OOW13. Don’t ask how, but I failed to notice the big red letters in the title saying “create_extended_stats”, and thought her slide was gathering the stats without first creating them.

First thought – “Surely that won’t work”
Second thought – “It’s the optimizer lady, better check”
Third thought – “Gosh, it works”
Fourth thought (some time later) – I just did a test because I didn’t read the slide properly, but I learned something new.

There is a serious point to this: if you create a column group then make a typing error collecting stats for it, you could end up with two column groups (though it’s more likely you’ll get an “ORA-00904: {column name} : invalid identifier” error message unless you are very unlucky with your typo) with statistics only on the one you didn’t realise you’d created.


If you do manage to create an extended stats definition / virtual column by accident, the command to get rid of it is: dbms_stats.drop_extended_stats():

        dbms_stats.drop_extended_stats (
                ownname   => user, 
                tabname   => 't1', 
                extension => '(name, type)'

1 Comment »

  1. […] not enough values”. There is a workaround: rather than creating a virtual column create extended stats to represent the expression. The internal, virtual column representing the expression will then be […]

    Pingback by Virtual Stats | Oracle Scratchpad — October 4, 2013 @ 9:36 am BST Oct 4,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 5,478 other followers