Re: Bad timing with nested sql select

In article <D8ru4E.DD1_at_mecati.mecasoft.ch>, Stefano UNTERNAEHRER <unter_at_mecati.mecasoft.ch> wrote:
>Hello folks!
>I have a really bad performance with a nested sql selection.
>The select is structurated like:
> SELECT ... /* 3th select */
> FROM ...
> WHERE ... IN
> SELECT ... /* 2nd select */
> FROM ...
> WHERE ... IN
> SELECT ... /* 1st select */
> FROM ...
> WHERE ...
>The complete nested selection require 37.2400 seconds.
>The 1st selection alone require: 1.0600 seconds,
>the 2nd, giving the IN values: 0.2000 seconds,
>and the 3th with IN values: 0.2400 seconds,
>with a total of supposed 1.5000 seconds,
>much better then 37 !!!!
 < snip > some stuff on Rollback segments deleted.
>______________________| Stefano Unternaehrer |__________________________
>\ Mecasoft SA | Oracle DBA | fax: +41 93 335 507 /
> \ 6600 Muralto | and PL/SQL, Pro*C | voice: +41 93 337 444 /
> \ | C, XWindow & Motif | /
> / | Software Developer | email: \
> / Switzerland Europe |____________________________| dba,unter_at_mecasoft.ch \
The explanation is very simple and it has nothing much to do with rollback segments. Whenever an Oracle SQL query executes the in statement, it compares for every value in the in statement. It must be observed that an IN statement can be expressed as a join conveniently in this case and since the join operation is usually optimized, it should give you very good results. IN should be only used when the number of entries in the IN is very small for example, select empno,empname,deptname from employee where deptno in (10,30) and desig='CLERK';
This will work fine because there are only two entries in the IN and the comparison is very fast.
Rule of thumb or Gotcha: Dont use IN for subselect as in the case. Use IN when there are a few ( very few ) values which have to be hardcoded. Try to join match tables as much as possible and avoid IN. Hope this helps.
Mahesh Vallampati

Home:				   Office:
PH : 409 846 3794 (H)		|  409 862 1070 (O)
e-mail address  : oracle7_at_tamu.edu
WWW 		: http://tam2000.tamu.edu/~m0v5533/
Lab 		: 409 847 8609