Hi,

Is there any way to speed up this query:

SELECT DISTINCT kiosk_id FROM Metrics WHERE ExecTime IS NOT NULL AND
Copies IS NOT NULL

The Metrics table has about 500,000 records in it, and an index
which includes the kiosk_id, ExecTime and Copies. I would have
expected it to use this, but the plan that the Analyzer shows is a
sort:

PLAN SORT ((METRICS NATURAL))

The full definition of the table and indexes is below.

Many thanks,

David

CREATE TABLE metrics (Kiosk_Id INTEGER, Form_Id INTEGER, Copies
INTEGER, Pgs INTEGER, Script_Id INTEGER, ExecTime TIMESTAMP, Id
INTEGER NOT NULL, Primary Key(Id));

CREATE GENERATOR GEN_METRICS_ID;

SET TERM ^ ;

CREATE TRIGGER METRICS_BI FOR METRICS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_METRICS_ID, 1);
END ^
commit ^
SET TERM ; ^


CREATE INDEX m_kioskId_formId_exectime ON metrics (Kiosk_Id,
Form_Id, ExecTime);

CREATE INDEX m_kioskId_exectime_copies ON metrics (Kiosk_Id,
ExecTime, Copies);

CREATE INDEX m_formId_exectime_copies ON metrics (Form_Id, ExecTime,
Copies);

CREATE INDEX m_scriptId_exectime_copies ON metrics (Script_Id,
ExecTime, Copies);