What is the default for a METHOD_OPT value using SIZE REPEAT?

Past experience has always led me to believe that a METHOD_OPT value of “FOR ALL COLUMNS SIZE REPEAT” would default to “SIZE 1″ on columns without statistics. I have never explicitly tested this default however so this is the quick test I did to put my mind at rest.

First we create a test table that, being new, has never had any queries on it. Therefore “SIZE AUTO” should not create any histograms.

DROP TABLE tab1;
CREATE TABLE tab1
(	col1 NUMBER
,	col2 VARCHAR2(1));
INSERT INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 100;
UPDATE tab1 SET col2 = 'N' WHERE ROWNUM = 1;
COMMIT;

exec dbms_stats.gather_table_stats(USER,'tab1',method_opt=>'for all columns size auto');

SELECT column_name, num_buckets FROM user_tab_columns WHERE table_name = 'TAB1';

COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
COL1                                     1
COL2                                     1

Ok, as expected there are no histograms. So now we repeat the test but query the columns before gathering statistics. This time "SIZE AUTO" should create histograms, at least on the skewed column "COL2".

DROP TABLE tab1;
CREATE TABLE tab1
(	col1 NUMBER
,	col2 VARCHAR2(1));
INSERT INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 100;
UPDATE tab1 SET col2 = 'N' WHERE ROWNUM = 1;
COMMIT;

SELECT COUNT(*) FROM tab1 WHERE col1 = 1 and col2 = 'N';

exec dbms_stats.gather_table_stats(USER,'tab1',method_opt=>'for all columns size auto');

SELECT column_name, num_buckets FROM user_tab_columns WHERE table_name = 'TAB1';

COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
COL1                                     1
COL2                                     2

NUM_BUCKETS for COL2 is "2" so we did get the expected histogram. Now we can repeat the test but this time gather statistics with "SIZE REPEAT". If "SIZE REPEAT" defaults to "SIZE 1" when no prior histograms are present then we should only see values of 1 for NUM_BUCKETS. If it defaults to "SIZE AUTO" then we should see NUM_BUCKETS > 0 for at least COL2. Let's see:

DROP TABLE tab1;
CREATE TABLE tab1
(	col1 NUMBER
,	col2 VARCHAR2(1));
INSERT INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 100;
UPDATE tab1 SET col2 = 'N' WHERE ROWNUM = 1;
COMMIT;

SELECT COUNT(*) FROM tab1 WHERE col1 = 1 and col2 = 'N';

exec dbms_stats.gather_table_stats(USER,'tab1',method_opt=>'for all columns size repeat');

SELECT column_name, num_buckets FROM user_tab_columns WHERE table_name = 'TAB1';

COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
COL1                                     1
COL2                                     1

So I believe the default value for "SIZE REPEAT" is "SIZE 1", please let me know if you see a hole in my thinking.

About these ads

One thought on “What is the default for a METHOD_OPT value using SIZE REPEAT?

  1. Hi,

    Well, “Size Repeat” is the option which do not create any new histograms on skewed columns

    Thanks,
    Anil

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