32个字节限制——Oracle直方图优化

Oracle直方图的限制往往让不少初学者头痛——超过32字节不显示，以及前32字节相同产生错误。由于OBJECT_TYPE列上的DISTINCT值的个数小于254,ORACLE将会在此列上建立频率直方图，优化器将会准确的估算出CONTENTS="TABLE"的查询返回37条记录。下面看看如下的情况：SQL> TRUNCATE TABLE T;Table truncated.SQL> SET AUTOT OFF
SQL> INSERT INTO T
2 SELECT ROWNUM,OBJECT_NAME,RPAD（"*",32,"*"）||OBJECT_TYPE
3 FROM ALL_OBJECTS WHERE ROWNUM<=10000;10000 rows created.SQL> COMMIT;Commit complete.SQL> SELECT COUNT（1） FROM T; COUNT（1）
----------
     10000SQL> SELECT CONTENTS ,COUNT（1） FROM T GROUP BY CONTENTS ;CONTENTS                                             COUNT（1）
-------------------------------------------------- ----------
********************************SEQUENCE                    1
********************************LIBRARY                     3
********************************WINDOW GROUP                1
********************************INDEX PARTITION           347
********************************PACKAGE                   164
********************************SCHEDULE                    1
********************************TABLE PARTITION            25
********************************VIEW                     1150
********************************TABLE                      37
********************************PROCEDURE                  11
********************************CONSUMER GROUP              2
********************************INDEX SUBPARTITION       3328
********************************OPERATOR                   15
********************************WINDOW                      2
********************************INDEX                      34
********************************FUNCTION                   60
********************************SYNONYM                  2552
********************************TABLE SUBPARTITION       1714
********************************TYPE                      538
********************************JOB CLASS                   1
********************************PACKAGE BODY               13
********************************EVALUATION CONTEXT          122 rows selected.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS（USER,"T",method_opt=>"FOR COLUMNS CONTENTS SIZE 254"）;PL/SQL procedure successfully completed.SQL> SELECT * FROM T WHERE CONTENTS="********************************TABLE";Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost （%CPU）| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 10000 |   654K|    24   （0）| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 10000 |   654K|    24   （0）| 00:00:01 |
--------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------   1 - filter（"CONTENTS"="********************************TABLE"）
由于CONTENTS列的前32位都一样，ORACLE在收集统计信息直方图的时候只考虑前32位，这会导致ORACLE认为
所有的记录的列CONTENTS都是相同的，因此优化器估算返回的行数为10000。 SQL> UPDATE T SET CONTENTS=SUBSTR（CONTENTS,2）;10000 rows updated.SQL> COMMIT;Commit complete.SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS（USER,"T",method_opt=>"FOR COLUMNS CONTENTS SIZE 254"）;PL/SQL procedure successfully completed.SQL> SELECT CONTENTS ,COUNT（1） FROM T GROUP BY CONTENTS ORDER BY 1;CONTENTS                                             COUNT（1）
-------------------------------------------------- ----------
*******************************CONSUMER GROUP               2
*******************************EVALUATION CONTEXT           1
*******************************FUNCTION                    60
*******************************INDEX                       34
*******************************INDEX PARTITION            347
*******************************INDEX SUBPARTITION        3328
*******************************JOB CLASS                    1
*******************************LIBRARY                      3
*******************************OPERATOR                    15
*******************************PACKAGE                    164
*******************************PACKAGE BODY                13
*******************************PROCEDURE                   11
*******************************SCHEDULE                     1
*******************************SEQUENCE                     1
*******************************SYNONYM                   2552
*******************************TABLE                       37
*******************************TABLE PARTITION             25
*******************************TABLE SUBPARTITION        1714
*******************************TYPE                       538
*******************************VIEW                      1150
*******************************WINDOW                       2
*******************************WINDOW GROUP                 1SQL> SELECT SUBSTR（CONTENTS,1,32）,COUNT（1） FROM T GROUP BY SUBSTR（CONTENTS,1,32）;SUBSTR（CONTENTS,1,32）                      COUNT（1）
---------------------------------------- ----------
*******************************E                  1
*******************************J                  1
*******************************P                188
*******************************C                  2
*******************************S               2554
*******************************T               2314
*******************************F                 60
*******************************O                 15
*******************************L                  3
*******************************W                  3
*******************************I               3709
*******************************V               115012 rows selected.
SQL> SET AUTOT TRACEONLY EXP
SQL> SELECT * FROM T WHERE CONTENTS="*******************************TABLE";Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost （%CPU）| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 2314 |   149K|    24   （0）| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 2314 |   149K|    24   （0）| 00:00:01 |
--------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------   1 - filter（"CONTENTS"="*******************************TABLE"）SQL> SELECT * FROM T WHERE CONTENTS="*******************************TYPE";Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost （%CPU）| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 2314 |   149K|    24   （0）| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 2314 |   149K|    24   （0）| 00:00:01 |
--------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------   1 - filter（"CONTENTS"="*******************************TYPE"）
从上面可以看出,ORACLE只考虑前32个字节，下面每一个值查询的估算行数都是等于3者实际记录数之和。*******************************TABLE                       37
*******************************TABLE PARTITION             25
*******************************TABLE SUBPARTITION        1714
*******************************TYPE                       538更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 行列转换函数Oracle 默认和非默认监听相关资讯 Oracle数据库基础教程

在Oracle数据库中插入含有&符号的（03/06/2013 09:20:14）
Oracle 执行计划更改导致数据加工（02/13/2013 14:45:04）
判断Oracle Sequence是否存在（02/13/2013 14:32:26）

Oracle数据库中无法对数据表进行（02/26/2013 14:24:58）
Oracle 在同一台主机上建立用户管（02/13/2013 14:40:58）
Oracle em 无法启动,报not found错（02/13/2013 14:29:48）

本文评论查看全部评论（0）