Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Group functions and indexes
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
![]() |
![]() |