metadata about tables [message #356688] |
Sat, 01 November 2008 02:14 |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
Hi,
I need to count the number of null values for a particular column of a table. The table contains trillions of records. So i need a fast method. Is there any table that contains such information?
cheers!
|
|
|
|
|
Re: metadata about tables [message #356702 is a reply to message #356692] |
Sat, 01 November 2008 03:34 |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
thanx.
Estimate wouldn't work. Exact value is required.
Note: The table isn't updated. Does gather stat operation give exact value from user_tab_columns. Or is there any other way to go around.
THANKS
|
|
|
Re: metadata about tables [message #356704 is a reply to message #356702] |
Sat, 01 November 2008 03:38 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
If you need the exact number, the fastest way would be to count.
Is there an index on the column? Any way to trick the db into using the index, and then count the number of non-nulls?
Anyway, I'm afraid you'll end up with a FTS after all.
[Updated on: Sat, 01 November 2008 03:39] Report message to a moderator
|
|
|
Re: metadata about tables [message #356705 is a reply to message #356704] |
Sat, 01 November 2008 03:44 |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
Hi,
You can imagine the time it would require counting distinct values as well as null values for each column of a table that contains trillions of records. The table doesn't have any indexes.
I don't understand why should user_tab_columns not give the exact count. Since the table is not updated, Shouldn't it give the exact count. If it doesn't give the exact count, is it within tolerable range of 0.05%
Thanx
|
|
|
Re: metadata about tables [message #356707 is a reply to message #356705] |
Sat, 01 November 2008 04:32 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
user_tab_columns gives the exact the last time you gathered the full statistics.
If you want to know more, search for dbms_stats in documentation.
Regards
Michel
|
|
|