Re: Smart indexes

Try creating two indices. One on the columns in the ON clause (on both
tables) and one on o.Pot. Then look at the plan and see how the indices
are being used.

-- 

IB 7.5.1

How to make smart indexes for this query?
My problem is that I whant good a index for the join itself and the criteria 
at the same time.

Mikael


Select
    Pot, Env, SessionID, LogId, SenderN, ReceiverN, Doctype
From
    Transferlog o
        inner join Documents d on (o.SessionID = d.SessionID and o.Env = 
d.Env and o.LogID = d.LogID)
Where
            o.Pot between '2008-01-08 09:02:48' and '2008-01-08 10:59:59'
     and o.Env = 'xib'
     and o.senderpi = 7
     and d.Docnr = '7815314'
     and d.Area = 'CIRCLE'
order by 1 Descending, 2 Descending, 3 Descending

CREATE TABLE DOCUMENTS (

    DOCIDENT    INTEGER NOT NULL,
    SESSIONID   INTEGER NOT NULL,
    ENV               VARCHAR(12) DEFAULT 'INIT' NOT NULL,
    LOGID           VARCHAR(20) NOT NULL,
    DOCNR         VARCHAR(35) NOT NULL,
    MSGFUNC    VARCHAR(35)
);

ALTER TABLE DOCUMENTS ADD CONSTRAINT PKDOC PRIMARY KEY (DOCIDENT);



CREATE TABLE TRANSFERLOG (
    LOGID                                          VARCHAR(20) NOT NULL,
    "EVENT"                                       SMALLINT NOT NULL,
    STATUS                                       SMALLINT NOT NULL,
    RESENT                                       CHAR(1) NOT NULL,
    STATUSDESCRIPTION             VARCHAR(150) NOT NULL,
    POT                                               TIMESTAMP NOT NULL,
    SESSIONID                                   INTEGER NOT NULL,
    ENV                                               VARCHAR(12) DEFAULT 
'INIT' NOT NULL,
    SENDERID                                    VARCHAR(35),
    SENDERN                                    VARCHAR(73),
    SENDERPI                                    INTEGER,
    RECEIVERID                                VARCHAR(35),
    RECEIVERN                                 VARCHAR(73),
    SENDERPI                                    INTEGER,
    ROWID                                         INTEGER NOT NULL
);

ALTER TABLE TRANSFERLOG ADD CONSTRAINT PKTL PRIMARY KEY (ROWID);