I'm working on optimizing a number of views and procedures that retrieve
work tickets based on a number of qualification criteria. One of the
relevant tables looks something like this:


CREATE TABLE ACTION_TICKETS (
TICKET_ID id PRIMARY KEY,
QUEUE_ID id,
JOB_ID id,
ACTION_ID id,
STATUS str256 DEFAULT 'Pending',
WEIGHT int default 250,
FOREIGN KEY (QUEUE_ID) REFERENCES QUEUES
ON DELETE CASCADE,
FOREIGN KEY (JOB_ID) REFERENCES JOBS
ON DELETE CASCADE,
FOREIGN KEY (ACTION_ID) REFERENCES ACTIONS
ON DELETE CASCADE
);

Where ID is a defined like so:

CREATE DOMAIN id AS BIGINT NOT NULL;

So I automatically end up with indexes on all of the ID type columns.
There are times where I'll need to query against the statuses of Tickets,
so I have those indexed. There are also times when I'll need to retrieve
jobs with the lowest priority, so I have that column indexed and a view
that sorts based on that column.

As I'm running my operational procedure, it might flow something like this:

1. Find first Ticket in 'Pending' Status.
2. Work with Ticket.
3. Set Ticket status to 'Complete'.
4. Repeat.

If I'm attached to my database in IBExpert, watching this take place, the
number of indexed read counts climbs for each iteration where I ask for the
first ticket where Status = 'Pending'. Is that normal / expected
behavior? Is there some way to create an intermediary view that would
reduce the number of reads necessary, or am incorrectly concerned about
normal behavior? Thanks in advance,

--Daniel


[Non-text portions of this message have been removed]