--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:

> >So I thought I would de-normalise the table structure and added a

new

> >filed and wrote this script.
> >
> >UPDATE ENROL E
> >SET REVENUE_NO = (
> > SELECT REVENUE_NO
> > FROM REVENUE_FACULTY RF, STUDENT S
> > WHERE S.STUD_NO = E.stud_no
> > AND RF.CRT_NO = E.crt_no
> > AND RF.OS = S.OS)
> >
> >There is only one rf.revenue_no for each rf.crt_no and rf.os
> >
> >The enrol table is one to many - although most enrol table entries
> >have only one associated student
> >
> >The enrol table has about 10,000 records, and student table about
> >7,000
>
> So do you have enrol records that have no associated student? Just

curious...

>
>
> >The plan only used natural order - and ignored the stud_no primary
> >key.
>
> This isn't surprising in a correlated sub-select. You would do a

much more more efficient job here with a stored procedure - one
result set for all of the updates instead of multiple result sets for
each single update.

Hello Helen!

Why? I can't understand this. There is a index an IB could use it
without problems. Couldn't we simply say, that IB is weak here or
the optimizer not smart enough? I cannot understand that always the
user hasn't been smart enough to do it right. I am a great
IB/Firebird fan too using it for a long time, but in the groups
dealing with it is always the user that made something wrong if IB
doesn't do a good job. And it is a very long thread, but not a word
that explains why IB could not use the index.

I give a late reply to this thread because I have a similar Update
clause where IB uses natural plans and takes about 45min with nearly
100% CPU. If I give a plan like:
update ADRESSEN A set ansprechpartner =
(select PERSONNR from Personen P where (P.Adressnr=A.Adressnr) and
(P.ANSPRECHPARTNER='T')
plan (P index (RDB$FOREIGN122))
)
where A.Ansprechpartner is null;

IB says ISC ERROR CODE:335544642

ISC ERROR MESSAGE:
index RDB$FOREIGN122 cannot be used in the specified plan

I have 2 or 3 other indices containing P.ADRESSNR, but IB accepts
none of them.
Of course I will try the SP, but is it really totally impossibe that
IB uses an index here?

Thank You Dieter Tremel

I Add DDL here:
CREATE TABLE ADRESSEN (
ADRESSNR INTEGER NOT NULL
, NAME1 VARCHAR( 39 ) NOT NULL
, NAME2 VARCHAR( 30 )
, ADRESSANREDE VARCHAR( 22 )
, ORTSTEIL VARCHAR( 30 )
, STRASSE1 VARCHAR( 30 )
, LAND VARCHAR( 4 )
, PLZ VARCHAR( 5 )
, ORT VARCHAR( 30 )
, POSTFACH VARCHAR( 30 )
, PLZ_POSTFACH VARCHAR( 5 )
, TELEFON1 VARCHAR( 24 )
, TELEFON2 VARCHAR( 24 )
, FAX VARCHAR( 24 )
, EMAIL VARCHAR( 80 )
, BRANCHE VARCHAR( 20 )
, EINZELPERSON CHAR( 1 ) /* Defaulted */
, ANSPRECHPARTNER INTEGER
, INSTITUTION CHAR( 1 ) /* Defaulted */
, KIRCHLICH CHAR( 1 ) /* Defaulted */
, PRIVAT_FUER VARCHAR( 31 )
, AUSGESCHIEDEN CHAR( 1 ) /* Defaulted */
, DEKANAT VARCHAR( 50 )
, EINKEHRTAG VARCHAR( 14 )
, PROSPEKTE SMALLINT
, KONTAKT_DURCH VARCHAR( 20 )
, KONTAKT_WANN DATE
, AKTION VARCHAR( 10 )
, BEMERKUNG BLOB( 65535, 1 )
, HERKUNFT VARCHAR( 5 )
, NUMMER_ALT INTEGER
, ERSTELLT_AM TIMESTAMP /* Defaulted */
, ERSTELLT_DURCH VARCHAR( 31 ) /* Defaulted */
, GEAENDERT_AM TIMESTAMP
, GEAENDERT_DURCH VARCHAR( 31 )
, HOMEPAGE VARCHAR( 80 )
, CONSTRAINT PK_ADRESSEN
PRIMARY KEY ( ADRESSNR )
)

ALTER TABLE ADRESSEN
ADD CONSTRAINT DEKANATREF
FOREIGN KEY ( DEKANAT )
REFERENCES DEKANATE ( DEKANAT )
ON UPDATE "CASCADE"
, ADD CONSTRAINT EINKEHRTAGREF
FOREIGN KEY ( EINKEHRTAG )
REFERENCES EINKEHRTAGE ( CODE )
ON UPDATE "CASCADE"
, ADD CONSTRAINT FK_ADR_ANREDEPERS
FOREIGN KEY ( ADRESSANREDE )
REFERENCES ANREDEN_ADRESSEN ( ANREDE )
ON UPDATE "CASCADE"

CREATE TABLE PERSONEN (
PERSONNR INTEGER NOT NULL
, ADRESSNR INTEGER NOT NULL
, NACHNAME VARCHAR( 24 )
, VORNAME VARCHAR( 20 )
, ANREDE VARCHAR( 22 )
, TITEL VARCHAR( 22 )
, NAMENSZUSATZ VARCHAR( 22 )
, ANSPRECHPARTNER CHAR( 1 ) /* Defaulted */
, STAND VARCHAR( 22 )
, KIRCHLICH CHAR( 1 ) /* Defaulted */
, ABTEILUNG VARCHAR( 50 )
, FUNKTION VARCHAR( 50 )
, TELEFON1 VARCHAR( 24 )
, TELEFON2 VARCHAR( 24 )
, FAX VARCHAR( 24 )
, EMAIL VARCHAR( 80 )
, NAMENSTAG VARCHAR( 5 )
, GEBURTSTAG DATE
, VERSTORBEN CHAR( 1 ) /* Defaulted */
, AUSSCHEIDETAG DATE
, AUSGESCHIEDEN CHAR( 1 ) /* Defaulted */
, BEMERKUNG BLOB( 65535, 1 )
, ERSTELLT_AM TIMESTAMP /* Defaulted */
, ERSTELLT_DURCH VARCHAR( 31 ) /* Defaulted */
, GEAENDERT_AM TIMESTAMP
, GEAENDERT_DURCH VARCHAR( 31 )
, NTAGBRIEF CHAR( 1 ) /* Defaulted */
, GTAGTAG SMALLINT
, GTAGMONAT SMALLINT
, GTAGJAHR SMALLINT
, CONSTRAINT PK_PERSONEN
PRIMARY KEY ( PERSONNR )
)

ALTER TABLE PERSONEN
ADD CONSTRAINT FKEY_PERS_ADR
FOREIGN KEY ( ADRESSNR )
REFERENCES ADRESSEN ( ADRESSNR )
ON DELETE "CASCADE"
, ADD CONSTRAINT FKEY_PERS_ANREDEN
FOREIGN KEY ( ANREDE )
REFERENCES ANREDEN_PERSONEN ( ANREDE )
, ADD CONSTRAINT FKEY_PERS_TITEL
FOREIGN KEY ( TITEL )
REFERENCES PERSONENTITEL ( TITEL )
, ADD CONSTRAINT FK_PERSON_NTAG
FOREIGN KEY ( NAMENSTAG )
REFERENCES NAMENSTAGE ( NAMENSTAG )
, ADD CONSTRAINT FK_PERS_ANREDEPERS
FOREIGN KEY ( ANREDE )
REFERENCES ANREDEN_PERSONEN ( ANREDE )
ON UPDATE "CASCADE"

CREATE INDEX PERS_ADRESSNR_NAMEX
ON PERSONEN(ADRESSNR, NACHNAME, VORNAME)^
CREATE INDEX PERS_ANSPRECHPARTNERX
ON PERSONEN(ADRESSNR, ANSPRECHPARTNER)^
CREATE INDEX PERS_GEBURTSTAGX
ON PERSONEN(GEBURTSTAG)^
CREATE INDEX PERS_NACHNAMEX
ON PERSONEN(NACHNAME, VORNAME)^
CREATE INDEX PERS_NAMENSTAGX
ON PERSONEN(NAMENSTAG)^