RE: Cardinality estimate for hybrid histogram skips padding char fields
Date: Wed, 20 Apr 2016 06:29:02 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282CEB0B_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Patrick Jolliffe [jolliffe_at_gmail.com] 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 ||* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 1447 | 14470 | 43 (38)| 00:00:01 |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 43 (38)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 10 | | |
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 ||* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 175K| 1716K| 43 (38)| 00:00:01 |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 43 (38)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 10 | | |
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 20 2016 - 08:29:02 CEST