ALL_INDEXES Table , Num_Rows column

[uparna]

Hi ,
I just noticed this and am not comfortable with this:
I analyze my tables and indexes daily. AS such , all the necessary details should be populated in the statistics tables such as user_tables, all_indexes etc etc. Well , some indexes seem to have this funny problem -> although their base table has well over 400,000 rows , the all_indexes tables has a '0' in the 'num_rows' column(FYI - the cardinality of this column is quite high). Now a similar problem is seen for some of the tables..infact , one of my tables which has >433,000 rows has '0' populated in the num_rows column though it is analyzed everyday. IS there a problem at hand .... or is there some logical explanation for the same?(FYI again -> i do compute statistics for all of them)
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[karluk]

I was not aware that an index or table analyze could generate a zero row count. Have you checked the last_analyzed column of dba_tables and dba_indexes to make sure that you really are doing the analyze?

 

[uparna]

hi Karluk ,
yes...i checked it out...(in fact that was the first thing that i did...)
unsolved mystery? Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[spenglerr]

Hi,

actually it is a kind of mystery. Normally you should get your statistics, the only thing I can think of is:

Do these tables, indexes reside in locally (non-dictionary) managed tablespaces?

 

[uparna]

Hi Spenglerr ,
All these objects are in Dictionary managed tablespaces... the mystery deepens...
well , i did analyze all the tables again, and the stats were populated... I assure you that i ran the same script both before and after the incident.........wonder why this happenned..... Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .