Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Group functions and indexes

RE: Group functions and indexes

From: Toepke, Kevin M <ktoepke_at_CMS.CENDANT.COM>
Date: Mon, 18 Sep 2000 10:15:21 -0400
Message-Id: <10623.117251@fatcity.com>


Internally, this query would be:

    1 Index-Range Scan on the Emp_ID index     2 Table Access by RowID on EMP
    3 Sort Aggregate  

Adding the SALARY column to the Emp_ID index would speed up the query by elimiating the table access.  

Kevin

-----Original Message-----
From: Webber Valerie H [mailto:Valerie.H.Webber_at_irs.gov] Sent: Monday, September 18, 2000 10:32 AM To: Multiple recipients of list ORACLE-L Subject: Group functions and indexes

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 Received on Mon Sep 18 2000 - 09:15:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US