Re: This SQL dont use indexes

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

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!