OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » NumNull Stats

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NumNull Stats [message #347686]

Fri, 12 September 2008 14:54

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: NumNull Stats [message #347738 is a reply to message #347705]

Sat, 13 September 2008 08:13

ehegagoka
Messages: 493
Registered: July 2005

Senior Member

Sorry for that. Here's what I'm currently reading.

Report message to a moderator

Re: NumNull Stats [message #347745 is a reply to message #347738]

Sat, 13 September 2008 09:45

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: NumNull Stats [message #347757 is a reply to message #347745]

Sat, 13 September 2008 13:36

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.

Report message to a moderator

Re: NumNull Stats [message #347758 is a reply to message #347757]

Sat, 13 September 2008 13:44

Michel Cadot
Messages: 68665
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

Report message to a moderator