rows in tables 1

[smacattack]

I have run query
select table_name,num_rows from user_tables;

The results don't show the number of rows.

How do i find out the number of rows in each table.

If you do a select count(*) on a single table it works!

Please help!

 

[carp]

For the num_rows column to have any data in it, you have to run the analyze..estimate/compute statistics command on the table. Also, realize that this will be a static value that only changes when you rerun this command.

Other than that, I believe SELECT count(*)... is the only way to find out how many rows you have in the table.

 

[alexhu]

This how SQL works - you will have to create a script to do what you want.

If you have 8i, you can do it like this :

scott@8i> create or replace
2 function get_rows( p_tname in varchar2 )
3 return number
4 as
5 l_columnValue number default NULL;
6 begin
7 execute immediate 'select count(*)
8 from ' || p_tname INTO l_columnValue;
9
10 return l_columnValue;
11 end ;
12 /

Function created.

scott@8i>
scott@8i> select table_name, get_rows(table_name)
2 from user_tables
3 /

TABLE_NAME GET_ROWS(TABLE_NAME)
------------------------------ --------------------
BIG_TABLE 3
BLOB_TEST 3
BONUS 0
CHRIS 0
CLOB_DEMO 1
CUSTOMER 0
CUSTOMER_SERVICE 0
DEMO 1
DEPT 4
DUMMY 1
EMP 14
EMPLOYEE 0
EXECUTABLES 4
IMAGE 2
IMAGES 1
IMAGE_TABLE 1
MAPPING_TABLE 8
MYTABLE 0
NEW_EMP 28
ORDERS 0
PRODUCT 0
SALGRADE 5
SPAREPARTCOMB 1
T 3
T1 2
T2 2
TESTLR 0
TEST_IMAGES 1
X 0

29 rows selected.

scott@8i>


As you can imagine -- getting the count(*) will be an expensive
operation and should be used somewhat sparingly.


Courtesy of

http://asktom.oracle.com

Alex

 

[alexhu]

OR

spool tmp.sql
select 'select count(*) from ' || table_name from user_tables;
spool off
@tmp.sql

Alex

 

[rcurva]

But if you have millions of records (say 20 million per table), analyze will definitely give you better and faster estimates. Other than that, your queries will be optimized when you analyze your tables often.

This will also give you a better stand on the effectiveness of your indexes by checking the distinct_keys column from dba_indexes.


To analyze a table:

ANALYZE TABLE table1 COMPUTE STATISTICS;
ANALYZE TABLE table1 ESTIMATE STATISTICS SAMPLE 20000 ROWS;

Of course, the setimate will always be faster than compute.

To delete the statistics:

ANALYZE TABLE table1 DELETE STATISTICS;

 

[alexhu]

To be fair , 'smacattack' didn't ask for an estimate, he wanted a count.

Alex

 

[jimbopalmer]

"estimate" in this context, does give an accurate count, what gets estmated is the number of distict values in a column. The one thing you can't give for your heart's desire is your heart. - Lois McMaster Bujold