Re: This SQL dont use indexes

odd.morten.sveas_at_accenture.com (=?ISO-8859-1?Q?Odd_Morten_Sve=E5s?=) wrote in message news:<4306a83.0211111341.59cb28d7_at_posting.google.com>...
> 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?

Your query appears slightly suspect to me as why would you be reporting columns for every child row and trying to sum the child rows at the same time? I would expect to see the sum of the child rows being returned for each parent row or perhaps a group by clause being present but based on what you have:

Does the explain plan show a hash join is being used to solve the problem, a sort/merge join, or a nested loops join? I would expect that the hash join is in use since the SQL you show has no limiting where clause condition as it consists on only a join condition. That could make a hash join the best plan.

Please post the explain plan and a description of what you are trying to get so you can get better assistance.

HTH -- Mark D Powell -- Received on Tue Nov 12 2002 - 22:20:39 CET