extended statistics and non-existent combined values

  • From: Ls Cheng <exriscer@xxxxxxxxx>
  • To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 31 Jul 2018 18:03:26 +0200

Hi

I have a problematic query which is not doing index scan.

I tried to fix the issue using extended statistics but without success. I
have following test case, the idea is the estimated cardinality combining
column C2 and C3 should return as few rows as possible because the
predicate C2 = N and C3 = N does not return any rows but with extended
statistics it is actually estimating 915 rows. Anyone can think of an
workaround?

C2 has only two distinct values, Y and N
C3 has only two distinct values, Y and N

C2 and C3 with both values N returns no rows.

Thanks




create table t20
(
 c1 number,
 c2 varchar2(5),
 c3 varchar2(5)
);

insert into t20
select rownum,
       case when object_type = 'TABLE' then 'N' else 'Y' end c2,
       case when object_type = 'TABLE' then 'Y' else 'N' end c3
  from dba_objects;

create index t20_i1 on t20(c2, c3);

select c2, c3, count(*) from t20 group by c2, c3;

C2    C3      COUNT(*)
----- ----- ----------
N     Y           1994
Y     N          71482

exec dbms_stats.gather_table_stats('LSC', 'T20', method_opt => 'FOR ALL
COLUMNS SIZE 1, FOR COLUMNS C2 SIZE 2, FOR COLUMNS C3 SIZE 2')

*-- **c2 = 'N' and c3 = 'N' returns cero rows*
select * from t20 where c2 = 'N' and c3 = 'N';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 287249393

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2121 | 19089 |    11   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T20    |  2121 | 19089 |    11   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T20_I1 |  2121 |       |     5   (0)|
00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("C2"='N' AND "C3"='N')

-- create extended statistics for C2 and C3
select
dbms_stats.create_extended_stats('LSC', 'T20','(C2, C3)')
from dual;

exec dbms_stats.gather_table_stats('LSC', 'T20', method_opt => 'FOR ALL
COLUMNS SIZE 1, FOR COLUMNS C2 SIZE 2, FOR COLUMNS C3 SIZE 2 FOR COLUMNS
(C2, C3) SIZE 2')


*-- *
*c2 = 'N' and c3 = 'N' returns cero rows but even with extended statistics
it estimates 915 rows*
select * from t20 where c2 = 'N' and c3 = 'N';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 287249393

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   915 |  8235 |     5   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T20    |   915 |  8235 |     5   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T20_I1 |   915 |       |     2   (0)|
00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("C2"='N' AND "C3"='N')

Other related posts: