Re: More Variations on NOT IN problem

From: Pedro Bandeira <pbandei>
Date: 1995/07/29
Message-ID: <3vefcr$m7_at_midgard.calvacom.fr>#1/1


<esanderl_at_explorer.csc.com> wrote:
>I have more variations on the use of NOT IN and NOT EXISTS;
>I ran both through the optimizer and got the same result;
>the first select is extrememly slow but returns the correct answer;
>the second runs fast but doesn't return any rows:
>
>(1):
>explain plan set statement_id = 'SELECT1'
>for
>select distinct b.crr_itn_plnd_glc_cd from carrier_itinerary b
> where not exists
> (select distinct a.op_rtg_glc_cd from oplan_force_rqmt_loc a
> where a.op_pln_id = '120AX' and
> a.op_rtg_glc_cd = b.crr_itn_plnd_glc_cd
> )
> order by 1;
>
>(2):
>explain plan set statement_id = 'SELECT2'
>for
>select distinct b.crr_itn_plnd_glc_cd from carrier_itinerary b
> where b.crr_itn_plnd_glc_cd NOT IN
> (select distinct a.op_rtg_glc_cd from oplan_force_rqmt_loc a
> where a.op_pln_id = '120AX'
> )
> order by 1;
>
>Both return the following from the optimizer:
>
>ID OPERATION OPTIONS OBJECT_NAME Parent_ID POSITION
>-- ------------------ -------- --------------------- ---- -----
>0 SELECT STATEMENT 11
>1 SORT UNIQUE 0 1
>2 FILTER 1 1
>3 TABLE ACCESS FULL CARRIER_ITINERARY 2 1
>4 TABLE ACCESS BY ROWID OPLAN_FORCE_RQMT_LOC 2 2
>5 INDEX RANGE SCAN pk_opfrq_loc 4 1
>
>6 rows selected.
>
>SQL>
>
>
>Any Ideas????

Which columns is pk_opfrq_loc indexing? Received on Sat Jul 29 1995 - 00:00:00 CEST

Original text of this message