Re: Group functions and indexes

From: Allan Nelson <anelson_at_houston.rr.com>
Date: Mon, 18 Sep 2000 22:22:33 -0500
Message-Id: <10623.117311@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0057_01C021BE.F0DEA5C0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Group functions and indexesThe where clause will determine if the index = will be used or not. I'm not sure what version of the rdbms you are = using and what your optimizer mode is. If the rule based optimizer is = in effect then the index should be used. In most organizations your = concern over the non-uniqueness of the index would be fully justified = :-). If the optimizer is in choose mode then statistics might affect a = more complex query, but in this case the index will probably still be = the least costly method of executing the query. Salary would serve no = purpose in the index as it does not serve in the query's where clause.

Allan

Original Message -----=20 From: Webber Valerie H=20 To: Multiple recipients of list ORACLE-L=20 Sent: Monday, September 18, 2000 9:32 AM Subject: Group functions and indexes

If I have a query...=20

select max(salary) from employee=20

where emp_id =3D :emp_id;=20

The table has about 4 million rows; non-unique index on emp_id...=20

What is going on internally? What effect does the group function MAX = have on the search? What about the non-unique index on emp_id? The index = is being used but I'm concerned about its uniqueness (or lack thereof.) = Should SALARY be included in the index even though it isn't a predicate = in the where clause?

  I have looked on MetaLink and TechNet to no avail...=20   Thanks in advance!=20
  Val=20
  Valerie H. Webber
  Management Systems Designers, Inc=20
  Database Administrator
  Valerie.H.Webber_at_irs.gov
  704-569-1002 x107=20

------=_NextPart_000_0057_01C021BE.F0DEA5C0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Group functions and indexes</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>The where clause will determine if the =

index will=20
be used or not.  I'm not sure what version of the rdbms you are = using and=20
what your optimizer mode is.  If the rule based optimizer is in = effect then=20
the index should be used.  In most organizations your concern over = the=20
non-uniqueness of the index would be fully justified :-).  If the = optimizer=20
is in choose mode then statistics might affect a more complex query, but = in this=20
case the index will probably still be the least costly method of = executing the=20
query.  Salary would serve no purpose in the index as it does not = serve in=20
the query's where clause.</FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Allan</FONT></DIV>
<BLOCKQUOTE=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20

  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20
<A title=3DValerie.H.Webber_at_irs.gov=20
  href=3D"mailto:Valerie.H.Webber_at_irs.gov">Webber Valerie H</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Monday, September 18, =
2000 9:32=20
  AM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Group functions and=20
indexes</DIV>
<DIV><BR></DIV>
<P><FONT face=3D"Bookman Old Style" color=3D#800000>If I have a =
query...=20
</FONT></P>
<P><FONT face=3D"Bookman Old Style" color=3D#800000>select max(salary) =
from=20
  employee</FONT> <BR>        <FONT=20   face=3D"Bookman Old Style" color=3D#800000>where emp_id =3D = :emp_id;</FONT> </P>
<P><FONT face=3D"Bookman Old Style" color=3D#800000>The table has =
about 4 million=20
  rows; non-unique index on emp_id...</FONT> </P>
<P><FONT face=3D"Bookman Old Style" color=3D#800000>What is going on =
internally?=20
  What effect does the group function MAX have on the search? What about = the=20
  non-unique index on emp_id? The index is being used but I'm concerned = about=20
  its uniqueness (or lack thereof.) Should SALARY be included in the = index even=20
  though it isn't a predicate in the where clause?</FONT></P>
<P><FONT face=3D"Bookman Old Style" color=3D#800000>I have looked on =
MetaLink and=20
  TechNet to no avail...</FONT> <BR><FONT face=3D"Bookman Old Style"=20   color=3D#800000>Thanks in advance!</FONT> <BR><FONT face=3D"Bookman = Old Style"=20
  color=3D#800000>Val</FONT> <BR><B><FONT face=3DVerdana = color=3D#000080>Valerie H.=20
  Webber</FONT><FONT face=3DVerdana color=3D#000000></FONT></B><BR><FONT =

  face=3D"Comic Sans MS" color=3D#000080 size=3D1>Management Systems = Designers,=20
  Inc</FONT> <BR><FONT face=3D"Comic Sans MS" color=3D#000080 = size=3D1>Database=20
  Administrator</FONT><FONT face=3DArial = color=3D#000000><BR></FONT><FONT=20
  face=3D"Comic Sans MS" color=3D#000080 = size=3D1>Valerie.H.Webber_at_irs.gov</FONT><FONT=20   face=3DArial color=3D#000000><BR></FONT><FONT face=3D"Comic Sans MS" = color=3D#000080=20
  size=3D1>704-569-1002 x107</FONT><FONT face=3DArial color=3D#000000>=20 Received on Mon Sep 18 2000 - 22:22:33 CDT