Hi
I think my problem is raleted to out of range values. I will look into
store low selectivity and faking histograms to solve this.
I wonder why even with column statistics Oracle cannot determine that the
rows that satisfies non-existent values (or out of range) is simply 0?
Thanks
On Tue, Jul 31, 2018 at 6:36 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
wrote:
That's Oracle doing what it does.
Your example 1: If you have a histogram on any of the individual columns
of a column group but NOT on the column group Oracle uses the individual
selectivities and multiplies (standard mechanism).
Your example 2: If you have a frequency histogram and request a value that
does appear in the histogram Oracle uses half the selectivity of the least
popular item in the histogram. In your case 1994/2 = 915 (approximately).
I'm guessing your on 11g since the histogram figures appear to be sampled
rather than 100%.
You may find that if you use dbms_stats.get_column_stats();
dbms_stats.set_column_stats() you can set the stored selectivity of the
column group to something very small so that the estimated cardinality is
1. (Depending on version this may not have any effect, or it may only work
if you also create a stat table then export and re-import the column stats).
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Ls Cheng <exriscer@xxxxxxxxx>
Sent: 31 July 2018 17:03:26
To: Oracle Mailinglist
Subject: extended statistics and non-existent combined values
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')
