Home » RDBMS Server » Performance Tuning » Performance Test: IS NOT NULL check (Oracle 9.2.0.3 Unix)
Performance Test: IS NOT NULL check [message #348749] Wed, 17 September 2008 18:48
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
After reading a post here that enlightened me to the fact that
a
SELECT COUNT(FIELD1) FROM TABLE;
excludes NULL values I
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


Previous Topic: Run time versus Elapsed time
Next Topic: Help in tuning query
Goto Forum:
  


Current Time: Tue Dec 06 13:51:31 CST 2016

Total time taken to generate the page: 0.12509 seconds