count(*) vs count(pk_col)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: count(*) vs count(pk_col)

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    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

  2. #2
    Join Date
    May 2001
    Posts
    19
    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

  3. #3
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    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!

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Chris,
    Then how does the count(*) differ from count(PK).
    what does oracle do to count the number of records in the table.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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 )


    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

  8. #8
    Join Date
    Nov 2000
    Posts
    344
    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

  9. #9
    Join Date
    Sep 2000
    Posts
    128
    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

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width