Hi, the good message is that the sap db optimizer supports min/max queries on primary key fields and on index fields The bad one is that the explain doesn't show this special strategy. Table scan means here that no qualification is usable for table access. I will change the explain output in a future release.
For sum/avg no special strategy exists so the optimizer have to scan the whole table. For count(*) the optimizer should use the smallest index if one exists to determine the row count. In some older releases a bug prevent this strategy for count(*) but count(<index column>) should work. HTH, Holger SAP Labs Berlin > -----Original Message----- > From: Flemming Frandsen [mailto:[EMAIL PROTECTED]] > Sent: Dienstag, 12. Februar 2002 11:12 > To: [EMAIL PROTECTED] > Subject: Re: Strange strategy for max(key) > > > Raimund Jacob wrote: > > > > explain select max(changelog_id) from changelog > > > changelog_id is an int and the primary key of changelog, which has > > > 184551 rows. > > > > > > And the gist of the explaination is: > > > TABLE SCAN 828 > > > RESULT IS COPIED , COSTVALUE IS 1657 > > > > i made an observation like that when i had: > > - a primary key consisting of 2 FIXED(30,0): oid, sid > > - made a query where oid was a parameter (WHERE oid=?) > > - selected max(sid) > > > > the plan contained also an execution plan like yours (other > pagecountes, > > of course). i could reduce the cost a tiny _tiny_ bit by > creating a DESC > > index on sid. however, it still had a "table scan" in the > next step. i > > guess it creates some temp result table which it has to scan again. > > Hmm, my guess is that the max/min functions are not optimized > at all and > are handled in the same way as sum/avg, but I guess only the gurus at > SAP can answer that question. > > > > in the meantime: did somone send you a good explaination > off-list? did > > you find another way to work around? got any other hint? :-) > > Nope, not yet. > > I use the max(primary_key_col) stuff a lot in my application > and so far > the penalty for tablescanning 200000 doesn't hurt too badly, > but I fear > it will not scale very well. > > Another datapoint: > explain select count(changelog_id) from changelog > explain select count(*) from changelog > explain select max(changelog_id) from changelog > explain select sum(changelog_id) from changelog > > All give the same execution plan: TABLE SCAN > > Is it possible that the explaination is wrong? > > -- > Regards Flemming Frandsen aka. Dion/Swamp http://dion.swamp.dk > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
