| Best way to find no. of rows in a table [message #8820] |
Mon, 29 September 2003 06:08  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
Hi All
Please let me know the best way to find the number of records in a table.
I know the below query. Is there any other query
select count(*) from tablename;
Thanks in Advance
Regards
Rajesh
|
|
|
|
|
|
|
|
|
|
| Re: Best way to find no. of rows in a table [message #8837 is a reply to message #8825] |
Mon, 29 September 2003 12:16  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
I dont agree.
count(*) DOES NOT scan for all the columns in a record.All it does is to check for the existence of a row and increment its count.
count(col) gives the count of NON-NULL values of that column.Even if this column has NOT NULL constraint on it, it is NOT faster than count(*).
Infact, when there is a NOT null constraint, Oracle internally converts count(col) into count(*) for optimal execution.
--Lets see if there is any difference ..
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects;
Table created.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
29200
SQL> select count(*) from t;
COUNT(*)
----------
29200
SQL> alter session set sql_Trace=false;
Session altered.
********************************************************************************
-- Lets look at the TKPROF report
select count(object_id)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 820 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 820 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
29200 TABLE ACCESS FULL T
********************************************************************************
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 820 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 820 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
29200 TABLE ACCESS FULL T
********************************************************************************
-- They are same in terms of performance.
--- Now,Lets do One more test. Lets create a unique index on this NOT NULL column and see if there's any performance difference...
SQL> create unique index t_idx on t(object_id);
Index created.
SQL> set autotrace on
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
29200
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (UNIQUE) (Cost=20 Card
=29200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t;
COUNT(*)
----------
29200
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (UNIQUE) (Cost=20 Card
=29200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Both Count(non null unique col) and count(*) used FAST FULL SCAN of the Index to get the count.
I will go for count(*) if I want to get the record count as fast as possible, unless this is a static table and you have analyzed the table, in which case I can get NUM_ROWS from DBA_TABLES.
Hope this clarifies..
Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
