How to get RecordCount without "Select count(*)"

Thank you very much,

if I have SAL field (number), I get SAL total as 'SELECT SUM(SAL) TOTAL from...', however this SQL command return the result very fast but 'SELECT count(*) from...' return the result very slow?

what is the explain plan of these stmts ?
try write "SELECT count(SAL) from ..." and see what happen ?

You probably have an index on SAL and the SUM(SAL) is using the index. COUNT(*) forces a full table scan.

I disagree with Urim's first answer.  There are at least two ways to get faster counts in many cases.

The first way is by forcing Oracle to count index entries rather than a full table scan.  You need to add a where clause referencing a not null, indexed column.
For example, in a customer table, if you have a numeric "cust_no" column as you primary key, use this statement:

select count(cust_no) "Qty"
from customer
where cust_no > 0;

The second way will only get you an approximate count and only if you analyze your tables regulary, but it will be very fast (especially if you add a where clause for a particular table).  Try this:

select table_name, num_rows
from user_tables;

markgeer, the answer I was answering on was to get the number of records in a tables faster than count(*).
If count(*) is slow, with the best explain plan how can you bring me the number of records from the table with better response time ? (you need to run your query with different explain plan, and if you can improve the explain plan you can improve the count(*).)
The second way you suggest is good if there are statsitics on the table, is not the select you put on a prod environment.

Select count(*) usually does not give the best possible explain plan, it usually results in a full-table scan.  A count of indexed values can result in only an index scan, usually a much smaller number of blocks.

I see no reason for not analyzing tables regularly (at least weekly if not daily) in most production environments, especially if the larger tables are just estimated.  If those statistics are available, then I don't know why this kind of select would not be used in a production environment.

Best and easy way for your problem ?

select max(rownum) from tablename

e.g.

select max(rownum) from scott.emp

I hope it will solve your problem.

markgeer,
for your opinon if there is unique index on COL1 then select count(*) will do full table scan, while select count(COL1) won't ?

For quering the dictonary to the number of rows, not always it a good solution, for example, I want to know how much records are now! and not yesterday or week before.

Analyze table < Table_name > estiamte statistics;
select num_rows from all_tables
where table_name ='< Table_name >;

this is  fast & the num_rows is an estimate;
Also try
Analyze table < Table_name > Compute Statistics;
Then select from all_tables
this is acurate .