Is select count(1) from aTable faster or more resource efficient than Select count(*) from aTable?
I've heard that select count(1) does not actually read records into memory but simply detects the fact that the records exist, whereas count(*) forces Oracle to read every record in its entirety.
What's your take on this?
Answer:
Rick, feeling and findings are that...
1. Whatever path you take, hide the query behind a function that returns your value. You can then adjust your implementation whenever you like or need to, taking advantage of any new optimizations or features from Oracle.
2. It doesn't really matter which approach you take, at least in Oracle Database 10g.
Click here to check out a script I wrote to compare the performance of each implementation. You will need to have the timer object installed to run the test.
Here are the results I got running my test:
Timings in seconds for "COUNT(1)":
Elapsed = 6.09 - per rep .00609
CPU = 5.9 - per rep .0059
Timings in seconds for "COUNT(*)":
Elapsed = 6.06 - per rep .00606
CPU = 5.8 - per rep .0058
BUT HOW
ReplyDelete