CODEWITHSUNDEEP
sqloracle11g
Shiva
Shiva

Reputation: 727

Trouble in displaying number of rows in table, Oracle express 11g DB

I know two ways to display no. of rows one using count() - slower, other using user_tables -quickie.

select table_name, num_rows from user_tables;

displays, null for 4 tables

TABLE_NAME  NUM_ROWS
TABLEP  
TABLEU  
TABLEN  
TABLE1  
TRANSLATE   26

but

select count(*) from tableu

gives,

COUNT(*)
6

What is the problem here, what should I do so that user_tables will be updated/ or whatever to show exact no. of rows. I have already tried issuing commit statement.

Upvotes: 1

Views: 393

Answers (2)

Justin Cave
Justin Cave

Reputation: 231781

You should not assume or expect that num_rows in user_tables is an accurate row count. The only way to get an accurate row count would be to do a count(*) against the table.

num_rows is used by the cost-based optimizer (CBO) to provide estimates that drive query plans. The actual value does not need to be particularly accurate for those estimates to generate query plans-- if the optimizer guesses incorrectly by a factor of 3 or 4 the number of rows that an operation will produce, that's still likely to be more than accurate enough. This estimate is generated when statistics are gathered on the tables. Generally, that happens late at night and only on tables that are either missing (num_rows is NULL) or are stale (generally meaning that roughly 20% of the rows are new or updated since the last time statistics were gathered). And even then, the values that are generated are normally only estimates, they're not intended to be 100% accurate.

It is possible to call dbms_stats.gather_table_stats to force num_rows to be populated immediately before querying num_rows and to pass parameters to generate a completely accurate value. Of course, that means that gather_table_stats is doing a count(*) under the covers (plus doing additional work to gather additional statistics) so it would be easier and more efficient to have done a count(*) directly in the first place.

Upvotes: 2

Nir Alfasi
Nir Alfasi

Reputation: 53545

num_rows is not accurate since it depends on when is the last time DBMS_STATS package was ran:

exec dbms_stats.gather_schema_stats('ONWER NAME');

Run stats like above and then re-run your query.

Upvotes: 3

Related Questions