On Sat, 21 Jul 2001, Sean Chittenden wrote:
> > > I thought it was worth trying a different query as well :
> > >
> > > SELECT count(*) FROM fact0
> > >
> > > DB Elapsed Cpu
> > > Postgres 1m5s 32s
> > > Db2 23s 15s
> > > Oracle 37s 11s
>
> This may be an Oracle DBA myth, but I was told by my ORA DBA
> that it should be "SELECT count(1) FROM fact0" and not count(*). For
> some reason it was thought that count(1) would run faster, but I can't
> confirm or deny this. Does this make a difference in the benchmark?
> -sc
Actually, your oracle DBA was smoking crack. The real query is:
SELECT COUNT(rowid) FROM fact0;
RowID is a specially indexed field that Oracle uses to go DIRECTLY to a
record through the datafile->tablespace->cluster->row, hence circumvents
both indexes and the table itself.
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+