select count(*)
 as nCount from A , 
 	 B , 
	 C
WHERE A.COL1 = B.COL1 AND
 	A.COl2 <> 'COM' AND
 	B.COL2 = C.COL1 AND
 	B.COl3 IS NULL AND
 	B.COL4 = 'TEST'

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	51  	 	      	             	 
  SORT AGGREGATE		1  	37  	 	 	      	             	 
    HASH JOIN		48 K	1 M	51  	 	      	             	 
      TABLE ACCESS FULL	A 68 K	998 K	32  	 	      	             	 
      NESTED LOOPS		98 K	2 M	5  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	B	142 K	2 M	4  	 	      	             	 
          INDEX SKIP SCAN	XIF37B	142 K	 	6  	 	      	             	 
        INDEX UNIQUE SCAN	XPKC	1  	5