Re: showing -ve value in num_nulls!!!

From: Erwin Dondorp <erwindon_at_wxs.nl>
Date: Tue, 01 May 2001 23:20:21 +0200
Message-ID: <3AEF2895.C5D68961_at_wxs.nl>


Aparna wrote:
> [...]
> select column_name, num_nulls, num_distinct from user_tab_columns where
> table_name = 'TABLE1';
> the value displayed for num_nulls for one column (step_no) was -56 when
> infact there are no rows containing null values for that column... he then
> tried creating another table as a duplicate of table1 using
> create table temp1 as select * from table1;
> on checking the num_nulls in temp1, the result was the same -56 for the
> column step_no...
> [...]
> the data is not corrupt.. the application using the data is working fine and
> showing accurate data.. but what we don't understand is how can num_nulls
> display a -ve value??? any guesses???

Aparna,

These columns are only populated when you have used an ANALYZE statement on these tables. Someone might have done that in the past for table1, but it seems like you did not do that for temp1. If you have not used the ANALYZE statement on these tables before, the num_nulls and num_distinct columns are NULL values.

This makes me wonder whether you are using something like Pro*C here? In that case a bound variable has more or less a random value when in the
database you have a NULL value, since you should test for a NULL value using an indicator variable.

Try rephrasing your query to

	select column_name, NVL(num_nulls, 123), NVL(num_distinct, 456)
	from user_tab_columns
	where table_name = 'TABLE1';

If you have used the ANALYZE statements, please tell us which form you used.

E. Received on Tue May 01 2001 - 23:20:21 CEST

Original text of this message