RE: extended statistics and non-existent combined values
Date: Tue, 31 Jul 2018 15:00:45 -0400
Message-ID: <0c2401d42900$ca3ef750$5ebce5f0$_at_rsiz.com>
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-lReceived on Tue Jul 31 2018 - 21:00:45 CEST