Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

djberg96 (2603)

djberg96
  (email not shown publicly)

Journal of djberg96 (2603)

Monday August 09, 2004
10:36 AM

Oracle Tuning Tip #2: count

[ #20318 ]
So, you want to do a simple "select count(*) from foo", eh? What's to optimize?

Well, the best way to count the number of rows in a table is to use a fast full-index scan. To do that, use the index_ffs(table,index) hint.

Testing this out on one of our own development tables with approximately 5 million rows, I tried this first:

SELECT count(*)
FROM foo

That took 25 seconds. Then I tried this:

SELECT /*+ index_ffs(f,sys_c0012345) */ count(*)
FROM foo f

That reduced it to 5 seconds. Nice, eh? Supposedly you can improve this even further by using the parallel _index hint as well, but it didn't seem to help in my tests, though I suspect that's due to the way our database is setup.

On a side note, be sure to give your indexes and primary keys meaningful names, so you don't have to refer back to them using Oracle's default "sys_xxxx" naming convention, which is both ugly and not as clear.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.