Update (2018-03-16) Seems WordPress or SQLPlus messed up my testcase, I have updated post to include unedited testcase as well as output.
Update (2018-03-22) Oracle Support have created a new bug for this issue, Bug 27730925 – WRONG CARDINALITY ESTIMATION FROM HYBRID HISTOGRAM ON NCHAR COLUMN
Update (2018-07-24) Tested the patch for bug 27730925 resolves issue. Note the following is required for fix to take effect: alter session set “_fix_control”=’27730925:1′;
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. The issue does not reproduce the symptoms with CHAR datatypes.
SQL> CREATE TABLE HH_NC_TAB (COL NCHAR(10));
Table created.
SQL> insert /*+APPEND*/ into HH_NC_TAB
2 SELECT 'AAA'||mod(rownum, 10) FROM DUAL CONNECT BY LEVEL < 100000
3 union all
4 SELECT 'AAA'||rownum FROM DUAL CONNECT BY LEVEL < 100000
5 ;
199998 rows created.
SQL> COMMIT;
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> 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 | 206 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HH_NC_TAB | 1 | 21 | 206 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("COL"=U'AAA1')
13 rows selected.
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 | | 10236 | 209K| 204 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HH_NC_TAB | 10236 | 209K| 204 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("COL"=U'AAA1 ')
13 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 (!).
