Tom - I know the number of rows reported in an index does not match the number of rows in it's related table because of nulls.. but I'm more curious about what effects a bitmap index's own num_rows report. Here is a partitioned table with many indexes on it.. BMI meaning bitmap indexes.. I'm wondering how to account for the large discrpency.. is it simply cardinatliy? What would a bitmap index on a date do if there were not just a few distinct dates. Here is some output. SQL> select num_rows, partition_name, last_analyzed
2 from dba_tab_partitions
3 where table_name='COST_TIME' order by 2;
NUM_ROWS PARTITION_NAME LAST_ANAL
---------- ------------------------------ ---------
2754846 P1999 04-OCT-11
9388967 P2000 04-OCT-11
7157649 P2001 04-OCT-11
6680070 P2002 04-OCT-11
9268967 P2003 04-OCT-11
9609117 P2004 04-OCT-11
10048222 P2005 04-OCT-11
10476136 P2006 05-OCT-11
11064963 P2007 05-OCT-11
11929334 P2008 05-OCT-11
11299463 P2009 05-OCT-11
11388296 P2010 05-OCT-11
12380712 P2011 05-OCT-11
0 P2012 05-OCT-11
1 select partition_name, index_name,num_rows, last_analyzed
2 from dba_ind_partitions
3 where
4 --table_name='COST_TIME'
5 partition_name='I2011' or partition_name='I2010'
6* Order by 1,2
SQL> /
PARTITION_NAME INDEX_NAME NUM_ROWS LAST_ANAL
------------------------------ ------------------------------ ---------- ---------
I2010 cost_time_SUM_BMI1 291333 05-OCT-11
I2010 cost_time_SUM_BMI2 268626 05-OCT-11
I2010 cost_time_SUM_BMI3 91230 05-OCT-11
I2010 cost_time_SUM_BMI4 928005 05-OCT-11
I2010 cost_time_SUM_BMI5 11518 05-OCT-11
I2010 cost_time_SUM_BMI6 260573 05-OCT-11
I2010 cost_time_SUM_BMI7 294220 05-OCT-11
I2010 cost_time_SUM_BMI8 273906 05-OCT-11
I2010 cost_time_SUM_BMI9 541255 05-OCT-11
I2010 cost_time_SUM_FB01 11388296 05-OCT-11
I2010 cost_time_SUM_I2 11388296 17-OCT-11
I2010 cost_time_SUM_I7 11388296 05-OCT-11
I2011 cost_time_SUM_BMI1 1867404 05-OCT-11
I2011 cost_time_SUM_BMI2 1747974 05-OCT-11
I2011 cost_time_SUM_BMI3 565685 05-OCT-11
I2011 cost_time_SUM_BMI4 6053955 05-OCT-11
I2011 cost_time_SUM_BMI5 69055 05-OCT-11
I2011 cost_time_SUM_BMI6 1747510 05-OCT-11
I2011 cost_time_SUM_BMI7 1968793 05-OCT-11
I2011 cost_time_SUM_BMI8 1799760 05-OCT-11
I2011 cost_time_SUM_BMI9 3223957 05-OCT-11
I2011 cost_time_SUM_FB01 12380712 05-OCT-11
I2011 cost_time_SUM_I2 12380712 17-OCT-11
I2011 cost_time_SUM_I7 12380712 05-OCT-11
bitmap indexes store a single key value that points to many rows. Therefore, there will be significantly less key values (rows) in a bit map index than in the table/segment it points to.
The num rows is the number of "rows" in the segment in question, for an index - that would be the key values.
You'll see this with b*tree indexes as well:
ops$tkyte%ORA11GR2> create table t
2 as
3 select decode( mod(rownum,100), 1, object_id ) id, all_objects.*
4 from all_objects
5 /
Table created.
ops$tkyte%ORA11GR2> create index t_idx on t(id);
Index created.
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select num_rows from user_tables where table_name = 'T';
NUM_ROWS
----------
72238
ops$tkyte%ORA11GR2> select num_rows from user_indexes where index_name = 'T_IDX';
NUM_ROWS
----------
723
since entirely null key entries are NOT made in b*tree indexes (but would be in a bitmap!) - only 1% of the rows appear in the b*tree in this case.
For a bitmap index - since each "row" in the index points to many "rows" in the table - you'll see fewer rows in the index than the table.