Thanks for the question, VV.
Asked: April 10, 2018 - 4:48 pm UTC
Last updated: April 11, 2018 - 3:37 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi - I just ran a full schema stats on one of our schemas. After the full schema stats was complete, I checked the num_rows in dba_tables and num_rows in dba_indexes. The num_rows from dba_tables looks good but num_rows from dba_indexes shows as 0 for a specific table. Why does that happen? how do we fix this ?
and Connor said...
Those stats might be perfectly accurate. Don't forget that entirely null entries are not indexed, so the number of rows in an index can be less than the number of rows in a table, eg
SQL> create table t as
2 select rownum x from dual
3 connect by level <= 10;
Table created.
SQL>
SQL> alter table t add y int;
Table altered.
SQL>
SQL> select * from t;
X Y
---------- ----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
SQL> create index ix on t ( y ) ;
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T')
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows from user_tables where table_name = 'T';
NUM_ROWS
----------
10
1 row selected.
SQL>
SQL> select num_rows from user_indexes where table_name = 'T';
NUM_ROWS
----------
0
1 row selected.
Is this answer out of date? If it is, please let us know via a Comment