Cardinality estimate for hybrid histogram skips padding char fields
Date: Tue, 19 Apr 2016 12:27:44 +0800
Message-ID: <CABx0cSU8HTkqPfuPku+GOENLi4gjPZT3PK18=QgxHnzjBBGusA_at_mail.gmail.com>
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 |
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 19 2016 - 06:27:44 CEST