0

Does an extended metadata table exist in oracle 19 holding column statistics from all tables? I know there is the table ALL_TAB_COL_STATISTICS which stores histogram, min, max, num distinct values etc. but I need additional statistics such as mean, median, or percentiles? If there is such a statistics table how and when gets the table updated? I do not want to calculate the statistics myself but want to extract them as metadata.

Thanks for your help.

KingLui81
  • 53
  • 1
  • 7

1 Answers1

2

I believe you are looking for something that Oracle never intended to. Oracle Statistics are information useful for the CBO ( Cost Based Optimizer ) in order to determine the best execution plan for any SQL statement. It stores information regarding the density and cardinality of the data stored there, but you want analytical data of it, which is not the purpose of these statistics.

You always might rely in Oracle Functions to retrieve those:

select avg(column) as mean_rating , MEDIAN(column) OVER () AS median_value from 
yourtable

Regarding percentiles, I would check PERCENTIL_CONT and PERCENTILE_DISC. But like I said, you won't find those in any metadata dictionary table.

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43