home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.ingres
- Path: sparky!uunet!usc!elroy.jpl.nasa.gov!sdd.hp.com!caen!uwm.edu!linac!uchinews!gsbacd.uchicago.edu!cs_mj
- From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
- Subject: Re: Finding maximum value of a column ?
- Message-ID: <1992Dec11.143126.1@gsbacd.uchicago.edu>
- Lines: 41
- Sender: news@uchinews.uchicago.edu (News System)
- Organization:
- References: <1992Dec3.123143.51@ittpub.nl> <1992Dec8.013622.1@gsbacd.uchicago.edu> <1797@niktow.canisius.edu>
- Date: Fri, 11 Dec 1992 20:31:26 GMT
-
- In article <1797@niktow.canisius.edu>, pavlov@niktow.canisius.edu
- (Greg Pavlov) writes:
- > In article <1992Dec8.013622.1@gsbvax.uchicago.edu>,
- > cs_mj@gsbvax.uchicago.edu (Mark Jaeger) writes:
- >>
- >> Right, it's slow. If you look at the QEP, you will see that INGRES uses
- >> a secondary index when such use would reduce the i/o's, but even so it
- >> always has to do a full scan of either the table or the secondary index
- >> in order to find the max.
- >>
- > I do not remember all of the details, but I believe that a key criteria was
- > that if the optimizer believed that more than 1/4 of the total rows needed
- > to be looked at, the base table would be traversed rather than the index
- > first.
-
- I don't know all of the details either, but I do know that I've seen the
- optimizer behave as I stated. I discovered it while trying to diagnose
- some index corruption problems. I did this:
-
- select count(tid) from base_table
- union select count(tidp) from index_table
- union select count(i.tidp) from base_table t , index_table ix
- where t.tid = ix.tidp
-
- as suggested by the release notes for some version of 6.3/03. When I
- examined the QEP for this, I discovered to my dismay that INGRES
- executed the first query by counting the rows in the smallest secondary
- index on the table, rather than going to the base table! Needless to
- say, we had to find another way of diagnosing the corruption problem.
-
- Your situation may be different.
-
- I'd also like to say that the INGRES optimizer is really good, and
- everything I've heard puts it really ahead of the crowd in this field.
-
- --Mark Jaeger internet: cs_mj@gsbvax.uchicago.edu
- Graduate School of Business yellnet: (312) 702-0328
- University of Chicago faxnet: (312) 702-0233
- Disclaimer: My opinions are my own and not those of my employer.
- Ich bin ein Virus. Mach' mit und kopiere mich in Deine .signature.
-
-