A case of wrong cardinality estimation by CBO

A developer handed over us a query, asking us to run in the report database. There is a predicate in the query for the table 'om', which looks like:

AND om.order_id = SUBSTR (om.order_id,1, 13)

The problem is that CBO can not correctly estimate the cardinality from such a predicate. (It showed cardinality equals 1, CBO choose this table as the build table in the hash join )

Below is a simple test to demonstrate this point:

1. Create and analyze a test table

DB10G> create table t(a varchar2(10));

Table created.

DB10G>
DB10G>
DB10G> begin
  2  for i in 1..1000
  3  loop
  4    insert into t values(dbms_random.string('x', mod(i,10) + 1 ));
  5
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

DB10G>
DB10G>
DB10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

2. Case 1 - Predicate: a=substr(a, 1, 5)

In this case, we can see CBO estimates cardinality to be 1, however, the actual number of row is 500

DB10G> select count(* ) from t where a=substr(a, 1, 5);

  COUNT(*)
----------
       500


Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1      6      3   (0) 00:00:01
   1   SORT AGGREGATE               1      6                
*  2    TABLE ACCESS FULL T         1      6      3   (0) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=SUBSTRA",1,5))

3. Case 2 - length(a) <=5 I have suggested use length function, but it still can not result in good estimation of the cadinality in the problem query. In my test, the carnality and the actual are different by one order of magnitude.

DB10G> select count(*) from t where length(a) <=5 ; 
COUNT(*)
----------     
500
Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362
---------------------------------------------------------------------------
Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
 0  SELECT STATEMENT              1      6      3   (0) 00:00:01 
 1   SORT AGGREGATE               1      6                  
*2    TABLE ACCESS FULL T        50    300      3   (0) 00:00:01
---------------------------------------------------------------------------  Predicate Information (identified by operation id):
--------------------------------------------------- 
 2 - filter(LENGTH("A")<=5)

Finally, I suggested to use /*+ ordered */ hint to force Oracle choose the table 'om' as the probe table in the hash join, instead of as a build table by default. The table 'om' is relatively big with approximately 37M rows. I also suggested to add parallel hint. The query was able to be finished in about 3.5 hours.