| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Any way to get RowCount by not using Select count(*) from table
On Mon, 04 Jan 1999 02:37:36 GMT, rvernick_at_home.com (Richard Vernick)
wrote:
>There are many ways of achieving the same result. Some ways are more
>efficient than others and I think that is what boonsong was asking.
>
>If your table has a primary key (what table doesn't? :) ) You'll get the
>quickest results by 'select count(customer_id) from customer' because the
>number of reads on an index is less than a full table scan. You should see
>about 15% increase in speed.
You might get 15% increase in speed or you might get 50% decrease in speed. It all depends.
It is true the number of logical I/O for full index scan is usualy lower then that for the full table scan. However there is a difference in how db blocks are read from a table or from an index.
Index blocks are read in the order of index keys *one block at the time*. This means that after reading one block, disk head will probably have to move to the other location to read the next single block and so on... And we all know how disk reads are sloooow.
On the other hand, during full table scan table blocks are read in their physical order (more or less) on the disk, and even more importantly - they will be read multiple blocks per read. The number of blocks read in a single I/O is defined with DB_FILE_MULTIBLOCK_READ_COUNT. So there is a very good chance the full table scan will outperform the full index scan because the physical reads on disks will be more sequential. Of course, it all depends on many factors, the size of the buffer cache is one of them.
Definitelly the fastest method for counting all records in a large table is a new operation called "fast full index scan", available in Oracle 7.3.3 and above. If optimizer chooses this operation (you can help him in this decision with a hint), the index blocks are read in the simmilar fashion as when doing full table scan. There is an article about this fast full index scan in july/august isue of the Oracle Magazine. It is written by Guy Harrison. In the article he also presents the results of his tests counting all rows from a 1,000,000 rows table using different access operations. Average times for counting all rows are:
- using full index scan: 17.76 sec - using full table scan: 12.53 sec - using fast full index scan: 4.94 sec
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |