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.0211122159.437b77d5_at_posting.google.com>...
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0211121320.3f8eb634_at_posting.google.com>...
>
> > 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 --
>
>
> You are of cource rigth, I use a group by. If not I will not be able
> to run the query at all. The example I used was made up du to security
> reasons, but here are the real query.
>
> SELECT
> l.CODE_COMBINATION_ID,
> l.TAX_CODE,
> l.EFFECTIVE_DATE,
> sum(nvl(l.ACCOUNTED_DR, 0)) - sum(nvl(l.ACCOUNTED_CR, 0)) ,
> h.DOC_SEQUENCE_VALUE
> FROM
> gl.GL_JE_LINES l ,
> gl.GL_JE_HEADERS h
> WHERE
> h.JE_HEADER_ID = l.JE_HEADER_ID
> and h.JE_SOURCE not in ('Payables','Receivables')
> group by
> l.CODE_COMBINATION_ID,
> l.TAX_CODE,
> l.EFFECTIVE_DATE,
> h.DOC_SEQUENCE_VALUE
>
> and it gives this explain plan:
> Operation Object Name Rows Bytes Cost
> SELECT STATEMENT Hint=CHOOSE 8 M 247402
> SORT GROUP BY 8 M 408 M 247402
> HASH JOIN 8 M 408 M 96082
> TABLE ACCESS FULL GL_JE_HEADERS 27 K 504 K 134
> TABLE ACCESS FULL GL_JE_LINES 8 M 262 M 30474
>
>
> h.JE_HEADER_ID is primary key
> and l.JE_HEADER_ID foreign key
>
>
> this index exists on the GL_JE_LINES table.
>
> CREATE UNIQUE INDEX GL.GL_JE_LINES_U1
> ON GL.GL_JE_LINES(JE_HEADER_ID, JE_LINE_NUM)
>
> It dos not mather even if I hint, it still uses table acces full
>
> So if the hash join is the problem here, how do I get rid of it?
>
> Thanx
> Odd Morten

Place the tables in the where clause in the order you think the query should drive in and use the ORDERED hint. You may also need a USE_NL hint to get the CBO to change from a hash join to a nested loops join, but are you sure that the hash join isn't the best way to do this query?

If the clause "and h.JE_SOURCE not in ('Payables','Receivables')" is selective and reduces the number of header table rows that you will process then I would think you want to drive on it. This is especially true if it greatly reduces the number of rows and this was the 500,000 row table and not the 4 million row table.

HTH -- Mark D Powell -- Received on Thu Nov 14 2002 - 01:32:59 CET