Oracle row count of table by count(*) vs NUM_ROWS from DBA_TABLES
According to the documentation NUM_ROWS is the "Number of rows in the table", so I can see how this might be confusing. There, however, is a major difference between these two methods.
This query selects the number of rows in MY_TABLE from a system view. This is data that Oracle has previously collected and stored.
select num_rows from all_tables where table_name = 'MY_TABLE'
This query counts the current number of rows in MY_TABLE
select count(*) from my_table
By definition they are difference pieces of data. There are two additional pieces of information you need about NUM_ROWS.
In the documentation there's an asterisk by the column name, which leads to this note:
Columns marked with an asterisk (*) are populated only if you collect
statistics on the table with the ANALYZE statement or the DBMS_STATS
package.This means that unless you have gathered statistics on the table then this column will not have any data.
Statistics gathered in 11g+ with the default
estimate_percent
, or with a 100% estimate, will return an accurate number for that point in time. But statistics gathered before 11g, or with a customestimate_percent
less than 100%, uses dynamic sampling and may be incorrect. If you gather 99.999% a single row may be missed, which in turn means that the answer you get is incorrect.
If your table is never updated then it is certainly possible to use ALL_TABLES.NUM_ROWS to find out the number of rows in a table. However, and it's a big however, if any process inserts or deletes rows from your table it will be at best a good approximation and depending on whether your database gathers statistics automatically could be horribly wrong.
Generally speaking, it is always better to actually count the number of rows in the table rather then relying on the system tables.
Mismatch in Num_Rows and count
The value in ALL_TABLES.NUM_ROWS
(or USER_TABLES.NUM_ROWS
) is just a statistical information generated by analyze table ...
or dbms_stats.gather_table_stats
procedure. It's not accurate, not real-time information.
Those statistical values are used by Oracle optimizer to compute cost of SQL statement and choose execution plan.
Extracting number of records in each table
Extracting number of records in each table
Then you are completely in a wrong direction while querying DBA_TABLES. It will give you the count of tables owned by a user, but not the count of rows in each table.
There are three ways:
- In pure SQL, the table name must be static. i.e. you will have to list down the table names explicitly and get the count.
SELECT COUNT(*) FROM table_1;
SELECT COUNT(*) FROM table_2;
SELECT COUNT(*) FROM table_3;
...
and so on...
Using a good text editor, you could do it quickly.
- If the statistics are collected up to date, then you could rely on NUM_ROWS in DBA_TABLES. In any case, better gather the statistics first using DBMS_STATS.
SQL> SELECT table_name, num_rows FROM dba_tables WHERE owner='SCOTT';
TABLE_NAME NUM_ROWS
---------- ----------
CLASS 0
SALGRADE 5
BONUS 0
DEPT 4
EMP 14
- In PL/SQL, loop through all the table names, create dynamic sql and execute (ab)using EXECUTE IMMEDIATE.
Row Count and Size For Each Table Under Oracle Schema
select table_name, num_rows, bytes/1047586 MB
from dba_tables t
Inner join dba_segments s
On table_name =segment_name and t.owner =s.owner
Where segment_type='TABLE'
And s.owner='schema'
ORACLE : Find table in database knowing the number of rows
SELECT TABLE_NAME FROM dba_tables WHERE num_rows = 130
-- num_rows = 130 can be replaced with any requirement you have
Related Topics
How to Check Existence of User-Define Table Type in SQL Server 2008
How to Find All Rows with a Null Value in Any Column Using Postgresql
How to Store More Than 255 Char in MySQL Database
Change Column Types in a Huge Table
Postgresql Window Function: Partition by Comparison
Combining Results of Two Select Statements
Doing a Where in on Multiple Columns in Postgresql
Is Order in a Subquery Guaranteed to Be Preserved
Why Should I Capitalize My SQL Keywords
MySQL Statement Combining a Join and a Count
SQL Server Stored Procedure Parameters
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
Arithmetic Overflow Error Converting Numeric to Data Type Numeric
How to Check If Identity_Insert Is Set to on or Off in SQL Server