RE: extended statistics and non-existent combined values

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 31 Jul 2018 15:00:45 -0400
Message-ID: <0c2401d42900$ca3ef750$5ebce5f0$_at_rsiz.com>



one additional thought: For the extended expression I think you need 4 buckets. Yes, two of them should be empty, but there are four cases and you need the empty buckets, right?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Timur Akhmadeev Sent: Tuesday, July 31, 2018 1:29 PM
To: exriscer_at_gmail.com
Cc: Oracle Mailinglist
Subject: Re: extended statistics and non-existent combined values  

You need a fake histogram that would have N,N in there with cardinality 1.  

On Tue, 31 Jul 2018 at 19:04, Ls Cheng <exriscer_at_gmail.com> wrote:

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')

-- 

Regards
Timur Akhmadeev



--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 31 2018 - 21:00:45 CEST

Original text of this message