Re: This SQL dont use indexes

From: Ethel Aardvark <bigjobbies_at_hotmail.com>
Date: 19 Dec 2002 04:44:11 -0800
Message-ID: <1a8fec49.0212190444.72b17783_at_posting.google.com>


You could try changing
"and h.JE_SOURCE not in ('Payables','Receivables')" to
"and h.JE_SOURCE != 'Payables' and h.JE_SOURCE != 'Receivables'".

It should not make any difference in theorey but I have seen the optimiser behave differently in some cases.

ETA Tim X <timx_at_spamto.devnul.com> wrote in message news:<878yytw7zb.fsf_at_tiger.rapttech.com.au>...
> odd.morten.sveas_at_accenture.com (Odd Morten SveƄs) writes:
>
> > Hi
> >
> > I have a problem here.
> >
> > The following SQL woun't use indexes whatever I do.
> >
> > select
> > a.trx_line_id
> > , a.description
> > , sum(a.amount)
> > , b.date
> > from
> > mother_table b
> > , child_table a
> > where
> > b.trx_id = a.trx_id;
> >
> > The trx_id is primary_key in mother_table and the first column in a 3
> > column index for the child table.
> >
> > The child_table contains abount 4 mil rows, and the mother_table
> > abount 500 000.
> >
> > When I do an explain_plan bount the mother- and the child_table are
> > accesed by full table scan.
> >
> > What do I do wrong?
>
> You forgot to tell us what version of oracle you are using for a
> start. Also, it would help if you told us what optimization the
> database is useing (rule, choose, statistics). Without this
> information, there are too many variables to even make a guess.
>
> Tim
Received on Thu Dec 19 2002 - 13:44:11 CET

Original text of this message