Gather the statistics for subpartition of the table in Oracle

Check the statistics for subpartition of the table in Oracle

-- Check the sub partition table statistics
Select * from USER_TAB_SUBPARTITIONS;

--Check the sub partition col statistics.
Select * from USER_SUBPART_COL_STATISTICS;

-- Check the Sub partition histograms
Select * from USER_SUBPART_HISTOGRAMS;

--Check the sub partition indexes
Select * from USER_SUBPART_INDEXES

Gather the subpartition stats including indexes:

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'SALES',
 GRANULARITY => 'SUBPARTITION' ,CASCADE => TRUE);

Gather the subpartition statistics for the table without indexes:

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'SALES',
 CASCADE => FALSE ,GRANULARITY => 'SUBPARTITION',METHOD_OPT => 'FOR COLUMNS');