Patrick,
I think it's not just hybrid histograms. I think it's a generic problem that
could show up in any type of histogram on a column group. (It's also possible
that it could appear with any type of virtual column based on a user-defined
function with CHAR inputs - but I haven't tested that idea.)
I think the issue is that Oracle doesn't consider the possible need for
blank-padding semantics when passing parameter to the sys_op_combined_hash()
function that it uses to generate the virtual column and the histogram entries
- and that could lead to all sorts of odd effects, even with (badly defined)
joins.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Patrick Jolliffe [jolliffe@xxxxxxxxx]
Sent: 19 April 2016 05:27
To: oracle-l
Subject: Cardinality estimate for hybrid histogram skips padding char fields
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 |
----------------------------------------------------------------------------------------