I have a query. In Oracle the column NUM_DISTINCT of the table table ALL_TAB_COLUMNS showing the number of different values throws me a number that if I compare with the following statement, I get different data:
select count(distinct(CUSTOMER)) from CONTACT
Data using table ALL_TAB_COLUMNS: 23904256
Data using query: 24122992
Does anyone know what the difference is?
I remain attentive to prompt assistance.
Thank you very much.
There are two different ways to count the records of a table:
select count(distinct(CUSTOMER)) from CONTACT
This query executes the actual "count" immediately, it will represent the most accurate number since it reflects the status at the time of the query.
On the other hand do something like this:
select column_name, num_distinct
from all_tab_columns
where table_name = 'CONTACT'
and column_name = 'CUSTOMER'
It is to search for information in a system view, not directly to the physical table. This view reflects the state of the table at the time when the last statistics were generated (whose times the engine handles), which surely in a table with a lot of movement or many records will produce discrepancies with the first method.