Home » RDBMS Server » Performance Tuning » NumNull Stats
NumNull Stats [message #347686] Fri, 12 September 2008 14:54 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:

NUM_NULL Column Statistic

NUM_NULLS indicates the number of null values for that column.
Low

If a single column index has few nulls, such as the COST_DISTRIBUTED_FLAG column in Example 3-5, and if this column is used as the index, then the resulting data set is large.
High

If there are many nulls on a particular column, such as the CONVERTED_FLAG column in Example 3-5, and if this column is used as the index, then the resulting data set is small. This means that COST_DISTRIBUTED_FLAG is a more appropriate column to index.



I'm a bit confused on this statement on the docs. What does it mean by the "data set is large", and says that the cost_distributed_flag as for the right to be indexed? Shouldn't it go for the small set to return? Or am I interpreting it in a wrong way. Thanks!

*please moved to performance, sorry for wrong section.

[Updated on: Fri, 12 September 2008 14:56]

Report message to a moderator

Re: NumNull Stats [message #347705 is a reply to message #347686] Sat, 13 September 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should always post the origin of what you quote, possibly with a link then we can read it.
We have not the example mentioned in your text so how could we answer if it is wise to index or not the column.
Indexing depends on the queries as well as result data set.

Generally speaking, if there are many null values then the index contains "small" (relatively to total) number of rows.

Regards
Michel

Re: NumNull Stats [message #347738 is a reply to message #347705] Sat, 13 September 2008 08:13 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Sorry for that. Here's what I'm currently reading.
Re: NumNull Stats [message #347745 is a reply to message #347738] Sat, 13 September 2008 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The documentation is misleading (and the query is syntaxically wrong), it depends on the total number of rows in the table.

You should read "resulting data set" as "number of indexed rows" in this section.
Resulting data set depends on table number of rows, num_distinct and num_nulls.

Regards
Michel
Re: NumNull Stats [message #347757 is a reply to message #347745] Sat, 13 September 2008 13:36 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks! so it's like a bug on the docu. On that statement above, it is correct on that the column with less null is more appropriate for indexing because it would *make* much more list of rows to indexed?, and those with high occurences of null are not.
Re: NumNull Stats [message #347758 is a reply to message #347757] Sat, 13 September 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't say anything from num_nulls alone, you have to know the value for the other parameters (assuming the conditions are equalities like in the example of the documentation).

Regards
Michel
Re: NumNull Stats [message #347759 is a reply to message #347758] Sat, 13 September 2008 14:00 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
you have to know the value for the other parameters


you mean the others like num_distinct and num_rows?
Re: NumNull Stats [message #347761 is a reply to message #347759] Sat, 13 September 2008 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: NumNull Stats [message #347762 is a reply to message #347761] Sat, 13 September 2008 15:26 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks Michel!
Previous Topic: Interpreting Histograms
Next Topic: TKPROF Fetch stats
Goto Forum:
  


Current Time: Sat Dec 10 03:07:51 CST 2016

Total time taken to generate the page: 0.10592 seconds