32-byte limit-Oracle histogram Optimization

The restrictions on Oracle histograms often lead to headaches for many beginners-not displaying more than 32 bytes, and generating the same errors as the first 32 bytes.

Since the number of DISTINCT values in the OBJECT_TYPE column is less than 254, ORACLE will create a frequency histogram on this column, and the optimizer will accurately estimate that the query of CONTENTS = 'table' will return 37 records.

Let's take a look at the following situation:

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)
----------
10000

SQL> 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 1

22 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 ')

Since the first 32 bits of the CONTENTS column are the same, ORACLE only takes the first 32 bits into account when collecting the statistical information histogram.
The CONTENTS column of all records is the same, so the optimizer estimates that the number of rows returned is 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 1

SQL> 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 1150

12 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 ')

From the above, we can see that ORACLE only considers the first 32 bytes. The estimated number of rows queried by each value below is equal to the sum of the actual number of records of three.

* ******************************* TABLE 37
* ***************************** Table partition 25
* ****************************** Table subpartition 1714
* ****************************** TYPE 538

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12