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 12.1.0.1 (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';

COLUMN_NAME
--------------------------------------------------------------------------------
OWNER
NAME
TYPE
LINE
TEXT
ORIGIN_CON_ID
SYS_STU90I0TV5BWQ1A16YH4SC_BL2

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 (more likely you’ll get an ORA-00904 error message – unless you are very unlucky with your typo) and end up with two column groups, with statistics only on the one you didn’t realise you’d created.

Footnote

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

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

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,003 other followers