THINK IN 'SETS'

Before posting technical stuff in coming days, I want to mention couple of basic points here .
See below -Tomkyte's Simple Mantra .

You should do it in a single SQL statement if at all possible. And believe it or not, it is almost always possible.
If you can’t do it in a single SQL Statement, do it in PL/SQL—as little PL/SQL as possible! Follow the saying that goes "more code = more bugs, less code = less bugs."
If you can’t do it in PL/SQL, try a Java stored procedure. The times this is necessary are extremely rare nowadays with Oracle9i and above.
If you can’t do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed or using a third-party API written in C is needed.
If you can’t do it in a C external routine, you might want to seriously think about why it is you need to do it. 

I feel this is something important specially for a developer .When talking about sql and pl/sql - the point here is to avoid doing something *procedurally* in pl/sql if that can be done in a single SQL statement .
I see many times similar type of the below pseudocode:

For each x in Cursor_C
loop
Select count(*) into v_count  from t2 where colm1=x
If v_count >0 then do something .
End loop;

Look at the above code -Firstly - LOOP - this is something to be avoided if it can be ……….because this causes the 'context switches' between SQL engine and Pl/SQL engine . The switch may consume negligible time (approx -0.0001sec) but just assume if the loop repeats 1 million times …definitely the time taken for switches matters. In most of the cases -it is possible to avoid the loops- if we think the results in 'sets' .Secondly -the developer wanted to check the *existence* for a row where colm1=x , he should not have used here count(*).Count(*) is to count the no of rows ,might not be used to check for the existence of a row and moreover the optimizer in this case may need to full scan the table /index to get the count .But in this example -if we use 'exists' operator - it will nicely check for the existence of the row by using a smart technique called- 'semi join' with less effort .This type of statements could be re-written using - EXISTS /IN , MERGE etc depends on the requirement in a single SQL statement in most of the cases .

We can really do magic with SQL if we think the results in 'SETS'...
SO -
THINK IN 'SETS' …………………………..
THINK IN 'SETS '
..
THINK IN 'SETS '
and Avoid procedural logic if it can be done in SQL …………..

If you finally have come to conclusion that something can not be done in SQL - yes , Undoubtedly this is the time to go for PL/SQL but again - here also think in smart way .For example : when I want to retrieve a single row - why should I use explicit cursor ? I use sweet implicit cursor because I feel Oracle introduced implicit cursor just to avoid explicit cursor which may need more code (and which may probably introduce bugs sometimes) and to give some relief to developers when the developer wants to retrieve a single row.
Can I say something here - actually there is no concept of 'implicit' or 'explicit' in oracle database engine point of view, every SQL is an explicit for Oracle database engine. This implicit cursor concept is a pl/sql programming feature -this is a smart technique given to the developers by Pl/sql just to avoid more coding (explicit cursors) when the developer wants to retrieve a single row - once we submit our cursor to pl/sql (implicit or explicit) - every thing will be converted to explicit and sent to Oracle database engine.


Efficient coding depends on how we understand the programming constructs/features and tools and where and when we have to use them ..There is no one for all rule and most of the times it 'depends'... …………………..
I will be discussing some real technical stuff in coming days .