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