After reading a post here that enlightened me to the fact that
a SELECT COUNT(FIELD1) FROM TABLE;
decided of course to see if there was a performance difference
vs. SELECT COUNT(*) FROM TABLE WHERE FIELD1 IS NOT NULL;
Interestingly enough there can be.
Using a production table here with 4+ million rows. Table has
fresh statistics via DBMS_STATS, all rows.
#1:
select count(ZIP) from efpref_provider;
No NULL constraints exist on ZIP.
ZIP is a varchar.
A single column Index exists on ZIP.
38 records contain NULL ZIPs in the table.
EXPLAIN PLAN:
Operation Object Name Rows Bytes Cost Object
SELECT STATEMENT Optimizer Mode=CHOOSE
1 7839
SORT AGGREGATE 1 6
TABLE ACCESS FULL MPIEFP_DEV.EFPREF_PROVIDER
4 M 23 M 7839
#2:
select count(*) from efpref_provider where ZIP is not null;
EXPLAIN PLAN:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE
1 1259
SORT AGGREGATE 1 6
INDEX FAST FULL SCAN MPIEFP_DEV.EFREF$ZIP
4 M 23 M 1259
After multiple executions of both, #1 with the table scan
took between 9 and 12 seconds.
#2 with the Index FFS took between 2 and 4 seconds
Puzzling to me is that the FFS was available, I had thought a NO NULL constraint was a requirement to invoke
as FFS. Obviously time for me to hit the reference material
on this and the COUNT function.
Received identical results using another field with identical
traits (single column index, no constraints).
Regards,
Harry
] 
Blog
Search
Help
Members
Register
Login
Home
