Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


16 - Use Indexes for Max() and Min()

The Max() and Min() aggregate functions may run faster if there is an index on the column. The example in Figure 16 shows that even though the estimated I/O for the second SELECT is hugely wrong, the PLAN says it all: An index is used instead of a sequential table scan and the actual I/O and time figures are much lower.

Figure 16 - An Index Helps Max() 
CREATE TABLE query1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     search2 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX iquery2 ON query1 ( search2 );


SELECT Max ( search1 ) FROM query1;

   PLAN> query1 (seq)

   Estimated - rows 1, I/O 626

   Actual    - rows 1, I/O 347, time 1.91 seconds


SELECT Max ( search2 ) FROM query1;

   PLAN> query1 (iquery2)

   Estimated - rows 1, I/O 7222

   Actual    - rows 1, I/O 6, time 0.10 seconds

Some restrictions may apply to the use of indexes with Max() and Min(). Try putting the function call all by itself in a separate SELECT rather than combining it with other expressions. The presence of a GROUP BY or WHERE clause may also inhibit optimization.

[Home] [Table of Contents] [Previous Section] [Next Section]