[20170114]12c varchar2型別直方圖.txt
[20170114]12c varchar2型別直方圖.txt
--我曾經提到慎用nvarchar2資料型別,連結:http://blog.itpub.net/267265/viewspace-2120925/
--我那裡提到資料型別nvarchar2型別,因為1個字元佔用2個位元組,這樣如果前面16個字元重複很多,直方圖的建立就是雞肋,
--毫無用處(因為分析僅僅對前面32個位元組有效),12c 直方圖支援更多型別: 高度直方圖,頻率直方圖.混和型別(HYBRID).
--看看12c關於直方圖方面的變化,透過例子說明:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (a varchar2(100));
insert into t select '12345678901234567890123456789012'||level||'3456789123456789123456' a from dual connect by level < 5000;
commit ;
2.建立直方圖:
SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049'); END;
*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 2049
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 1
--2049 太大了,11g最大支援255.
SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2048');
PL/SQL procedure successfully completed.
--//12C可以支援2048.
SELECT COLUMN_NAME
,NUM_DISTINCT
,DENSITY
,NUM_BUCKETS
,HISTOGRAM
,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
,UTL_RAW.cast_to_varchar2 (low_value) L_A
,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'T';
Record View
As of: 2017/1/14 21:25:12
COLUMN_NAME: A
NUM_DISTINCT: 4999
DENSITY: 0.0002
NUM_BUCKETS: 2048
HISTOGRAM: HYBRID
L_LENGTH: 58
L_A: 1234567890123456789012345678901210003456789123456789123456
H_LENGTH: 57
H_A: 123456789012345678901234567890129993456789123456789123456
--//你可以發現儲存的長度發生了變數,而且bucket的數量增加到了2048.直方圖型別HYBRID.
--//也可以查詢:select * from DBA_TAB_HISTOGRAMS where owner=user and table_name='T' order by endpoint_number;
--//結果太長,大家可以自行檢測.
3.再來看看查詢的sql語句:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select * from t where a='12345678901234567890123456789012'||1001||'3456789123456789123456';
A
----------------------------------------------------------
1234567890123456789012345678901210013456789123456789123456
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c4asjnvdvyqhn, child number 0
-------------------------------------
select * from t where a='12345678901234567890123456789012'||1001||'34567
89123456789123456'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 15 (100)| | 1 |00:00:00.01 | 54 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 59 | 15 (0)| 00:00:01 | 1 |00:00:00.01 | 54 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='1234567890123456789012345678901210013456789123456789123456')
--//可以發現E-Rows與A-Rows一樣.
4.也就是oracle 12c現在能很好的處理長字串直方圖問題,但是究竟支援多長,我繼續測試:
create table t1 (a varchar2(1000));
insert into t1 select lpad('x',996,'x')||level a from dual connect by level < 5000;
commit ;
exec DBMS_STATS.GATHER_TABLE_STATS(null,'T1',method_opt => 'FOR ALL COLUMNS SIZE 2048');
SELECT COLUMN_NAME
,NUM_DISTINCT
,DENSITY
,NUM_BUCKETS
,HISTOGRAM
,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
,UTL_RAW.cast_to_varchar2 (low_value) L_A
,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'T1';
Record View
As of: 2017/1/14 21:41:33
COLUMN_NAME: A
NUM_DISTINCT: 4999
DENSITY: 0.0002
NUM_BUCKETS: 1
HISTOGRAM: HYBRID
L_LENGTH: 64
L_A: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
H_LENGTH: 64
H_A: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//也就是比原來增加了1倍,現在支援64位元組.但是看看現在的情況NUM_BUCKETS=1,也就是僅僅1個桶.
SCOTT@test01p> select * from t1 where a=lpad('x',996,'x')||1001;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2cua094wf06kf, child number 0
-------------------------------------
select * from t1 where a=lpad('x',996,'x')||1001
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 204 (100)| | 1 |00:00:00.01 | 753 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4999 | 4886K| 204 (0)| 00:00:01 | 1 |00:00:00.01 | 753 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1001')
32 rows selected.
--//現在E-Rows,A-Rows相差很遠了.這樣的直方圖也無用.
--我曾經提到慎用nvarchar2資料型別,連結:http://blog.itpub.net/267265/viewspace-2120925/
--我那裡提到資料型別nvarchar2型別,因為1個字元佔用2個位元組,這樣如果前面16個字元重複很多,直方圖的建立就是雞肋,
--毫無用處(因為分析僅僅對前面32個位元組有效),12c 直方圖支援更多型別: 高度直方圖,頻率直方圖.混和型別(HYBRID).
--看看12c關於直方圖方面的變化,透過例子說明:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (a varchar2(100));
insert into t select '12345678901234567890123456789012'||level||'3456789123456789123456' a from dual connect by level < 5000;
commit ;
2.建立直方圖:
SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049'); END;
*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 2049
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 1
--2049 太大了,11g最大支援255.
SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2048');
PL/SQL procedure successfully completed.
--//12C可以支援2048.
SELECT COLUMN_NAME
,NUM_DISTINCT
,DENSITY
,NUM_BUCKETS
,HISTOGRAM
,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
,UTL_RAW.cast_to_varchar2 (low_value) L_A
,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'T';
Record View
As of: 2017/1/14 21:25:12
COLUMN_NAME: A
NUM_DISTINCT: 4999
DENSITY: 0.0002
NUM_BUCKETS: 2048
HISTOGRAM: HYBRID
L_LENGTH: 58
L_A: 1234567890123456789012345678901210003456789123456789123456
H_LENGTH: 57
H_A: 123456789012345678901234567890129993456789123456789123456
--//你可以發現儲存的長度發生了變數,而且bucket的數量增加到了2048.直方圖型別HYBRID.
--//也可以查詢:select * from DBA_TAB_HISTOGRAMS where owner=user and table_name='T' order by endpoint_number;
--//結果太長,大家可以自行檢測.
3.再來看看查詢的sql語句:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select * from t where a='12345678901234567890123456789012'||1001||'3456789123456789123456';
A
----------------------------------------------------------
1234567890123456789012345678901210013456789123456789123456
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c4asjnvdvyqhn, child number 0
-------------------------------------
select * from t where a='12345678901234567890123456789012'||1001||'34567
89123456789123456'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 15 (100)| | 1 |00:00:00.01 | 54 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 59 | 15 (0)| 00:00:01 | 1 |00:00:00.01 | 54 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='1234567890123456789012345678901210013456789123456789123456')
--//可以發現E-Rows與A-Rows一樣.
4.也就是oracle 12c現在能很好的處理長字串直方圖問題,但是究竟支援多長,我繼續測試:
create table t1 (a varchar2(1000));
insert into t1 select lpad('x',996,'x')||level a from dual connect by level < 5000;
commit ;
exec DBMS_STATS.GATHER_TABLE_STATS(null,'T1',method_opt => 'FOR ALL COLUMNS SIZE 2048');
SELECT COLUMN_NAME
,NUM_DISTINCT
,DENSITY
,NUM_BUCKETS
,HISTOGRAM
,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
,UTL_RAW.cast_to_varchar2 (low_value) L_A
,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'T1';
Record View
As of: 2017/1/14 21:41:33
COLUMN_NAME: A
NUM_DISTINCT: 4999
DENSITY: 0.0002
NUM_BUCKETS: 1
HISTOGRAM: HYBRID
L_LENGTH: 64
L_A: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
H_LENGTH: 64
H_A: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//也就是比原來增加了1倍,現在支援64位元組.但是看看現在的情況NUM_BUCKETS=1,也就是僅僅1個桶.
SCOTT@test01p> select * from t1 where a=lpad('x',996,'x')||1001;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2cua094wf06kf, child number 0
-------------------------------------
select * from t1 where a=lpad('x',996,'x')||1001
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 204 (100)| | 1 |00:00:00.01 | 753 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4999 | 4886K| 204 (0)| 00:00:01 | 1 |00:00:00.01 | 753 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1001')
32 rows selected.
--//現在E-Rows,A-Rows相差很遠了.這樣的直方圖也無用.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2132462/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190630]如何確定直方圖型別.txt直方圖型別
- [20170615]直方圖-高度直方圖(11g).txt直方圖
- [20161208]11g直方圖與char資料型別.txt直方圖資料型別
- 直方圖直方圖
- 收集直方圖及檢視直方圖資訊直方圖
- [20120905]刪除直方圖.txt直方圖
- 12c 新特性之varchar2,nvarchar2型別大小測試型別
- 直方圖(histograms)直方圖Histogram
- 7.3 直方圖直方圖
- oracle 直方圖Oracle直方圖
- [20140301]直方圖與優化.txt直方圖優化
- 直方圖均衡化直方圖
- 04:垂直直方圖直方圖
- oracle直方圖使用Oracle直方圖
- 直方圖學習直方圖
- Oracle直方圖解析Oracle直方圖圖解
- [zt] Histograms - 直方圖Histogram直方圖
- dba_histograms等高直方圖和等頻直方圖的理解Histogram直方圖
- long型別轉換成varchar2型別
- 直方圖中最大矩形直方圖
- Oracle直方圖詳解Oracle直方圖
- 【效能優化】直方圖優化直方圖
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- [20180122]列統計與直方圖.txt直方圖
- 關於直方圖中關於字元型別的ENDPOINT_VALUE欄位的轉換直方圖字元型別
- Oracle的資料型別：char/varchar2Oracle資料型別
- [Python影象處理] 十一.灰度直方圖概念及OpenCV繪製直方圖Python直方圖OpenCV
- OpenCV計算機視覺學習（9）——影像直方圖 & 直方圖均衡化OpenCV計算機視覺直方圖
- halcon-直方圖均衡直方圖
- python如何畫直方圖Python直方圖
- elasticsearch 之 histogram 直方圖聚合ElasticsearchHistogram直方圖
- oracle直方圖筆記-轉Oracle直方圖筆記
- 使用直方圖注意事項直方圖
- （轉）Oracle直方圖詳解Oracle直方圖
- Oracle直方圖詳解(ZT)Oracle直方圖
- CBO計算與資料型別的選擇（兼談日期、字元、數字三種型別的直方圖生成原理和使用）資料型別字元直方圖
- Oracle直方圖 (柱狀圖 histograms) 詳解Oracle直方圖Histogram
- Oracle CHAR，VARCHAR，VARCHAR2,nvarchar型別的區別與使用Oracle型別