http://www.ibphoenix.com/downloads/60LangRef.zip

Page 139 - 140

But you didn't hear that from me. I personally would be looking for
some other option rather than overriding the optimisers plan, however
it can be done.

Adam





--- In firebird-support@yahoogroups.com, Kevin Herrmann
<kevinherrmann@y...> wrote:

> Thank you for the reply. I searched a lot of groups and sites

looking for

> the way to specify a plan and had no luck. A hint in the right

direction?

>
> Kevin Herrmann
>
> ...... Original Message .......
> On 4 Feb 2005 01:51:21 -0000 firebird-support@yahoogroups.com wrote:
> >It is using the doc_type index to speed up the "where" clause. You
> >can instruct FB on which plan to use by including it before
> >the "order by" if you think that is a good idea.
> >
> >Adam
> >
> >
> >--- In firebird-support@yahoogroups.com, "Kevin Herrmann"
> ><kevinherrmann@y...> wrote:
> >>
> >> This query is horribly slow on Firebird 1.0.3 UNTIL I remove the
> >> 'order by'. I think I've included all things I need. Here is the
> >> relevant Info:
> >>
> >> select d.*, e.NAME, e.GIVENNAME, a.EVENTTYPE from documents d
> >> left join entity e on d.subject_ID=e.id
> >> left join auditmessage a on d.id = a.id1 and a.eventtype = 19
> >> where d.doc_type in (5,6,7)
> >> and e.status=0
> >> and e.name <> 'MACRO'
> >> and e.AttendingID=2
> >> and a.ID1 is null
> >> order by d.doc_category;
> >>
> >> There is an index on doc_category, but the optimizer doesn't

want to

> >> use it. Here is the Plan:
> >>
> >> PLAN SORT (JOIN (JOIN (D INDEX
> >> (DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
> >> (RDB$PRIMARY8)),A INDEX
> >(AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))
> >>
> >> Adapted Plan
> >> PLAN SORT (JOIN (JOIN (D INDEX
> >> (DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
> >> (INTEG_36)),A INDEX

(AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))

> >>
> >>
> >> Here is the DDL:
> >>
> >> CREATE TABLE DOCUMENTS (
> >> ID INTEGER NOT NULL,
> >> DOC_TYPE SMALLINT NOT NULL,
> >> SUBJECT_ID INTEGER,
> >> SESSION_ID INTEGER,
> >> CREATED DATE NOT NULL,
> >> METADOCUMENT_ID INTEGER,
> >> ATTRIBUTES INTEGER,
> >> DESCRIPTION VARCHAR(250),
> >> DOC_CATEGORY SMALLINT
> >> );
> >> ALTER TABLE DOCUMENTS ADD PRIMARY KEY (ID);
> >> CREATE INDEX DOCUMENTS_CAT_X ON DOCUMENTS (DOC_CATEGORY);
> >> CREATE INDEX DOCUMENTS_SID_X ON DOCUMENTS (SUBJECT_ID);
> >> CREATE INDEX DOCUMENTS_TYP_X ON DOCUMENTS (DOC_TYPE);
> >>
> >> CREATE TABLE ENTITY (
> >> ID ENIGMA_ID /* INTEGER NOT NULL */ NOT
> >NULL,
> >> ENTITYTYPE SMALLINT NOT NULL,
> >> NAME VARCHAR(48) NOT NULL,
> >> ROLE_IDS VARCHAR(64),
> >> BIRTH DATE,
> >> MRNUM VARCHAR(16),
> >> SS CHAR(9),
> >> SEX CHAR(1),
> >> RACE VARCHAR(16),
> >> STATUS INTEGER,
> >> PAGER VARCHAR(20),
> >> EMAIL VARCHAR(48),
> >> TITLE VARCHAR(48),
> >> DEPARTMENT VARCHAR(48),
> >> COMPANY VARCHAR(48),
> >> ISPATIENT CHAR(1),
> >> ISEMPLOYEE CHAR(1),
> >> ISPHYSICIAN CHAR(1),
> >> PHOTO BLOB SUB_TYPE -3 SEGMENT SIZE 1024,
> >> HOME_ID ENIGMA_ID /* INTEGER NOT NULL */,
> >> WORK_ID ENIGMA_ID /* INTEGER NOT NULL */,
> >> DEA VARCHAR(20),
> >> AMA VARCHAR(20),
> >> PREFIX SMALLINT,
> >> SUFFIX CHAR(16),
> >> IVERSION SMALLINT NOT NULL,
> >> IOBSOLETE CHAR(1),
> >> CLASSSIGNATURE VARCHAR(64),
> >> PHYSICIANS VARCHAR(128),
> >> USEROID INTEGER,
> >> ATTENDINGID INTEGER,
> >> MIDDLE VARCHAR(16),
> >> OTHERMRNUMS VARCHAR(40),
> >> NOTEBOOK VARCHAR(2048),
> >> PHARMACY_NAME VARCHAR(64),
> >> PHARMACY_PHONE CHAR(20),
> >> PHARMACY_FAX CHAR(20),
> >> USER_SETTINGS BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> >> ISGUARANTOR CHAR(1),
> >> GUARANTORID INTEGER,
> >> GUARANTORRELATIONSHIP CHAR(32),
> >> INSURANCEID_1 INTEGER,
> >> INSURANCEID_2 INTEGER,
> >> INSURANCEID_3 INTEGER,
> >> SIGNATURE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> >> GIVENNAME VARCHAR(64)
> >> );
> >> ALTER TABLE ENTITY ADD PRIMARY KEY (ID);
> >> CREATE INDEX ENTITY_ATTENDING ON ENTITY (ATTENDINGID);
> >> CREATE INDEX ENTITY_GIVENNAME ON ENTITY (GIVENNAME);
> >> CREATE INDEX ENTITY_NAME ON ENTITY (NAME);
> >> CREATE INDEX ENTITY_ROLE ON ENTITY (ISPATIENT, ISEMPLOYEE,
> >ISPHYSICIAN);
> >> CREATE INDEX ENTITY_STATUS ON ENTITY (STATUS);
> >> CREATE INDEX ENTITY_USEROID ON ENTITY (USEROID);
> >>
> >> CREATE TABLE AUDITMESSAGE (
> >> EVENTTYPE SMALLINT,
> >> EVENTDATE DATE,
> >> USERNAME CHAR(16),
> >> ID1 INTEGER,
> >> ID2 INTEGER,
> >> AUDITTEXT VARCHAR(255)
> >> );
> >> CREATE INDEX AUDITMESSAGE_EVENTDATE2_X ON AUDITMESSAGE

(EVENTDATE);

> >> CREATE DESCENDING INDEX AUDITMESSAGE_EVENTDATE_X ON AUDITMESSAGE
> >> (EVENTDATE);
> >> CREATE INDEX AUDITMESSAGE_EVENTTYPE_X ON AUDITMESSAGE

(EVENTTYPE);

> >> CREATE INDEX AUDITMESSAGE_ID1_X ON AUDITMESSAGE (ID1);
> >> CREATE INDEX AUDITMESSAGE_USERNAME_X ON AUDITMESSAGE (USERNAME);
> >
> >
> >
> >
> >
>
>_____________________________________________________________________

___

> >___________