I try to solve problem, see
I am creating 2 table as below:
Id name Id name
1 a 1 a
2 b 2 b
3 c 3 c
upto 20000 records upto 10 records only
Then starts: "set timing on;"
I triggered first ( USING JOIN )query as :
Select t1.*,t2.name from t1,t2 where t1.id=t2.id;
This query takes 32.08 second to execute and showing joined records....
Then i triggered second ( USING DECODE ) query as :
select t1.*,decode(t2.id,2,'b',5,'e',10,'j')result from t1,t2 where t1.id=t2.id;
This query takes only 09.01 second to execute and showing records....
FROM MY CREATED SCANARIO IT IS CLEAR THAT DECODE IS MORE FASTER THEN JOIN.
THANKYOU FOR IMPROVING MY KNOWLEDGE, I NEVER THOUGHT LIKE THIS BEFOR.... ABOUT COMPARISION OF DECODE AND JOIN.
If you executed those SELECTs only once, results are most probably wrong. You should have ran both of them several times, discard the first execution time (because, subsequent ones most probably used cached data).
Besides, SET TIMING ON is not the most accurate way to do that. You should have created execution plan and compare these two plans. Additionally, see what SQL Trace says (i.e. view results of the TKPROF).
Furthermore, did you create any indexes on these tables? Did you collect statistics?
There's a LOT more stuff to do in order to make a conclusion. A single-run TIMING ON is far from that.