[80122]column statistics and histogram .txt,20180122.txt

[80122]column statistics and histogram .txt,20180122.txt

[80122]column statistics and histogram .txt

-- // I saw token yesterday:

1. Environment:
SCOTT @ book> @ ver1
PORT_STRING VERSION BANNER
----------------------------------------------------------------------------------------------------------------------------
X86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

Create table t1 as select object_type o1, object_type o2, object_type o3, object_id, object_name from all_objects where rownum <= 50000;

SCOTT @ book> select column_name, num_distinct, histogram, num_buckets, last_analyzed from user_tab_cols where table_name = 't1' order by column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
-----------------------------------------------------------------------------
O1 NONE
O2 NONE
O3 NONE
OBJECT_ID NONE
OBJECT_NAME NONE

-- // No analysis after creation.

2. test:

Exec dbms_stats.gather_table_stats (user, 't1', method_opt => 'for all columns size 1 for columns o1 o2 o3 size 15 ');

SCOTT @ book> select column_name, num_distinct, histogram, num_buckets, last_analyzed, histogram from user_tab_cols where table_name = 't1' order by column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED HISTOGRAM
--------------------------------------------------------------------------------------------
O1 33 NONE 1 08:52:44 NONE
O2 33 NONE 1 08:52:44 NONE
O3 33 height balanced 15 2018-01-22 08:52:44 HEIGHT BALANCED
OBJECT_ID 50000 NONE 1 08:52:44 NONE
OBJECT_NAME 28576 NONE 1 2011-1-22 08:52:44 NONE
-- // Note that the O1 and O2 fields do not have a histogram.

SCOTT @ book> exec dbms_stats.gather_table_stats (user, 't1', method_opt => 'for all columns size 1 for columns size 15 o1 o2 o3 ');
PL/SQL procedure successfully completed.

SCOTT @ book> select column_name, num_distinct, histogram, num_buckets, last_analyzed, histogram from user_tab_cols where table_name = 't1' order by column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED HISTOGRAM
--------------------------------------------------------------------------------------------
O1 33 height balanced 15 2018-01-22 08:57:37 HEIGHT BALANCED
O2 33 height balanced 15 2018-01-22 08:57:37 HEIGHT BALANCED
O3 33 height balanced 15 2018-01-22 08:57:37 HEIGHT BALANCED
OBJECT_ID 50000 NONE 1 08:57:37 NONE
OBJECT_NAME 28576 NONE 1 2011-1-22 08:57:37 NONE
-- // Create a histogram for the O1, O2, O3 fields.

-- // My personal favorite method is:
SCOTT @ book> exec dbms_stats.gather_table_stats (user, 't1', method_opt => 'for all columns size 1 for columns o1 size 15 o2 size 16 o3 size 17 ');
PL/SQL procedure successfully completed.

SCOTT @ book> select column_name, num_distinct, histogram, num_buckets, last_analyzed, histogram from user_tab_cols where table_name = 't1' order by column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED HISTOGRAM
--------------------------------------------------------------------------------------------
O1 33 height balanced 15 2018-01-22 08:58:46 HEIGHT BALANCED
O2 33 height balanced 16 2018-01-22 08:58:46 HEIGHT BALANCED
O3 33 height balanced 17 2018-01-22 08:58:46 HEIGHT BALANCED
OBJECT_ID 50000 NONE 1 08:58:46 NONE
OBJECT_NAME 28576 NONE 1 2011-1-22 08:58:46 NONE
-- // In this way, the O1, O2, and O3 fields are used to create a histogram. The author advocates the following statement: "for columns {size clause} {column list}". In this way, I am not used to this. ^_^.

-- // The author also provides some examples for testing:
For columns o1 size 12, o2 size 13, o3 size 14
For columns o1 size 15 o2 size 16 o3 size 17
For columns size 18 o1 size 19 o2 size 20 o3
For columns size 21 o1 o2 size 22 o3
For columns o1 size 12, o2 size 12, o3 size 13, object_id size 13 object_name size 14
For columns size 22 o1 o2 for columns size 23 o3 object_id for columns size 24 object_name

-- // In short, note that 'for all columns size 1 for columns size 15 o1 o2 o3 'may not meet the requirements. Please pay attention to it during work.