-- https://franckpachot.medium.com/oracle-global-vs-partition-level-statistics-cbo-usage-1c2aa2aa3f32

create table DEMO (day date) partition by range(day) (
     partition P2018 values less than (date '2019-01-01'),
     partition P2019 values less than (date '2020-01-01'),
     partition P2020 values less than (date '2021-01-01'),
     partition P2021 values less than (date '2022-01-01'),
     partition INFINIT values less than (MAXVALUE)
    );

insert into DEMO 
     select date '2019-01-01'+rownum from xmltable('1 to 100');
insert into DEMO 
     select date '2018-01-01'-rownum/24 from xmltable('1 to 5000');
commit;

exec dbms_stats.gather_table_stats(user,'DEMO');

set lines 256

col OWNER for a20
col TABLE_NAME for a20
col PARTITION_NAME for a20
col LAST_ANALYZED for a20
col GLOBAL_STATS for a3
col STALE_STATS for a5

select OWNER,TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STALE_STATS from dba_tab_statistics where table_name='DEMO';

OWNER                TABLE_NAME           PARTITION_NAME         NUM_ROWS LAST_ANALYZED        GLO STALE
-------------------- -------------------- -------------------- ---------- -------------------- --- -----
POC                  DEMO                                            5100 20-JAN-24            YES NO
POC                  DEMO                 P2020                         0 20-JAN-24            YES NO
POC                  DEMO                 INFINIT                       0 20-JAN-24            YES NO
POC                  DEMO                 P2018                      5000 20-JAN-24            YES NO
POC                  DEMO                 P2019                       100 20-JAN-24            YES NO
POC                  DEMO                 P2021                         0 20-JAN-24            YES NO



Pstart = Pstop (PARTITION RANGE SINGLE) -> partition stats are used
--------------

select count(*) from DEMO where day between to_date( '2019-01-08','yyyy-mm-dd' ) and to_date( '2019-02-08','yyyy-mm-dd' ) ;

  COUNT(*)
----------
        32

----------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Pstart| Pstop |
----------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    33 |     2 |     2 |
|   3 |    TABLE ACCESS FULL    | DEMO |    33 |     2 |     2 |
----------------------------------------------------------------

Partition-level statistics are used when only one partition is accessed, known at the time of parsing. 
You see that with Pstart=Pstop = number.

Pstart <> Pstop (PARTITION RANGE ITERATOR/FULL) -> global stats are used
---------------

select count(*) from DEMO where day between to_date( '2019-01-08','yyyy-mm-dd' ) and to_date( '2019-02-08','yyyy-mm-dd' ) ;

 COUNT(*)
----------
        94


select * from dbms_xplan.display_cursor(format=>'basic +rows +outline +peeked_binds +partition');

------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Pstart| Pstop |
------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   705 |     2 |     3 |
|   3 |    TABLE ACCESS FULL      | DEMO |   705 |     2 |     3 |
------------------------------------------------------------------

Note that nomber of rows is overestimated.

Global table level statistics are used when more than one partition is accessed, even when they are known at the time of parsing. 
You see that with Pstart <> Pstop.


KEY — KEY (PARTITION RANGE ITERATOR/FULL) -> partition stats are used
----------

var d1 varchar2(10)
var d2 varchar2(10)
exec :d1:='2019-01-08'; 
exec :d2:='2019-02-08';

select count(*) from DEMO where day between to_date(:d1,'yyyy-mm-dd' ) and to_date(:d2,'yyyy-mm-dd' );

  COUNT(*)
----------
        32


-------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Pstart| Pstop |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |       |       |
|   2 |   FILTER                   |      |       |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |    33 |   KEY |   KEY |
|   4 |     TABLE ACCESS FULL      | DEMO |    33 |   KEY |   KEY |
-------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEMO"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :D1 (VARCHAR2(30), CSID=873): '2019-01-08'
   2 - :D2 (VARCHAR2(30), CSID=873): '2019-02-08'

Partition-level statistics are used when only one partition is accessed, known at the time of parsing, even the value is known with bind peeking. 
You see that with KEY/KEY for Pstart/Pstop and with bind variables listed by +peeked_binds format


Without bind peeking -> global
--------------------

Same previous binded query but with the bind peeking disabled at the session level.

alter system flush shared_pool;

select count(*) from DEMO where day between to_date(:d1,'yyyy-mm-dd' ) and to_date(:d2,'yyyy-mm-dd' );

  COUNT(*)
----------
        32

select * from dbms_xplan.display_cursor(format=>'basic +rows +outline +peeked_binds +partition');

-------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Pstart| Pstop |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |       |       |
|   2 |   FILTER                   |      |       |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |    13 |   KEY |   KEY |
|   4 |     TABLE ACCESS FULL      | DEMO |    13 |   KEY |   KEY |
-------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEMO"@"SEL$1")
      END_OUTLINE_DATA
  */

  The number of rows is underestimated, global stats has been used.

  In conclusion, there is only one case where the partition level statistics can be used: 
    partition pruning to one single partition, at parse time by the literal value or the peeked bind.

--------------------------------------
Extra (example online split partition)
--------------------------------------

-- add the next 5000 days since 1 Jan 2022
insert into DEMO 
     select date '2022-01-01'+rownum from xmltable('1 to 5000');

commit;

-- create P2022, P2022 and P2023 partitions using split online

ALTER TABLE DEMO
  SPLIT PARTITION INFINIT AT (date '2023-01-01')
  INTO (PARTITION P2022,
        PARTITION INFINIT)
  ONLINE;


  ALTER TABLE DEMO
  SPLIT PARTITION INFINIT AT (date '2024-01-01')
  INTO (PARTITION P2023,
        PARTITION INFINIT)
  ONLINE;


  ALTER TABLE DEMO
  SPLIT PARTITION INFINIT AT (date '2025-01-01')
  INTO (PARTITION P2024,
        PARTITION INFINIT)
  ONLINE;


exec dbms_stats.gather_table_stats(user,'DEMO');

col OWNER for a20
col TABLE_NAME for a20
col PARTITION_NAME for a20
col LAST_ANALYZED for a20
col GLOBAL_STATS for a3
col STALE_STATS for a5

select OWNER,TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STALE_STATS from dba_tab_statistics where table_name='DEMO';

OWNER                TABLE_NAME           PARTITION_NAME         NUM_ROWS LAST_ANALYZED        GLO STALE
-------------------- -------------------- -------------------- ---------- -------------------- --- -----
POC                  DEMO                                           10100 20-JAN-24            YES NO
POC                  DEMO                 P2020                         0 20-JAN-24            YES NO
POC                  DEMO                 P2022                       364 20-JAN-24            YES NO
POC                  DEMO                 INFINIT                    3905 20-JAN-24            YES NO
POC                  DEMO                 P2018                      5000 20-JAN-24            YES NO
POC                  DEMO                 P2019                       100 20-JAN-24            YES NO
POC                  DEMO                 P2021                         0 20-JAN-24            YES NO
POC                  DEMO                 P2024                       366 20-JAN-24            YES NO
POC                  DEMO                 P2023                       365 20-JAN-24            YES NO


-- check boundaries

select min(day),max(day) from DEMO partition (P2022);

MIN(DAY)  MAX(DAY)
--------- ---------
02-JAN-22 31-DEC-22

select min(day),max(day) from DEMO partition (P2023);

MIN(DAY)  MAX(DAY)
--------- ---------
01-JAN-23 31-DEC-23

select min(day),max(day) from DEMO partition (P2024);

MIN(DAY)  MAX(DAY)
--------- ---------
01-JAN-24 31-DEC-24

