Home » SQL & PL/SQL » SQL & PL/SQL » metadata about tables (10.)
metadata about tables [message #356688] Sat, 01 November 2008 02:14 Go to next message
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 #356691 is a reply to message #356688] Sat, 01 November 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_tab_columns but in order to get the number either you or Oracle (with dbms_stats) has to scan the table.
Now it depends if you want the exact number or an estimate one.

Regards
Michel
Re: metadata about tables [message #356692 is a reply to message #356688] Sat, 01 November 2008 02:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you need the exact number, or would an estimate suffice?
Analyzing data for such big tables can be done using the sample clause
Re: metadata about tables [message #356702 is a reply to message #356692] Sat, 01 November 2008 03:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64111
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
Previous Topic: BULK COLLECT LIMIT generate ORA-01401
Next Topic: Help with TRIGGERS
Goto Forum:
  


Current Time: Mon Dec 05 19:18:41 CST 2016

Total time taken to generate the page: 0.10207 seconds