Count(1) Vs Count(*) Vs Count(experession) in Oracle.

1. Number of Records:

   The count(1)  and count(*)  returns same number of records, as its return the number rows in the table
   including the NULL records.
 
    While Count(Expression)  return the number of null records for which expression evalutes.
2. Performance Factor.
    There is no significance difference  in performance onwards release R8 oracle. 
  
  I will say don't invest too much time on this topics.

Source  doc Link
http://www.oracledba.co.uk/tips/count_speed.htm
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1156159920245

 

IN Vs EXIST and NOT IN VS NOT EXIST in oracle

1. IN condition Vs EXIST   condition
==========================

1.Use IN with list of sub-query (Where servey_date  in ('20015','2016'))
2. EXIST  looking for at least one row to return return true
3.If inner query has less records then;then use the IN
4. If inner query has more records then; the outer query then use EXIST.
 (Thumb rule to use the IN and EXIST)

IN query processed as
===============

Select * from T1 where x in ( select y from T2 )
is typically processed as:

select *
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

Exist Processed as folowing
===================

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

 - It always result into FULL_TABLE scan on the T1.

  -If your goal is the FIRST row exists might totally blow away IN this is 

the exception to thumb rule.

 Link to source page:  IN_VS_EXIST_ASK_TOM

NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER 

even -- "anti-join" can be used (if the subquery is known to not return nulls) 

Anti-Join fails when NULL;Therefore, a NOT IN operation would fail

 if the result set being probed returns a NULL. In such a case, 

the results of a NOT IN query is 0 rows  while a NOT EXISTS query

 would still show the rows present in the one table but not in the other table.