Cardinality estimate for hybrid histogram skips padding char fields

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 19 Apr 2016 12:27:44 +0800

Thought I would share the test case in case it helps anyone.  Have raised
SR.


DROP TABLE TEST_CARD;
CREATE TABLE TEST_CARD (COL1 CHAR(4), COL2 CHAR(2));
INSERT INTO TEST_CARD SELECT 'XX', 'XX' FROM DUAL CONNECT BY LEVEL <=
171000;
INSERT INTO TEST_CARD SELECT MOD(ROWNUM, 16), MOD(ROWNUM, 43) FROM DUAL
CONNECT BY LEVEL <= 1000000;
COMMIT;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(NULL, 'TEST_CARD', '(COL1,COL2)')
FROM DUAL;
SYS_STUFLHATC5RBD6JHJZWT$X2AAH
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'TEST_CARD', METHOD_OPT=>'FOR
COLUMNS SIZE 255 SYS_STUFLHATC5RBD6JHJZWT$X2AAH');
SELECT HISTOGRAM FROM user_tab_col_statistics WHERE COLUMN_NAME =
'SYS_STUFLHATC5RBD6JHJZWT$X2AAH' and table_name = 'TEST_CARD'
HYBRID

EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_CARD WHERE COL1 = 'XX' AND COL2=
'XX';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost
(%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |    10 |    43
(38)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |    10 |
|          |
|*  2 |   TABLE ACCESS STORAGE FULL| TEST_CARD |  1447 | 14470 |    43
(38)| 00:00:01 |
----------------------------------------------------------------------------------------
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_CARD WHERE COL1 = 'XX  ' AND
COL2= 'XX';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost
(%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |    10 |    43
(38)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |    10 |
|          |
|*  2 |   TABLE ACCESS STORAGE FULL| TEST_CARD |   175K|  1716K|    43
(38)| 00:00:01 |
----------------------------------------------------------------------------------------

Other related posts: