count(*) vs count(pk_col)

Hi,
I just wanted to know which one is faster,is it count(*) on a table or count(pk_col)(primary key or index column).

I feel count(pk_col) should be faster, correct me if I am wrong and explain this concept

Badrinath

It is Proven fact that the Count(PK) is faster than Count(*) ... Not a big difference the reason is PK_Column hav the index and this was it retrieves the data faster.

The other possibility is Count(Rowid) is also good option.
Hope it helps.

Thanks

Also select count(1) is faster than select count(*), from my reading this is due to the fact that Oracle must identify all columns first with the select count(*) command, whereas select count(1) eliminates this step and returns the result more quickly.

Quote:

---

Originally posted by Newton
It is Proven fact that the Count(PK) is faster than Count(*) ... Not a big difference the reason is PK_Column hav the index and this was it retrieves the data faster.

---

Cite the study where this proof exists, if you would, because I've yet to see it. In all my testing, count(*), count(ROWID), count(1), count(col) are all absolutely equal.

And just to get on my ever-present soap-box for a moment: :)

I am absolutely amazed at 2 contradictory issues I have found to be true:

- Most people who write SQL and/or PL/SQL actually know very little about how to truly optimize them.

- Almost everybody has an opinion on how to write count(*)

Is anybody else struck by the irony here? Something as incredibly unimportant as how to write count(*) holds *everyone's* interest, yet I'd be willing to bet money that at least 75% of the projects out there do something like this with it:

SELECT COUNT(*) INTO l_Count FROM .......

IF ( l_Count > 0 ) THEN ....

I see this *all the time*! People do a count of every single record when all they really want to know is if one *exists*

The statement *should* be:

SELECT COUNT(*) INTO l_Exists FROM ... WHERE ...
AND ROWNUM = 1

or COUNT(1) or COUNT(ROWID) or whatever makes you happy. The addition of the ROWNUM predicate is going to make a difference. What is inside the COUNT() will not.

Rant over ( over 4 weeks and no cigarrettes - can you tell? ;) )

- Chris

BTW - another thought on the subject - I would personally not use ROWID. This would simply be in keeping with my position that ROWID should never appear in production application code.

Chris,

Try these 2 on a big table and you will see the difference :

Select count(*) from big_table;

select /*+ INDEX (BIG_TABLE BIG_TABLE_PK) */
count(BIG_TABLE_PK_COLUMN)
from BIG_TABLE;

I just ran it on a table and the difference was 39
seconds vs. 11 seconds. Repeat the test and it
drops to 39 seconds vs 1 second since the index
is in the buffer cache.

QED.

-John

ps. congrats on the cigarrettes! Hang in there!

Chris,
Then how does the count(*) differ from count(PK).
what does oracle do to count the number of records in the table.

Quote:

---

Originally posted by jdorlon
Select count(*) from big_table;

select /*+ INDEX (BIG_TABLE BIG_TABLE_PK) */
count(BIG_TABLE_PK_COLUMN)
from BIG_TABLE;

---

Sorry John, but you're mixing metaphors, as they say :)

Try:
select /*+ INDEX (BIG_TABLE BIG_TABLE_PK) */
count(BIG_TABLE_PK_COLUMN)
from BIG_TABLE;
against
select /*+ INDEX (BIG_TABLE BIG_TABLE_PK) */
count(*)
from BIG_TABLE;

to get a true test.

You should find that the times are equal. If you look in V$SQLArea, you should further find that all the stats are also equal.

Here are my tests and results: (all returned immediately - I'm at home and my largest table is only 300K :))

1. SELECT COUNT(*) FROM TIMECARD_T
Plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=1)
---SORT (AGGREGATE)
------INDEX (FAST FULL SCAN) OF TIMECARD_IF563_X (NON-UNIQUE) (Cost=103 Card=362202)
LRs: 709

2. SELECT COUNT(TIMECARD_ID) FROM TIMECARD_T
Plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=1)
---SORT (AGGREGATE)
------INDEX (FAST FULL SCAN) OF TIMECARD_IF563_X (NON-UNIQUE) (Cost=103 Card=362202)
LRs: 709

3. SELECT /*+ INDEX(TIMECARD_T TIMECARD_PK_X ) */ COUNT(*) FROM TIMECARD_T
SELECT STATEMENT Optimizer=CHOOSE (Cost=681 Card=1)
---SORT (AGGREGATE)
------INDEX (FULL SCAN) OF TIMECARD_PK_X (UNIQUE) (Cost=681 Card=362202)
LRs: 687

4. SELECT /*+ INDEX(TIMECARD_T TIMECARD_PK_X ) */ COUNT(TIMECARD_ID) FROM TIMECARD_T
SELECT STATEMENT Optimizer=CHOOSE (Cost=681 Card=1)
---SORT (AGGREGATE)
------INDEX (FULL SCAN) OF TIMECARD_PK_X (UNIQUE) (Cost=681 Card=362202)
LRs: 687

So, I stand behind my original contention - it doesn't matter what goes inside the COUNT(). The plan that the optimizer chooses, or the user forces, as *always*, makes a difference.

( And thanks for the support on the smokes :D )


As for badrinathn, I'm not really sure what you're asking. As I said, the two actually do *not* differ. As for how Oracle *actually*, internally counts them, I'm not sure of the exact mechanics.

For a full table, however, all it has to do is choose the smallest index on the table and count the leaf entries. This will differ based on the statement, however.

- Chris

Hi Chris,

Yeah I agree on you with what goes inside the (). I just wanted to make sure to point out that if you add the hint
you can really speed things up, since everybody is looking
for the fastest ways to count records but nobody mentioned
it. You'd think the optimizer would be smart enough to
figure out to use the index without a hint, but it (at least
in my experience) isn't.

-John

I was always under the impression that count(1/pk/rowid)was quicker then count(*) in tables that had a lot of deletes against them (since count(*) would read up to the HWM).

I've just tried this though (for the first time!), and it seems was I though has just been completely disproved (well on 7.3.4 anway).

With a newly populated 400,000 row table, I tried count(*), and count(1) - same results (as I would've expected before).

I then delete all the rows...
Exactly the same results as before (indicates that it's still being read to the HWM for count(anything)).

Create a primary key (there are no rows, but the table had the data deleted).
Ran again - same results * (Full table scan).

Analyze the table...
Count(*), count(1) etc. all now use the PK index which returns the result much quicker.

So, based on what I've just tried and what has been written above, it seems that count(*), count(1) etc. all perform the same when counting all rows in a table.

Terry

Quote:

---

Originally posted by jdorlon
I just wanted to make sure to point out that if you add the hint
you can really speed things up, since everybody is looking
for the fastest ways to count records but nobody mentioned
it. You'd think the optimizer would be smart enough to
figure out to use the index without a hint, but it (at least
in my experience) isn't.

---

Agreed. It is always worth looking at the plan, even on something as simple as SELECT COUNT. I've definitely noticed that the optimizer makes some interesting choices when dealing with COUNT(). I haven't been able to put my finger on the discriminating factor(s) yet. Sometimes it makes perfect choices and sometimes it misses the obvious index. Ah well... :)

- Chris