| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Group functions and indexes
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C02174.644DD31E
Content-Type: text/plain;
charset="iso-8859-1"
If I have a query...
select max(salary) from employee
where emp_id = :emp_id;
The table has about 4 million rows; non-unique index on emp_id...
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...
Thanks in advance!
Val
Valerie H. Webber
Management Systems Designers, Inc
Database Administrator
Valerie.H.Webber_at_irs.gov
704-569-1002 x107
------_=_NextPart_001_01C02174.644DD31E
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>Group functions and indexes</TITLE>
</HEAD>
<BODY>
<P><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">If I have a =
query... </FONT>
</P>
<P><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">select =
max(salary) from employee</FONT>
<BR> <FONT COLOR=3D"#800000" =
FACE=3D"Bookman Old Style">where emp_id =3D :emp_id;</FONT>
</P>
<P><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">The table has =
about 4 million rows; non-unique index on emp_id...</FONT>
</P>
<P><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">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?</FONT></P>
<P><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">I have looked on =
MetaLink and TechNet to no avail...</FONT>
<BR><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">Thanks in =
advance!</FONT>
<BR><FONT COLOR=3D"#800000" FACE=3D"Bookman Old Style">Val</FONT>
<BR><B><FONT COLOR=3D"#000080" FACE=3D"Verdana">Valerie H. =
Webber</FONT><FONT COLOR=3D"#000000" FACE=3D"Verdana"></FONT></B><BR>
<FONT COLOR=3D"#000080" SIZE=3D1 FACE=3D"Comic Sans MS">Management =
Systems Designers, Inc</FONT>
<BR><FONT COLOR=3D"#000080" SIZE=3D1 FACE=3D"Comic Sans MS">Database =
Administrator</FONT><FONT COLOR=3D"#000000" FACE=3D"Arial"><BR>
</FONT><FONT COLOR=3D"#000080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Valerie.H.Webber_at_irs.gov</FONT><FONT COLOR=3D"#000000" =
FACE=3D"Arial"><BR>
</FONT><FONT COLOR=3D"#000080" SIZE=3D1 FACE=3D"Comic Sans =
MS">704-569-1002 x107</FONT><FONT COLOR=3D"#000000" FACE=3D"Arial"> =
Received on Mon Sep 18 2000 - 08:28:49 CDT
 |
 |