EDIT:2018-03-16 Seems WordPress or SQLPlus messed up my testcase, I have updated tihs post to include unedited testcase as well as output.
EDIT:2018-03-22 Oracle Support have created a new bug for this issue, Bug 27730925 – WRONG CARDINALITY ESTIMATION FROM HYBRID HISTOGRAM ON NCHAR COLUMN
Paraphrasing Jonathan Lewis it’s very easy to start ‘breaking’ Oracle as soon as you start combining a few features that most people are not running. The following demonstrates that if you are using NCHAR datatypes (in our case this is forced on us because due to our JDEdwards application) and if you have a hybrid histogram then cardinality estimates vary greatly depending on whether or not you pad your predicates.
Of course with NCHAR datatypes, the results and hence cardinality should be unchanged whether or not predicates are padded. I have been unable to reproduce the symptoms with CHAR datatypes.
DROP TABLE HH_NC_TAB;
CREATE TABLE HH_NC_TAB (COL NvarCHAR2(10));
insert /*+APPEND*/ into HH_NC_TAB
SELECT 'AAA'||mod(rownum, 10) FROM DUAL CONNECT BY LEVEL < 100000
union all
SELECT 'AAA'||rownum FROM DUAL CONNECT BY LEVEL < 100000;
commit;
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'HH_NC_TAB', METHOD_OPT=>'FOR COLUMNS COL SIZE 254');
EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = 'AAA1';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = RPAD('AAA1', 10);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL> DROP TABLE HH_NC_TAB;
Table HH_NC_TAB dropped.
SQL> CREATE TABLE HH_NC_TAB (COL NCHAR(10));
Table HH_NC_TAB created.
SQL> insert /*+APPEND*/ into HH_NC_TAB
SELECT 'AAA'||mod(rownum, 10) FROM DUAL CONNECT BY LEVEL < 100000
union all
SELECT 'AAA'||rownum FROM DUAL CONNECT BY LEVEL < 100000; Commit complete. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'HH_NC_TAB', METHOD_OPT=>'FOR COLUMNS COL SIZE 254');
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = 'AAA1';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2981390417
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 13 (24)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| HH_NC_TAB | 1 | 21 | 13 (24)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("COL"=U'AAA1')
filter("COL"=U'AAA1')
14 rows selected.
SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM HH_NC_TAB WHERE COL = RPAD('AAA1', 10);
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2981390417
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9689 | 198K| 12 (17)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| HH_NC_TAB | 9689 | 198K| 12 (17)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("COL"=U'AAA1 ')
filter("COL"=U'AAA1 ')
14 rows selected.
SQL>
Issue reproduces on 12.1, 12.2 and I’ve uploaded to LiveSQL so you can verify for yourself on 18.1
The fact that these two conferences run back-to-back seemed a perfect opportunity to experience both, at the expense of a single long-haul flight. Their taking place at amazing locations on the Istrian coast which I had never visited before sealed the deal. I submitted abstracts and was delighted when they both accepted me to speak.
The Slovenian conference was held on the town of Portoroz, and I had heard about some cave systems, Postojna and Skocjan on the way. I had planned to visit both, but on the way to the Postojna, I saw a sign to a cave at Planina so I decided to check that out. The public accessible area only extends a couple of hundred meters inside (much more is accessible with a guide) but it was really cool just being alone in the quiet and pitch darkness.
Postojna Cave is a complete contrast; you take a 20 minute train journey with hundreds of other sightseers deep into an amazing cave system, followed by a 40 minute guided tour, before taking the train back out again. After these two experiences I’d had more then enough of caves, so I decided too skip Skocjan and head direct to the conference venue.
The speakers dinner was held at an fantastic restaurant, Na Burji, and the meal was accompanied by bread baked by the multi-talented Jože Senegačnik. I was thrilled to finally catch up in person with Jure Bratina, the first subscriber to my blog (!).
