Wrong rounded cardinality calculation of a UNION-ALL inline view

10053.sql

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  X  Alias:  X  (NOT ANALYZED)
  #Rows: 2000  SSZ: 0  LGR: 0  #Blks:  10  AvgRowLen:  9.00  NEB: 0  ChainCnt:  0.00  ScanRate:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#3): YN(VARCHAR2)  NO STATISTICS (using defaults)
    AvgLen: 1 NDV: 0 Nulls: 0 Density: 0.000000
  Column (#2): STATUS(NUMBER)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000
  Column (#1): ID(NUMBER)
    AvgLen: 4 NDV: 1000 Nulls: 0 Density: 0.001000 Min: 1.000000 Max: 1000.000000
try to generate single-table filter predicates from ORs for query block SEL$3 (#0)
finally: "X"."YN"='Y'

=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$3 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for X
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for X[X] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

 kkecdn: Single Table Predicate:"X"."YN"='Y'
  Table: X  Alias: X
    Card: Original: 2000.000000  Rounded: 20  Computed: 20.000000  Non Adjusted: 20.000000
  Scan IO  Cost (Disk) =   4.000000
  Scan CPU Cost (Disk) =   451214.400000
  Cost of predicates:
    io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048  ("X"."YN"='Y')
  Total Scan IO  Cost  =   4.000000 (scan (Disk))
                         + 0.000000 (io filter eval) (= 0.000000 (per row) * 2000.000000 (#rows))
                       =   4.000000
  Total Scan CPU  Cost =   451214.400000 (scan (Disk))
                         + 100000.000000 (cpu filter eval) (= 50.000000 (per row) * 2000.000000 (#rows))
                       =   551214.400000
  Access Path: TableScan
    Cost:  4.018618  Resp: 4.018618  Degree: 0
      Cost_io: 4.000000  Cost_cpu: 551214
      Resp_io: 4.000000  Resp_cpu: 551214
  Best:: AccessPath: TableScan
         Cost: 4.018618  Degree: 0  Resp: 4.018618  Card: 20.000000  Bytes: 0.000000

***************************************
<skipped>
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
  #Rows: 1000  SSZ: 0  LGR: 0  #Blks:  5  AvgRowLen:  9.00  NEB: 0  ChainCnt:  0.00  ScanRate:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#3): YN(VARCHAR2)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000
  Column (#1): ID(NUMBER)
    AvgLen: 4 NDV: 1000 Nulls: 0 Density: 0.001000 Min: 1.000000 Max: 1000.000000
try to generate single-table filter predicates from ORs for query block SEL$1 (#0)
finally: "T1"."YN"='Y'

=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T1] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

 kkecdn: Single Table Predicate:"T1"."YN"='Y'
  Estimated selectivity: 0.500000 , col: #3 
  Table: T1  Alias: T1
    Card: Original: 1000.000000  Rounded: 500  Computed: 500.000000  Non Adjusted: 500.000000
  Scan IO  Cost (Disk) =   3.000000
  Scan CPU Cost (Disk) =   205607.200000
  Cost of predicates:
    io = NOCOST, cpu = 50.000000, sel = 0.500000 flag = 2048  ("T1"."YN"='Y')
  Total Scan IO  Cost  =   3.000000 (scan (Disk))
                         + 0.000000 (io filter eval) (= 0.000000 (per row) * 1000.000000 (#rows))
                       =   3.000000
  Total Scan CPU  Cost =   205607.200000 (scan (Disk))
                         + 50000.000000 (cpu filter eval) (= 50.000000 (per row) * 1000.000000 (#rows))
                       =   255607.200000
  Access Path: TableScan
    Cost:  3.008633  Resp: 3.008633  Degree: 0
      Cost_io: 3.000000  Cost_cpu: 255607
      Resp_io: 3.000000  Resp_cpu: 255607
  Best:: AccessPath: TableScan
         Cost: 3.008633  Degree: 1  Resp: 3.008633  Card: 500.000000  Bytes: 0.000000

***************************************

Results

SQL> explain plan for
  2  with x as
  3  (select t1.* from t1
  4   union all
  5   select t1.* from t1)
  6  select *
  7  from x
  8  where yn = 'Y';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3505968351

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    20 |   160 |     4   (0)| 00:00:01 |
|   1 |  VIEW               |      |  1000 |  9000 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |   500 |  4500 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |   500 |  4500 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   3 - filter("T1"."YN"='Y')
   4 - filter("T1"."YN"='Y')

17 rows selected.

SQL>
SQL> alter table t1 modify status invisible;

Table altered.

SQL> alter table t1 modify status visible;

Table altered.

SQL>
SQL> explain plan for
  2  with x as
  3  (select t1.* from t1
  4   union all
  5   select t1.* from t1)
  6  select *
  7  from x
  8  where yn = 'Y';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3505968351

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 28000 |     6   (0)| 00:00:01 |
|   1 |  VIEW               |      |  1000 | 28000 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |   500 |  4500 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |   500 |  4500 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   3 - filter("T1"."YN"='Y')
   4 - filter("T1"."YN"='Y')

17 rows selected.

test-case.sql

drop table t1;
set echo on;

create table t1
(id            number not null
,status        int generated always as (decode(yn,'Y',1,2))
,yn            varchar2(1) not null
);

insert into t1
(id, yn)
select rownum
,      case when mod(rownum,2) = 1 then 'Y' else 'N' end
from   dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(USER,'T1'); 

explain plan for
with x as
(select t1.* from t1
 union all
 select t1.* from t1)
select *
from x
where yn = 'Y';

select * from table(dbms_xplan.display); 

alter table t1 modify status invisible;
alter table t1 modify status visible;

explain plan for
with x as
(select t1.* from t1
 union all
 select t1.* from t1)
select *
from x
where yn = 'Y';

select * from table(dbms_xplan.display); 

PS. From 10053 looks like CBO incorrectly calculates "Rounded cardinality":

 kkecdn: Single Table Predicate:"X"."YN"='Y'
  Table: X  Alias: X
    Card: Original: 2000.000000  Rounded: 20  Computed: 20.000000  Non Adjusted: 20.000000

And I forgot to describe why it helps: making this column invisible and visible again changes columns order: after the change this virtual column becomes "last" column in the table:

SQL> create table t1
  2  (id            number not null
  3  ,status        int generated always as (decode(yn,'Y',1,2))
  4  ,yn            varchar2(1) not null
  5  );

SQL> select column_name,column_id,segment_column_id,internal_column_id from user_tab_cols tc where table_name='T1';

COLUMN_NAME   COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------ ---------- ----------------- ------------------
ID                    1                 1                  1
STATUS                2                                    2
YN                    3                 2                  3

SQL> alter table t1 modify status invisible;
SQL> alter table t1 modify status visible;
SQL> select column_name,column_id,segment_column_id,internal_column_id from user_tab_cols tc where table_name='T1';

COLUMN_NAME   COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------ ---------- ----------------- ------------------
ID                    1                 1                  1
STATUS                3                                    2
YN                    2                 2                  3

3 rows selected.

As you can see its INTERNAL_COLUMN_ID is still 2, but COLUMN_ID is 3 now.
And you will get another column order in case of using *
Just another reason to do not use * without dynamic cursor definition :)

SQL> select * from t1 where rownum=1;

        ID YN     STATUS
---------- -- ----------
         1  Y          1