extended statistics and non-existent combined values

[Ls Cheng]

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

[Mark W. Farnham]

The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and c3 = 'N';

and then generate your query with a cardinality hint.



You might want to hint the use of the index for the count(*) query.



Of course you already know this is the sort of thing that the CBO is supposed to get right routinely and I believe you’ve already done everything correctly to give it the best possible chance. Sigh.



Now I do take it from your one is Y, one is N values that this is actually an either or in your database. IF I’m correct about that and C2=Y literally implies that C3=N, they you might want to code it up that way, leaving C3 out of the database entirely and out of queries as a predicate. If someone wants it instantiated for them in a query, I supposed you could make C3 a virtual column. I’m presuming a *lot* for that to be true, and the CBO should be getting this right as you’ve done it. (C2 could actually be N for N and null for Y if you really want to get the index as small as possible.)



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Ls Cheng
Sent: Tuesday, July 31, 2018 12:03 PM
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')

[Mladen Gogala]

A slightly lighter sledgehammer would be using /*+
DYNAMIC_SAMPLING(table,11) */.

Post by Mark W. Farnham
The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and c3 = 'N';
and then generate your query with a cardinality hint.
You might want to hint the use of the index for the count(*) query.
Of course you already know this is the sort of thing that the CBO is
supposed to get right routinely and I believe you’ve already done
everything correctly to give it the best possible chance. Sigh.
Now I do take it from your one is Y, one is N values that this is
actually an either or in your database. IF I’m correct about that and
C2=Y literally implies that C3=N, they you might want to code it up
that way, leaving C3 out of the database entirely and out of queries
as a predicate. If someone wants it instantiated for them in a query,
I supposed you could make C3 a virtual column. I’m presuming a **lot**
for that to be true, and the CBO should be getting this right as
you’ve done it. (C2 could actually be N for N and null for Y if you
really want to get the index as small as possible.)
mwf
*Sent:* Tuesday, July 31, 2018 12:03 PM
*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 |
--------------------------------------------------------------------------------------
---------------------------------------------------
   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 |
--------------------------------------------------------------------------------------
---------------------------------------------------
   2 - access("C2"='N' AND "C3"='N')

[Dominic Brooks]

Wouldn’t the optimiser then discard dynamic sampling results because there was no matching data in the sample?

Sent from my iPhone

On 1 Aug 2018, at 21:14, Mladen Gogala <***@gmail.com<mailto:***@gmail.com>> wrote:


A slightly lighter sledgehammer would be using /*+ DYNAMIC_SAMPLING(table,11) */.

On 7/31/2018 12:39 PM, Mark W. Farnham wrote:
The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and c3 = 'N';
and then generate your query with a cardinality hint.

You might want to hint the use of the index for the count(*) query.

Of course you already know this is the sort of thing that the CBO is supposed to get right routinely and I believe you’ve already done everything correctly to give it the best possible chance. Sigh.

Now I do take it from your one is Y, one is N values that this is actually an either or in your database. IF I’m correct about that and C2=Y literally implies that C3=N, they you might want to code it up that way, leaving C3 out of the database entirely and out of queries as a predicate. If someone wants it instantiated for them in a query, I supposed you could make C3 a virtual column. I’m presuming a *lot* for that to be true, and the CBO should be getting this right as you’ve done it. (C2 could actually be N for N and null for Y if you really want to get the index as small as possible.)

mwf

From: oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org> [mailto:oracle-l-***@freelists.org] On Behalf Of Ls Cheng
Sent: Tuesday, July 31, 2018 12:03 PM
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')

[Stefan Koehler]

Hey Dominic,
no, it works but (new) ADS code has some other possible side effect - especially with 12.2 and SPD / directive cache.

-------------------8<-------------------------
qksdsExeStmt(): qksdsExeStmt(): enter
qksdsExeStmt(): do compute: sampSize = 100
qksdsExeStmt(): ************************************************************
DS Query Text:
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
qksdsExeStmt():

qksdsExeStmt(): newSoftTimeLimit is 1
qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 0
=====================
PARSING IN CURSOR #139742383085112 len=280 dep=1 uid=106 oct=3 lid=106 tim=356438955 hv=2939816853 ad='85f43500' sqlid='117mkrurmn2wp'
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
END OF STMT
PARSE #139742383085112:c=1000,e=1227,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1856612004,tim=356438954
EXEC #139742383085112:c=0,e=832,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1856612004,tim=356439871
FETCH #139742383085112:c=0,e=1801,p=5,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=1856612004,tim=356441704
CLOSE #139742383085112:c=0,e=9,dep=1,type=0,tim=356441772
**************************************************************
Iteration 1
Exec count: 1
CR gets: 2
CU gets: 0
Disk Reads: 5
Disk Writes: 0
IO Read Requests: 2
IO Write Requests: 0
Bytes Read: 40960
Bytes Written: 0
Bytes Exchanged with Storage: 40960
Bytes Exchanged with Disk: 40960
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 4155 (us)
CPU Time: 1000 (us)
User I/O Time: 1456 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 100.000000
Post S. Size: 100.000000
qksdsExeStmt(): qksdsExeStmt: exit
qksdsExecute(): Dumping unscaled result
qksdsDumpResult(): DS Results: #exps=1, smp obj=T20
qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=0.0, low=0.0, hig=0.0)qksdsDumpResult():
qksdsDumpResult(): end dumping results
qksdsScaleResult(): Dumping scaled result (status = SUCCESS)
qksdsDumpResult(): DS Results: #exps=1, smp obj=T20
qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=0.0, low=0.0, hig=0.0)qksdsDumpResult():
qksdsDumpResult(): end dumping results
...
...
qksdsDumpStats(): **************************************************************
DS Service Statistics
qksdsDumpStats(): Executions: 1
Retries: 0
Timeouts: 0
ParseFails: 0
ExecFails: 0
qksdsDumpStats():
qksdsExecute(): qksdsExecute(): exit

Single Tab Card adjusted from 1307.000000 to 1.000000 due to adaptive dynamic sampling

Rounded: 1 Computed: 1.000000 Non Adjusted: 1307.000000
...
...
-------------------8<-------------------------

Of course ADS code is run for the index as well but works the same way there - so omitted this snippet.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Wouldn’t the optimiser then discard dynamic sampling results because there was no matching data in the sample? 

A slightly lighter sledgehammer would be using /*+ DYNAMIC_SAMPLING(table,11) */.

--
http://www.freelists.org/webpage/oracle-l

[Dominic Brooks]

Useful details thanks!

Sent from my iPhone

Post by Stefan Koehler
Hey Dominic,
no, it works but (new) ADS code has some other possible side effect - especially with 12.2 and SPD / directive cache.
-------------------8<-------------------------
qksdsExeStmt(): qksdsExeStmt(): enter
qksdsExeStmt(): do compute: sampSize = 100
qksdsExeStmt(): ************************************************************
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
qksdsExeStmt(): newSoftTimeLimit is 1
qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 0
=====================
PARSING IN CURSOR #139742383085112 len=280 dep=1 uid=106 oct=3 lid=106 tim=356438955 hv=2939816853 ad='85f43500' sqlid='117mkrurmn2wp'
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
END OF STMT
PARSE #139742383085112:c=1000,e=1227,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1856612004,tim=356438954
EXEC #139742383085112:c=0,e=832,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1856612004,tim=356439871
FETCH #139742383085112:c=0,e=1801,p=5,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=1856612004,tim=356441704
CLOSE #139742383085112:c=0,e=9,dep=1,type=0,tim=356441772
**************************************************************
Iteration 1
Exec count: 1
CR gets: 2
CU gets: 0
Disk Reads: 5
Disk Writes: 0
IO Read Requests: 2
IO Write Requests: 0
Bytes Read: 40960
Bytes Written: 0
Bytes Exchanged with Storage: 40960
Bytes Exchanged with Disk: 40960
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 4155 (us)
CPU Time: 1000 (us)
User I/O Time: 1456 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 100.000000
Post S. Size: 100.000000
qksdsExeStmt(): qksdsExeStmt: exit
qksdsExecute(): Dumping unscaled result
qksdsDumpResult(): DS Results: #exps=1, smp obj=T20
qksdsDumpResult(): end dumping results
qksdsScaleResult(): Dumping scaled result (status = SUCCESS)
qksdsDumpResult(): DS Results: #exps=1, smp obj=T20
qksdsDumpResult(): end dumping results
...
...
qksdsDumpStats(): **************************************************************
DS Service Statistics
qksdsDumpStats(): Executions: 1
Retries: 0
Timeouts: 0
ParseFails: 0
ExecFails: 0
qksdsExecute(): qksdsExecute(): exit

Single Tab Card adjusted from 1307.000000 to 1.000000 due to adaptive dynamic sampling

Rounded: 1 Computed: 1.000000 Non Adjusted: 1307.000000
...
...
-------------------8<-------------------------
Of course ADS code is run for the index as well but works the same way there - so omitted this snippet.
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.soocs.de&amp;data=02%7C01%7C%7C32423b98747743d993ea08d5f85dee78%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636688004672573057&amp;sdata=ZVrLuWSb40XEWMpidctDyJ2cZiGOkGeX%2FJd%2FX2vTSjw%3D&amp;reserved=0

Wouldn’t the optimiser then discard dynamic sampling results because there was no matching data in the sample?

A slightly lighter sledgehammer would be using /*+ DYNAMIC_SAMPLING(table,11) */.

[Mladen Gogala]

Well, that depends on the version. In Oracle 12.2, no it will not. In
Oracle 12.1, I believe that it depends on the value of
optimizer_adaptive_features_enable parameter. I haven't tested the 11G
version. You may be right about the 11G version.

Post by Dominic Brooks
Wouldn’t the optimiser then discard dynamic sampling results because there
was no matching data in the sample?
Sent from my iPhone
A slightly lighter sledgehammer would be using /*+
DYNAMIC_SAMPLING(table,11) */.
The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and c3 = 'N';
and then generate your query with a cardinality hint.
You might want to hint the use of the index for the count(*) query.
Of course you already know this is the sort of thing that the CBO is
supposed to get right routinely and I believe you’ve already done
everything correctly to give it the best possible chance. Sigh.
Now I do take it from your one is Y, one is N values that this is actually
an either or in your database. IF I’m correct about that and C2=Y literally
implies that C3=N, they you might want to code it up that way, leaving C3
out of the database entirely and out of queries as a predicate. If someone
wants it instantiated for them in a query, I supposed you could make C3 a
virtual column. I’m presuming a **lot** for that to be true, and the CBO
should be getting this right as you’ve done it. (C2 could actually be N for
N and null for Y if you really want to get the index as small as possible.)
mwf
*Sent:* Tuesday, July 31, 2018 12:03 PM
*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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
2 - access("C2"='N' AND "C3"='N')
--
Mladen Gogala Database Consultant Tel: [phone]

[Mark W. Farnham]

Now if LS were to add those constraints, then the need for the N and N query
to look for errors would evaporate, the ultimate optimization of not doing
it at all.

I *think* the more buckets issue also might have legs to disambiguate the
*possibility* of N and N rows. With only 2 buckets, they get populated
(presumably) one of the buckets with rows presumably is consulted for the
quantity of the non-existant row combinations.

But I really like the constraint idea, even if the plan generation idea
doesn't yet work, for the no need to run result. I wonder if the aggregate
work to maintain the constraint exceeds even a bad plan occasional check for
errors, and also whether adding the constraint would require "n" points in
the application of change to absorb the (correct) error produced by attempts
to insert N+N rows or update to the N+N result. (Or Y+Y, similarly, left out
for brevity.)

mwf

-----Original Message-----
From: Jonathan Lewis [mailto:***@jlcomp.demon.co.uk]
Sent: Wednesday, August 01, 2018 4:00 PM
To: ***@gmail.com; 'Oracle Mailinglist'; ***@rsiz.com
Subject: Re: extended statistics and non-existent combined values


Mark,

Interesting point - if it's legal to have the constraints:

alter table t1 modify c2 not null;
alter table t1 modify c3 not null;
alter table t1 add constraint c_check check((c2 = 'Y' and c3 = 'N') or (c2 =
'N' and c3 = 'Y'));

Then you might hope that the optimizer would take

explain plan for
select * from t1 where c2 = 'N' and c3 = 'N'
;

select * from t1 where c2 = 'N' and c3 = 'N' and
((c2 = 'Y' and c3 = 'N') or (c2 = 'N' and c3 = 'Y')) ;

And recognise the contradiction and produce the plan:
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 18369 | 73476 | 21 (20)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("C2"='N' AND "C3"='N')


i.e. not actually do anything at all.
Unfortunately this doesn't work - even in 18.1 (LiveSQL).

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on
behalf of Mark W. Farnham <***@rsiz.com>
Sent: 31 July 2018 17:39:05
To: ***@gmail.com; 'Oracle Mailinglist'
Subject: RE: extended statistics and non-existent combined values

The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and
c3 = 'N'; and then generate your query with a cardinality hint.

You might want to hint the use of the index for the count(*) query.

Of course you already know this is the sort of thing that the CBO is
supposed to get right routinely and I believe you've already done everything
correctly to give it the best possible chance. Sigh.

Now I do take it from your one is Y, one is N values that this is actually
an either or in your database. IF I'm correct about that and C2=Y literally
implies that C3=N, they you might want to code it up that way, leaving C3
out of the database entirely and out of queries as a predicate. If someone
wants it instantiated for them in a query, I supposed you could make C3 a
virtual column. I'm presuming a *lot* for that to be true, and the CBO
should be getting this right as you've done it. (C2 could actually be N for
N and null for Y if you really want to get the index as small as possible.)

mwf

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org]
On Behalf Of Ls Cheng
Sent: Tuesday, July 31, 2018 12:03 PM
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')


--
http://www.freelists.org/webpage/oracle-l

[Mark W. Farnham]

Of course the test for more buckets disambiguating is simple: Pick some
power of 2 like 512. If that gets you the correct estimate, cut it in half
until it goes wrong. Since Y+Y is another possible error search, you
probably want to test the estimates for that also.

If there is no joy just using more buckets, then insert a dummy error row
and do enough buckets so you find 1 each for N,N and Y,Y as estimates.

Or it could work differently than I think it should. JL might know in his
head.

mwf

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org]
On Behalf Of Mark W. Farnham
Sent: Thursday, August 02, 2018 11:20 AM
To: 'Jonathan Lewis'; ***@gmail.com; 'Oracle Mailinglist'
Subject: RE: extended statistics and non-existent combined values

Now if LS were to add those constraints, then the need for the N and N query
to look for errors would evaporate, the ultimate optimization of not doing
it at all.

I *think* the more buckets issue also might have legs to disambiguate the
*possibility* of N and N rows. With only 2 buckets, they get populated
(presumably) one of the buckets with rows presumably is consulted for the
quantity of the non-existant row combinations.

But I really like the constraint idea, even if the plan generation idea
doesn't yet work, for the no need to run result. I wonder if the aggregate
work to maintain the constraint exceeds even a bad plan occasional check for
errors, and also whether adding the constraint would require "n" points in
the application of change to absorb the (correct) error produced by attempts
to insert N+N rows or update to the N+N result. (Or Y+Y, similarly, left out
for brevity.)

mwf

-----Original Message-----
From: Jonathan Lewis [mailto:***@jlcomp.demon.co.uk]
Sent: Wednesday, August 01, 2018 4:00 PM
To: ***@gmail.com; 'Oracle Mailinglist'; ***@rsiz.com
Subject: Re: extended statistics and non-existent combined values


Mark,

Interesting point - if it's legal to have the constraints:

alter table t1 modify c2 not null;
alter table t1 modify c3 not null;
alter table t1 add constraint c_check check((c2 = 'Y' and c3 = 'N') or (c2 =
'N' and c3 = 'Y'));

Then you might hope that the optimizer would take

explain plan for
select * from t1 where c2 = 'N' and c3 = 'N'
;

select * from t1 where c2 = 'N' and c3 = 'N' and
((c2 = 'Y' and c3 = 'N') or (c2 = 'N' and c3 = 'Y')) ;

And recognise the contradiction and produce the plan:
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 18369 | 73476 | 21 (20)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("C2"='N' AND "C3"='N')


i.e. not actually do anything at all.
Unfortunately this doesn't work - even in 18.1 (LiveSQL).

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on
behalf of Mark W. Farnham <***@rsiz.com>
Sent: 31 July 2018 17:39:05
To: ***@gmail.com; 'Oracle Mailinglist'
Subject: RE: extended statistics and non-existent combined values

The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and
c3 = 'N'; and then generate your query with a cardinality hint.

You might want to hint the use of the index for the count(*) query.

Of course you already know this is the sort of thing that the CBO is
supposed to get right routinely and I believe you've already done everything
correctly to give it the best possible chance. Sigh.

Now I do take it from your one is Y, one is N values that this is actually
an either or in your database. IF I'm correct about that and C2=Y literally
implies that C3=N, they you might want to code it up that way, leaving C3
out of the database entirely and out of queries as a predicate. If someone
wants it instantiated for them in a query, I supposed you could make C3 a
virtual column. I'm presuming a *lot* for that to be true, and the CBO
should be getting this right as you've done it. (C2 could actually be N for
N and null for Y if you really want to get the index as small as possible.)

mwf

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org]
On Behalf Of Ls Cheng
Sent: Tuesday, July 31, 2018 12:03 PM
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')


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

[Ls Cheng]

Hi

I think I will opt the solution of adding a dummy row which satisfies N-N
condition.

Thanks all

Post by Mark W. Farnham
Of course the test for more buckets disambiguating is simple: Pick some
power of 2 like 512. If that gets you the correct estimate, cut it in half
until it goes wrong. Since Y+Y is another possible error search, you
probably want to test the estimates for that also.
If there is no joy just using more buckets, then insert a dummy error row
and do enough buckets so you find 1 each for N,N and Y,Y as estimates.
Or it could work differently than I think it should. JL might know in his
head.
mwf
-----Original Message-----
On Behalf Of Mark W. Farnham
Sent: Thursday, August 02, 2018 11:20 AM
Subject: RE: extended statistics and non-existent combined values
Now if LS were to add those constraints, then the need for the N and N query
to look for errors would evaporate, the ultimate optimization of not doing
it at all.
I *think* the more buckets issue also might have legs to disambiguate the
*possibility* of N and N rows. With only 2 buckets, they get populated
(presumably) one of the buckets with rows presumably is consulted for the
quantity of the non-existant row combinations.
But I really like the constraint idea, even if the plan generation idea
doesn't yet work, for the no need to run result. I wonder if the aggregate
work to maintain the constraint exceeds even a bad plan occasional check for
errors, and also whether adding the constraint would require "n" points in
the application of change to absorb the (correct) error produced by attempts
to insert N+N rows or update to the N+N result. (Or Y+Y, similarly, left out
for brevity.)
mwf
-----Original Message-----
Sent: Wednesday, August 01, 2018 4:00 PM
Subject: Re: extended statistics and non-existent combined values
Mark,
alter table t1 modify c2 not null;
alter table t1 modify c3 not null;
alter table t1 add constraint c_check check((c2 = 'Y' and c3 = 'N') or (c2 =
'N' and c3 = 'Y'));
Then you might hope that the optimizer would take
explain plan for
select * from t1 where c2 = 'N' and c3 = 'N'
;
select * from t1 where c2 = 'N' and c3 = 'N' and
((c2 = 'Y' and c3 = 'N') or (c2 = 'N' and c3 = 'Y')) ;
------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 18369 | 73476 | 21 (20)| 00:00:01 |
------------------------------------------------------------
---------------
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("C2"='N' AND "C3"='N')
i.e. not actually do anything at all.
Unfortunately this doesn't work - even in 18.1 (LiveSQL).
Regards
Jonathan Lewis
________________________________________
Sent: 31 July 2018 17:39:05
Subject: RE: extended statistics and non-existent combined values
The sledge hammer approach is to select count(*) from t20 where c2 = 'N' and
c3 = 'N'; and then generate your query with a cardinality hint.
You might want to hint the use of the index for the count(*) query.
Of course you already know this is the sort of thing that the CBO is
supposed to get right routinely and I believe you've already done everything
correctly to give it the best possible chance. Sigh.
Now I do take it from your one is Y, one is N values that this is actually
an either or in your database. IF I'm correct about that and C2=Y literally
implies that C3=N, they you might want to code it up that way, leaving C3
out of the database entirely and out of queries as a predicate. If someone
wants it instantiated for them in a query, I supposed you could make C3 a
virtual column. I'm presuming a *lot* for that to be true, and the CBO
should be getting this right as you've done it. (C2 could actually be N for
N and null for Y if you really want to get the index as small as possible.)
mwf
On Behalf Of Ls Cheng
Sent: Tuesday, July 31, 2018 12:03 PM
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 |
------------------------------------------------------------
----------------
----------
---------------------------------------------------
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 |
------------------------------------------------------------
----------------
----------
---------------------------------------------------
2 - access("C2"='N' AND "C3"='N')
--
http://www.freelists.org/webpage/oracle-l

[Timur Akhmadeev]

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

Post by Ls Cheng
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 |
--------------------------------------------------------------------------------------
---------------------------------------------------
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 |
--------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("C2"='N' AND "C3"='N')
--

Regards
Timur Akhmadeev

[Mark W. Farnham]

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-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Timur Akhmadeev
Sent: Tuesday, July 31, 2018 1:29 PM
To: ***@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 <***@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')

[Ls Cheng]

Hi

Right now we have

C2 => N or Y
C3 => N or Y

But the users are only interested in finding N and N (erroneous records) so
I need 3 buckets.

Thanks

Post by Mark W. Farnham
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
freelists.org] *On Behalf Of *Timur Akhmadeev
*Sent:* Tuesday, July 31, 2018 1:29 PM
*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.
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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
2 - access("C2"='N' AND "C3"='N')
--
Regards
Timur Akhmadeev

[Ls Cheng]

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

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
________________________________________
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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
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 |
------------------------------------------------------------
--------------------------
---------------------------------------------------
2 - access("C2"='N' AND "C3"='N')

[l***@bluewin.ch]

Hi,
I think the issue is that 2 potenial buckets of the histogram do not shop up because there is no data for it.
In theory we have 4 combinations YY, NN, NY, YN.
When we looks at stats we see only tow buckets of course:
COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
-------------------------------- ------------ ---------- ----------- ---------------
C1 222988 ,00000448455128 256 HEIGHT BALANCED
C2 2 ,00000112113908 2 FREQUENCY
C3 2 ,00000112113908 2 FREQUENCY
SYS_STUOXVZ1C2WGW4DRVBD89VDEO_ 2 ,00000112113908 2 FREQUENCY
select num_rows from user_tables where table_name='T20';
445975
The estimate looks like roughly 10% of the rows..
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 160 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| T20 | 44332 | 389K| 160 (5)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - storage(("C2"='N' AND "C3"='N'))
filter(("C2"='N' AND "C3"='N'))

Actually the estimate for a missing bucket is not supposed to be zero, but 10% seems far to high. I wonder if extended stats are used at all for this estimate.
When we check for an existing combination the estimate is spot on.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 31/07/2018 - 18:03 (GMT)
An : oracle-***@freelists.org
Betreff : 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')