Re: This SQL dont use indexes

From: Odd Morten Sveås <odd.morten.sveas_at_accenture.com>
Date: 12 Nov 2002 21:59:09 -0800
Message-ID: <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 Received on Wed Nov 13 2002 - 06:59:09 CET