By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

db2 udb 8.2 ignores indexes

P: n/a
I've recently upgraded from 8.1.4 to 8.2.3. System slowed down
noticably. Explain shows that indexes are not used. I did reorgs on
indexes and tables, collected stats on key fields with detail sample
for all indexes with no improvement.

Any help would be greatly appretiated.

Thank you in advance.

Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
"romicva" <ro*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I've recently upgraded from 8.1.4 to 8.2.3. System slowed down
noticably. Explain shows that indexes are not used. I did reorgs on
indexes and tables, collected stats on key fields with detail sample
for all indexes with no improvement.

Any help would be greatly appretiated.

Thank you in advance.


I assume that you mean "some" indexes that were previously used are no
longer used. Can you post a detailed example?
Nov 12 '05 #2

P: n/a
I suspect we are only going to get attention on this if all of us with the
problem open PMRs until IBM finally accepts that they really do have a
problem. Our attempt failed, but volume may help.

"romicva" <ro*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I've recently upgraded from 8.1.4 to 8.2.3. System slowed down
noticably. Explain shows that indexes are not used. I did reorgs on
indexes and tables, collected stats on key fields with detail sample
for all indexes with no improvement.

Any help would be greatly appretiated.

Thank you in advance.

Nov 12 '05 #3

P: n/a
Hello Mark, what kind of information can I provide?
Thank you.
Roman.

Nov 12 '05 #4

P: n/a
Hello Mark Yudkin,

I believe I saw your post on this subject, where you said that
"...feedling with indexes helped...". Any chance I can do the same?

Thank you.
Roman.

Nov 12 '05 #5

P: n/a
"romicva" <ro*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hello Mark, what kind of information can I provide?
Thank you.
Roman.

Need the table and index DDL, SQL statement in question, explain (or
description of access path before and after 8.2), and number of rows in the
tables.
Nov 12 '05 #6

P: n/a
Hello Mark A,

thank you for your response, below is everything you mentioned except
explain info. I just don't know how to extract it for you. Please, let
me know how I should go about that. Thank you. Roman.

-- below table has 2348982 rows
CREATE TABLE EIS.EIS_NAME_CASE
(NAME_CASE_ID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
MASTER_NAME_INDEX_PID INTEGER NOT NULL,
LU_NAME_TYPE INTEGER NOT NULL,
PERSONNEL_ID INTEGER NOT NULL,
MASTER_NAME_INDEX_ID INTEGER NOT NULL,
WORKING_COPY_YN INTEGER,
REARREST_INFO INTEGER,
ROW_ACTIVE_INACTIVE INTEGER NOT NULL,
ROW_INSERTED_DATE TIMESTAMP NOT NULL,
ROW_INSERTED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_DATE TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_NAME_CASE LOCKSIZE ROW APPEND OFF NOT VOLATILE;
ALTER TABLE EIS.EIS_NAME_CASE ADD CONSTRAINT XPKEIS_NAME_CASE PRIMARY
KEY (NAME_CASE_ID );
ALTER TABLE EIS.EIS_NAME_CASE ADD CONSTRAINT R__113 FOREIGN KEY
(LU_NAME_TYPE )
REFERENCES EIS.EIS_LU_NAME_TYPE (LU_NAME_TYPE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_NAME_CASE ADD CONSTRAINT R__127 FOREIGN KEY
(PERSONNEL_ID )
REFERENCES EIS.EIS_PERSONNEL (PERSONNEL_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_NAME_CASE ADD CONSTRAINT R__138 FOREIGN KEY
(MASTER_NAME_INDEX_ID )
REFERENCES EIS.EIS_MASTER_NAME_INDEX (MASTER_NAME_INDEX_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_NAME_CASE ADD CONSTRAINT R__99 FOREIGN KEY
(MASTER_NAME_INDEX_PID )
REFERENCES EIS.EIS_MASTER_NAME_INDEX_PID (MASTER_NAME_INDEX_PID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.NC_ROW_ACT_INACT ON EIS.EIS_NAME_CASE
(ROW_ACTIVE_INACTIVE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF2EIS_NAME_CASE ON EIS.EIS_NAME_CASE
(MASTER_NAME_INDEX_PID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF3EIS_NAME_CASE ON EIS.EIS_NAME_CASE (LU_NAME_TYPE
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF4EIS_NAME_CASE ON EIS.EIS_NAME_CASE (PERSONNEL_ID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF5EIS_NAME_CASE ON EIS.EIS_NAME_CASE
(MASTER_NAME_INDEX_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL050919123932470 ON EIS.EIS_NAME_CASE
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/************************************************** **************/
--below table has 1833432 rows
CREATE TABLE EIS.EIS_NAME_CASE_XREF
(NAME_CASE_XREF_ID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
NAME_CASE_ID INTEGER NOT NULL,
CASE_ID INTEGER NOT NULL,
ROW_ACTIVE_INACTIVE INTEGER NOT NULL,
ROW_INSERTED_DATE TIMESTAMP NOT NULL,
ROW_INSERTED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_DATE TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_NAME_CASE_XREF LOCKSIZE ROW APPEND OFF NOT
VOLATILE;
ALTER TABLE EIS.EIS_NAME_CASE_XREF ADD CONSTRAINT SQL050919130448330
PRIMARY KEY (NAME_CASE_XREF_ID );
ALTER TABLE EIS.EIS_NAME_CASE_XREF ADD CONSTRAINT R__455 FOREIGN KEY
(CASE_ID )
REFERENCES EIS.EIS_CASE (CASE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.XIF1EIS_NAME_CASE_ ON EIS.EIS_NAME_CASE_XREF
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF2EIS_NAME_CASE_ ON EIS.EIS_NAME_CASE_XREF (CASE_ID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL050919130441470 ON
EIS.EIS_NAME_CASE_XREF (NAME_CASE_XREF_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/************************************************** **************/
-- below table has 658730 rows
CREATE TABLE EIS.EIS_MASTER_NAME_INDEX
(MASTER_NAME_INDEX_ID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
SUFFIX VARCHAR(4),
FIRST_NAME VARCHAR(50),
MIDDLE_NAME VARCHAR(50),
LAST_NAME VARCHAR(50) NOT NULL,
ROW_ACTIVE_INACTIVE INTEGER NOT NULL,
ROW_INSERTED_DATE TIMESTAMP NOT NULL,
ROW_INSERTED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_DATE TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_MASTER_NAME_INDEX LOCKSIZE ROW APPEND OFF NOT
VOLATILE;
ALTER TABLE EIS.EIS_MASTER_NAME_INDEX ADD CONSTRAINT
SQL050919130353690 PRIMARY KEY (MASTER_NAME_INDEX_ID );
ALTER TABLE EIS.EIS_MASTER_NAME_INDEX ADD CONSTRAINT R__405 FOREIGN
KEY (SUFFIX )
REFERENCES EIS.EIS_LU_NAME_SUFFIX (SUFFIX )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.XIE1EIS_MNI_LAST ON EIS.EIS_MASTER_NAME_INDEX
(LAST_NAME ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIE2EIS_MNI_FIRST ON EIS.EIS_MASTER_NAME_INDEX
(FIRST_NAME ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIE3EIS_MNI_MIDDLE ON EIS.EIS_MASTER_NAME_INDEX
(MIDDLE_NAME ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF1EIS_MASTER_NAM ON EIS.EIS_MASTER_NAME_INDEX
(SUFFIX ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL050919130351080 ON
EIS.EIS_MASTER_NAME_INDEX (MASTER_NAME_INDEX_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/************************************************** *******************/
--below table has 1649889 rows
CREATE TABLE EIS.EIS_MASTER_NAME_INDEX_PID
(MASTER_NAME_INDEX_PID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
PID VARCHAR(50) NOT NULL,
SID VARCHAR(50) NOT NULL,
SUBJECT_NCIC_NUMBER VARCHAR(50) NOT NULL,
HISTORICAL_SID VARCHAR(50),
ROW_ACTIVE_INACTIVE INTEGER NOT NULL,
ROW_INSERTED_DATE TIMESTAMP NOT NULL,
ROW_INSERTED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_DATE TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_MASTER_NAME_INDEX_PID LOCKSIZE ROW APPEND OFF
NOT VOLATILE;
ALTER TABLE EIS.EIS_MASTER_NAME_INDEX_PID ADD CONSTRAINT
SQL050919130418870 PRIMARY KEY (MASTER_NAME_INDEX_PID );
CREATE INDEX EIS.IDX_MNI_H_SID ON EIS.EIS_MASTER_NAME_INDEX_PID
(HISTORICAL_SID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_MNI_PID ON EIS.EIS_MASTER_NAME_INDEX_PID (PID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_MNI_SID ON EIS.EIS_MASTER_NAME_INDEX_PID (SID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL050919130412110 ON
EIS.EIS_MASTER_NAME_INDEX_PID (MASTER_NAME_INDEX_PID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/************************************************** *************/
-- below table has 438923 rows
CREATE TABLE EIS.EIS_CASE_SUBJECT_DESC
(CASE_SUBJECT_DESC_ID INTEGER NOT NULL GENERATED
BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
LU_ACCENT_ID INTEGER,
NAME_CASE_ID INTEGER,
BIRTH_STATE CHARACTER(2),
BIRTH_COUNTRY INTEGER,
LU_COMPLEXION_ID INTEGER,
LU_EYE_COLOR_ID INTEGER,
LU_HAIR_COLOR_ID INTEGER,
LU_TYPE_BUILD_ID INTEGER,
RACE INTEGER,
ETHNICITY INTEGER,
SEX INTEGER,
AGE_CLASSIFICATION INTEGER,
BLOOD_TYPE INTEGER,
LU_MARITAL_STATUS_ID INTEGER,
LU_CAREER_ID INTEGER,
LU_SCHOOL_GRADE_ID INTEGER,
LU_DEFORMITY_ID INTEGER,
LU_SPEECH_ID INTEGER,
LU_HAIR_LENGTH_ID INTEGER,
LU_HAIR_STYLE_ID INTEGER,
LU_FACIAL_HAIR_ID INTEGER,
LU_FACIAL_HAIR_COLOR_ID INTEGER,
LU_FACIAL_HAIR_LENGTH_ID INTEGER,
LU_HAIR_TEXTURE_ID INTEGER,
LU_HAIR_CONDITION_ID INTEGER,
LU_TEETH_ID INTEGER,
LU_BODY_ODOR_ID INTEGER,
LU_BODY_HAIR_ID INTEGER,
LU_HAND_USE_ID INTEGER,
LU_SOBRIETY_TYPE_ID INTEGER,
LU_RESIDENT_STATUS_ID INTEGER,
ARREST_NUMBER INTEGER,
COMPLAINT_NUMBER VARCHAR(50),
WEIGHT INTEGER,
WEIGHT_TO INTEGER,
HEIGHT_FT INTEGER,
HEIGHT_TO_FT INTEGER,
HEIGHT_IN INTEGER,
HEIGHT_TO_IN INTEGER,
AGE INTEGER,
AGE_TO INTEGER,
EYES VARCHAR(50),
PRIMARY_OCCUPATION VARCHAR(50),
JUVENILE_YN INTEGER,
DOB_DATE TIMESTAMP,
BIRTH_CITY VARCHAR(50),
SHIRT VARCHAR(50),
GLOVES VARCHAR(50),
JEWELRY VARCHAR(50),
PANTS VARCHAR(50),
MASK VARCHAR(50),
HAT VARCHAR(50),
SHOES VARCHAR(50),
COAT VARCHAR(50),
ACCESSORIES VARCHAR(50),
DRESS_SKIRT VARCHAR(50),
GLASSES_YN INTEGER,
CONTACT_LENSES_YN INTEGER,
HOMELESS_YN INTEGER,
HIGH_SCHOOL_GRADUATE_YN INTEGER,
GENERAL_EQUIVALENCY_DIPLOMA_YN INTEGER,
OTHER_YN INTEGER,
OTHER_DESC VARCHAR(300),
ROW_ACTIVE_INACTIVE INTEGER NOT NULL,
ROW_INSERTED_DATE TIMESTAMP NOT NULL,
ROW_INSERTED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PERSON VARCHAR(20) NOT NULL,
ROW_UPDATED_DATE TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC LOCKSIZE ROW APPEND OFF NOT
VOLATILE;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT PKEIS_CASE_SUBJEC
PRIMARY KEY (CASE_SUBJECT_DESC_ID );
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__16789 FOREIGN
KEY (LU_EYE_COLOR_ID )
REFERENCES EIS.EIS_LU_EYE_COLOR (LU_EYE_COLOR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__175 FOREIGN
KEY (BIRTH_COUNTRY )
REFERENCES EIS.EIS_LU_COUNTRY (LU_COUNTRY )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__215 FOREIGN
KEY (LU_COMPLEXION_ID )
REFERENCES EIS.EIS_LU_COMPLEXION (LU_COMPLEXION_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__217 FOREIGN
KEY (LU_HAIR_COLOR_ID )
REFERENCES EIS.EIS_LU_HAIR_COLOR (LU_HAIR_COLOR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__220 FOREIGN
KEY (LU_TYPE_BUILD_ID )
REFERENCES EIS.EIS_LU_TYPE_BUILD (LU_TYPE_BUILD_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__242 FOREIGN
KEY (RACE )
REFERENCES EIS.EIS_LU_RACE (RACE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__243 FOREIGN
KEY (ETHNICITY )
REFERENCES EIS.EIS_LU_ETHNICITY (ETHNICITY )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__244 FOREIGN
KEY (SEX )
REFERENCES EIS.EIS_LU_SEX (SEX )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__245 FOREIGN
KEY (AGE_CLASSIFICATION )
REFERENCES EIS.EIS_LU_AGE_CLASSIFICATION (AGE_CLASSIFICATION
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__246 FOREIGN
KEY (BIRTH_STATE )
REFERENCES EIS.EIS_LU_STATE (STATE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__247 FOREIGN
KEY (BLOOD_TYPE )
REFERENCES EIS.EIS_LU_BLOOD_TYPE (BLOOD_TYPE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__317 FOREIGN
KEY (LU_MARITAL_STATUS_ID )
REFERENCES EIS.EIS_LU_MARITAL_STATUS (LU_MARITAL_STATUS_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__322 FOREIGN
KEY (LU_CAREER_ID )
REFERENCES EIS.EIS_LU_CAREER (LU_CAREER_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__331 FOREIGN
KEY (LU_SCHOOL_GRADE_ID )
REFERENCES EIS.EIS_LU_SCHOOL_GRADE (LU_SCHOOL_GRADE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__405 FOREIGN
KEY (LU_DEFORMITY_ID )
REFERENCES EIS.EIS_LU_DEFORMITY (LU_DEFORMITY_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__406 FOREIGN
KEY (LU_SPEECH_ID )
REFERENCES EIS.EIS_LU_SPEECH (LU_SPEECH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__407 FOREIGN
KEY (LU_ACCENT_ID )
REFERENCES EIS.EIS_LU_ACCENT (LU_ACCENT_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__408 FOREIGN
KEY (LU_HAIR_LENGTH_ID )
REFERENCES EIS.EIS_LU_HAIR_LENGTH (LU_HAIR_LENGTH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__409 FOREIGN
KEY (LU_HAIR_STYLE_ID )
REFERENCES EIS.EIS_LU_HAIR_STYLE (LU_HAIR_STYLE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__410 FOREIGN
KEY (LU_FACIAL_HAIR_ID )
REFERENCES EIS.EIS_LU_FACIAL_HAIR (LU_FACIAL_HAIR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__411 FOREIGN
KEY (LU_FACIAL_HAIR_COLOR_ID )
REFERENCES EIS.EIS_LU_FACIAL_HAIR_COLOR
(LU_FACIAL_HAIR_COLOR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__412 FOREIGN
KEY (LU_FACIAL_HAIR_LENGTH_ID )
REFERENCES EIS.EIS_LU_FACIAL_HAIR_LENGTH
(LU_FACIAL_HAIR_LENGTH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__413 FOREIGN
KEY (LU_HAIR_TEXTURE_ID )
REFERENCES EIS.EIS_LU_HAIR_TEXTURE (LU_HAIR_TEXTURE_ID )

ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__414 FOREIGN
KEY (LU_HAIR_CONDITION_ID )
REFERENCES EIS.EIS_LU_HAIR_CONDITION (LU_HAIR_CONDITION_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__415 FOREIGN
KEY (LU_TEETH_ID )
REFERENCES EIS.EIS_LU_TEETH (LU_TEETH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__416 FOREIGN
KEY (LU_BODY_ODOR_ID )
REFERENCES EIS.EIS_LU_BODY_ODOR (LU_BODY_ODOR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__417 FOREIGN
KEY (LU_BODY_HAIR_ID )
REFERENCES EIS.EIS_LU_BODY_HAIR (LU_BODY_HAIR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__418 FOREIGN
KEY (LU_HAND_USE_ID )
REFERENCES EIS.EIS_LU_HAND_USE (LU_HAND_USE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__535 FOREIGN
KEY (LU_SOBRIETY_TYPE_ID )
REFERENCES EIS.EIS_LU_SOBRIETY_TYPE (LU_SOBRIETY_TYPE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SUBJECT_DESC ADD CONSTRAINT R__536 FOREIGN
KEY (LU_RESIDENT_STATUS_ID )
REFERENCES EIS.EIS_LU_RESIDENT_STATUS (LU_RESIDENT_STATUS_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.IDX_CSD_AGE ON EIS.EIS_CASE_SUBJECT_DESC (AGE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_AGE_TO ON EIS.EIS_CASE_SUBJECT_DESC (AGE_TO
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_ARR ON EIS.EIS_CASE_SUBJECT_DESC
(ARREST_NUMBER ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_HIGHT ON EIS.EIS_CASE_SUBJECT_DESC (HEIGHT_IN
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_HIGHT_TO ON EIS.EIS_CASE_SUBJECT_DESC
(HEIGHT_TO_IN ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_WEIGHT ON EIS.EIS_CASE_SUBJECT_DESC (WEIGHT
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_WEIGHT_TO ON EIS.EIS_CASE_SUBJECT_DESC
(WEIGHT_TO ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX EIS.PKEIS_CASE_SUBJEC ON EIS.EIS_CASE_SUBJECT_DESC
(CASE_SUBJECT_DESC_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF10EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_EYE_COLOR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF11EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAIR_COLOR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF12EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_TYPE_BUILD_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF13EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC (RACE
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF14EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(ETHNICITY ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF15EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC (SEX
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF16EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(AGE_CLASSIFICATION ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF17EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(BIRTH_STATE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF18EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(BLOOD_TYPE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF19EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_MARITAL_STATUS_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF21EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_CAREER_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF22EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_SCHOOL_GRADE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF23EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_DEFORMITY_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF24EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_SPEECH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF25EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_ACCENT_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF26EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAIR_LENGTH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF27EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAIR_STYLE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF28EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_FACIAL_HAIR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF29EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_FACIAL_HAIR_COLOR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF30EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_FACIAL_HAIR_LENGTH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF31EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAIR_TEXTURE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF32EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAIR_CONDITION_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF33EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_TEETH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF34EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_BODY_ODOR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF35EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_BODY_HAIR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF36EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_HAND_USE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF37EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_SOBRIETY_TYPE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF38EIS_CASE_SUBJ ON EIS.EIS_CASE_SUBJECT_DESC
(LU_RESIDENT_STATUS_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF3EIS_CASE_SUBJR ON EIS.EIS_CASE_SUBJECT_DESC
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF4EIS_CASE_SUBJE ON EIS.EIS_CASE_SUBJECT_DESC
(BIRTH_COUNTRY ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF9EIS_CASE_SUBJE ON EIS.EIS_CASE_SUBJECT_DESC
(LU_COMPLEXION_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/************************************************** ***********************/
--below talbe is session. temp table used in the stored procedure it
can contain anywhere from 1 to 5000 rows
-- currenty there's 221 rows
CREATE TABLE EIS.T_RC (NC_ID INTEGER );
--- below is the SQL that executes fast on db2 8.1.4
-- all tables are of the same structure and and contain the same data

SELECT nc.*,
mni.*,
pid.*,
ncx.name_case_xref_id, ncx.case_id,

CSD.ACCESSORIES,CSD.AGE,CSD.AGE_CLASSIFICATION,CSD .AGE_TO,CSD.ARREST_NUMBER,CSD.BIRTH_CITY,CSD.BIRTH _COUNTRY,CSD.BIRTH_STATE,CSD.BLOOD_TYPE,CSD.CASE_S UBJECT_DESC_ID,CSD.COAT,CSD.COMPLAINT_NUMBER,CSD.C ONTACT_LENSES_YN,CSD.DOB_DATE,CSD.DRESS_SKIRT,CSD. ETHNICITY,CSD.EYES,CSD.GENERAL_EQUIVALENCY_DIPLOMA _YN,CSD.GLASSES_YN,CSD.GLOVES,CSD.HAT,CSD.HEIGHT_F T,CSD.HEIGHT_IN,CSD.HEIGHT_TO_FT,CSD.HEIGHT_TO_IN, CSD.HIGH_SCHOOL_GRADUATE_YN,CSD.HOMELESS_YN,CSD.JE WELRY,CSD.JUVENILE_YN,CSD.LU_ACCENT_ID,CSD.LU_BODY _HAIR_ID,CSD.LU_BODY_ODOR_ID,CSD.LU_CAREER_ID,CSD. LU_COMPLEXION_ID,CSD.LU_DEFORMITY_ID,CSD.LU_EYE_CO LOR_ID,CSD.LU_FACIAL_HAIR_COLOR_ID,CSD.LU_FACIAL_H AIR_ID,CSD.LU_FACIAL_HAIR_LENGTH_ID,CSD.LU_HAIR_CO LOR_ID,CSD.LU_HAIR_CONDITION_ID,CSD.LU_HAIR_LENGTH _ID,CSD.LU_HAIR_STYLE_ID,CSD.LU_HAIR_TEXTURE_ID,CS D.LU_HAND_USE_ID,CSD.LU_MARITAL_STATUS_ID,CSD.LU_R ESIDENT_STATUS_ID,CSD.LU_SCHOOL_GRADE_ID,CSD.LU_SO BRIETY_TYPE_ID,CSD.LU_SPEECH_ID,CSD.LU_TEETH_ID,CS D.LU_TYPE_BUILD_ID,CSD.MASK,CSD.NAME_CASE_ID,CSD.O THER_DESC,CSD.OTHER_YN,CSD.PANTS,CSD.PRIMARY_OCCUP ATION,CSD.RACE,CSD.ROW_ACTIVE_INACTIVE,CSD.ROW_INS ERTED_DATE,CSD.ROW_INSERTED_PERSON,CSD.ROW_UPDATED _DATE,CSD.ROW_UPDATED_PERSON,CSD.SEX,CSD.SHIRT,CSD .SHOES,CSD.WEIGHT,CSD.WEIGHT_TO,

COALESCE((SELECT DISTINCT nc_id FROM ( (SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
WHERE u.nc_id = nc.name_case_id),0) AS hit
FROM eis_name_case nc
JOIN eis_master_name_index mni
ON nc.master_name_index_id = mni.master_name_index_id
JOIN eis_master_name_index_pid pid
ON nc.master_name_index_pid = pid.master_name_index_pid
LEFT JOIN eis_case_subject_desc csd
ON csd.name_case_id = nc.name_case_id
LEFT JOIN eis_name_case_xref ncx
ON ncx.name_case_id = nc.name_case_id
WHERE nc.master_name_index_pid IN (SELECT DISTINCT
master_name_index_pid
FROM eis.eis_name_case
WHERE name_case_id IN ( SELECT DISTINCT U.NC_ID
FROM ( (SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
join t_rc MNI
on MNI.NC_ID = U.NC_ID
FETCH FIRST 5000 ROWS ONLY
)
AND lu_name_type <> 2
ORDER BY master_name_index_pid
FETCH FIRST 500 ROWS ONLY
)
AND nc.row_active_inactive = 1 AND lu_name_type <> 2
ORDER BY nc.master_name_index_pid, mni.last_name, mni.first_name,
mni.middle_name
FETCH FIRST 501 ROWS ONLY

Nov 12 '05 #7

P: n/a
Hello Mark A,

I figured out how to get explain data.
----------------> from 8.2.3:
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "EIS"
SQL Statement:

SELECT nc.*, mni.*, pid.*, ncx.name_case_xref_id, ncx.case_id,
CSD.*, COALESCE((
SELECT DISTINCT nc_id
FROM ((
SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY))U
WHERE u.nc_id =nc.name_case_id), 0)AS hit
FROM eis_name_case nc JOIN eis_master_name_index mni ON
nc.master_name_index_id =mni.master_name_index_id JOIN
eis_master_name_index_pid pid ON nc.master_name_index_pid =
pid.master_name_index_pid LEFT JOIN eis_case_subject_desc
csd ON csd.name_case_id =nc.name_case_id LEFT JOIN
eis_name_case_xref ncx ON ncx.name_case_id =nc.name_case_id
WHERE nc.master_name_index_pid IN (
SELECT DISTINCT master_name_index_pid
FROM eis.eis_name_case
WHERE name_case_id IN (
SELECT DISTINCT U.NC_ID
FROM ((
SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY))U join t_rc MNI on MNI.NC_ID =
U.NC_ID
FETCH FIRST 5000 ROWS ONLY)AND lu_name_type <> 2
ORDER BY master_name_index_pid
FETCH FIRST 500 ROWS ONLY)AND nc.row_active_inactive =1 AND
lu_name_type <> 2
ORDER BY nc.master_name_index_pid, mni.last_name, mni.first_name,
mni.middle_name
FETCH FIRST 501 ROWS ONLY
Section Code Page = 1252

Estimated Cost = 4045344.000000
Estimated Cardinality = 501.000000

Data Stream 1:
| Not Piped
| Data Stream 2:
| | Not Piped
| | Access Table Name = EIS.T_RC ID = 2,658
| | | #Columns = 1
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | Process Build Table for Hash Join
| | Hash Join
| | | Early Out: Single Match Per Outer Row
| | | Estimated Build Size: 12661
| | | Estimated Probe Size: 9196
| | | Access Table Name = EIS.T_RC ID = 2,658
| | | | #Columns = 1
| | | | Relation Scan
| | | | | Prefetch: Eligible
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: NC_ID (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 5000
| | | | Row Width = 12
| | | | Sort Limited To Estimated Row Count
| | | Piped
| | | Duplicate Elimination
| | Access Temp Table ID = t1
| | | #Columns = 1
| | | Relation Scan
| | | | Prefetch: Eligible
| | Nested Loop Join
| | | Access Table Name = EIS.EIS_NAME_CASE ID = 2,220
| | | | Index Scan: Name = SYSIBM.SQL050919123932470 ID = 5
| | | | | Regular Index (Not Clustered)
| | | | | Index Columns:
| | | | | | 1: NAME_CASE_ID (Ascending)
| | | | #Columns = 1
| | | | Single Record
| | | | Fully Qualified Unique Key
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: ?
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: ?
| | | | Data Prefetch: None
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | #Predicates = 1
| | Insert Into Sorted Temp Table ID = t2
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: MASTER_NAME_INDEX_PID (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 15
| | | | Row Width = 8
| | | Piped
| | | Duplicate Elimination
| | Access Temp Table ID = t2
| | | #Columns = 1
| | | Relation Scan
| | | | Prefetch: Eligible
| | Insert Into Temp Table ID = t3
| | | #Columns = 1
| End of Data Stream 2
| ANY/ALL Subquery
| | Access Temp Table ID = t3
| | | Keep Rows In Private Memory
End of Data Stream 1
Access Table Name = EIS.EIS_NAME_CASE_XREF ID = 2,63
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Estimated Build Size: 70801864
| Estimated Probe Size: 1359256064
| Access Table Name = EIS.EIS_MASTER_NAME_INDEX ID = 2,254
| | #Columns = 10
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Build Table for Hash Join
| Hash Join
| | Early Out: Single Match Per Outer Row
| | Estimated Build Size: 66205408
| | Estimated Probe Size: 205605152
| | Access Table Name = EIS.EIS_NAME_CASE ID = 2,220
| | | Index Scan: Name = EIS.NC_ROW_ACT_INACT ID = 6
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: ROW_ACTIVE_INACTIVE (Ascending)
| | | #Columns = 11
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: 1
| | | | Stop Key: Inclusive Value
| | | | | | 1: 1
| | | Data Prefetch: Eligible 0
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| | | | Process Build Table for Hash Join
| | Hash Join
| | | Early Out: Single Match Per Inner Row
| | | Estimated Build Size: 131360288
| | | Estimated Probe Size: 133935056
| | | Bit Filter Size: 1400448
| | | Access Table Name = EIS.EIS_MASTER_NAME_INDEX_PID ID = 2,256
| | | | #Columns = 10
| | | | Relation Scan
| | | | | Prefetch: Eligible
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | Process Probe Table for Hash Join
| Insert Into Sorted Temp Table ID = t4
| | #Columns = 29
| | #Sort Key Columns = 1
| | | Key 1: NAME_CASE_ID (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 1417792
| | | Row Width = 224
| | Piped
| Access Temp Table ID = t4
| | #Columns = 29
| | Relation Scan
| | | Prefetch: Eligible
| Left Outer Merge Join
| | Access Table Name = EIS.EIS_CASE_SUBJECT_DESC ID = 2,48
| | | Index Scan: Name = EIS.XIF3EIS_CASE_SUBJR ID = 29
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: NAME_CASE_ID (Ascending)
| | | #Columns = 69
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | | Data Prefetch: Eligible 87084
| | | Index Prefetch: Eligible 87084
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
| EXISTS Subquery
| | Access Temp Table ID = t3
| | | Keep Rows In Private Memory
Insert Into Sorted Temp Table ID = t5
| #Columns = 100
| #Sort Key Columns = 4
| | Key 1: (Ascending)
| | Key 2: (Ascending)
| | Key 3: (Ascending)
| | Key 4: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 973
| | Row Width = 956
| Piped
Access Temp Table ID = t5
| #Columns = 100
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Piped Inner
| Access Table Name = EIS.T_RC ID = 2,658
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| Residual Predicate(s)
| | #Predicates = 1
| Insert Into Sorted Temp Table ID = t6
| | #Columns = 1
| | #Sort Key Columns = 1
| | | Key 1: (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 1
| | | Row Width = 8
| | Piped
| | Duplicate Elimination
| Access Temp Table ID = t6
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
Return Data to Application
| #Columns = 104

End of section
----------------> from 8.1.4:
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "EIS"
SQL Statement:

SELECT nc.*, mni.*, pid.*, ncx.name_case_xref_id, ncx.case_id,
CSD.*, COALESCE((SELECT DISTINCT nc_id
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
WHERE u.nc_id =nc.name_case_id), 0) AS hit
FROM eis_name_case nc JOIN eis_master_name_index mni ON
nc.master_name_index_id =mni.master_name_index_id JOIN
eis_master_name_index_pid pid ON
nc.master_name_index_pid =pid.master_name_index_pid LEFT
JOIN eis_case_subject_desc csd ON csd.name_case_id =
nc.name_case_id LEFT JOIN eis_name_case_xref ncx ON
ncx.name_case_id =nc.name_case_id
WHERE nc.master_name_index_pid IN
(SELECT DISTINCT master_name_index_pid
FROM eis.eis_name_case
WHERE name_case_id IN (
SELECT DISTINCT U.NC_ID
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U join t_rc MNI on
MNI.NC_ID =U.NC_ID
FETCH FIRST 5000 ROWS ONLY) AND lu_name_type <> 2
ORDER BY master_name_index_pid
FETCH FIRST 500 ROWS ONLY) AND nc.row_active_inactive =
1 AND lu_name_type <> 2
ORDER BY nc.master_name_index_pid, mni.last_name,
mni.first_name, mni.middle_name
FETCH FIRST 501 ROWS ONLY
Section Code Page = 1252

Estimated Cost = 6581.564941
Estimated Cardinality = 12.606277

Access Table Name = EIS.T_RC ID = 2,659
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 12661
| Estimated Probe Size: 9196
| Access Table Name = EIS.T_RC ID = 2,659
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: NC_ID (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 5000
| | Row Width = 12
| | Sort Limited To Estimated Row Count
| Piped
| Duplicate Elimination
Access Temp Table ID = t1
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = EIS.EIS_NAME_CASE ID = 2,220
| | Index Scan: Name = EIS.XPKEIS_NAME_CASE ID = 5
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: NAME_CASE_ID (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
Insert Into Sorted Temp Table ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: MASTER_NAME_INDEX_PID (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 15
| | Row Width = 8
| Piped
| Duplicate Elimination
Access Temp Table ID = t2
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = EIS.EIS_NAME_CASE ID = 2,220
| | Index Scan: Name = EIS.XIF2EIS_NAME_CASE ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_INDEX_PID (Ascending)
| | #Columns = 11
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 2
| | | Insert Into Sorted Temp Table ID = t3
| | | | #Columns = 12
| | | | #Sort Key Columns = 1
| | | | | Key 1: MASTER_NAME_INDEX_ID (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 13
| | | | | Row Width = 72
| | | | Piped
Sorted Temp Table Completion ID = t3
Access Temp Table ID = t3
| #Columns = 12
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = EIS.EIS_MASTER_NAME_INDEX_PID ID = 2,256
| | Index Scan: Name = EIS.XPKEIS_MASTER_NAME ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_INDEX_PID (Ascending)
| | #Columns = 10
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Nested Loop Join
| Access Table Name = EIS.EIS_MASTER_NAME_INDEX ID = 2,254
| | Index Scan: Name = EIS.PKEIS_MASTER_NAME ID = 2
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_INDEX_ID (Ascending)
| | #Columns = 10
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Insert Into Sorted Temp Table ID = t4
| #Columns = 31
| #Sort Key Columns = 1
| | Key 1: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 13
| | Row Width = 232
| Piped
Access Temp Table ID = t4
| #Columns = 31
| Relation Scan
| | Prefetch: Eligible
Left Outer Nested Loop Join
| Access Table Name = EIS.EIS_CASE_SUBJECT_DESC ID = 2,48
| | Index Scan: Name = EIS.XIF3EIS_CASE_SUBJR ID = 29
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: NAME_CASE_ID (Ascending)
| | #Columns = 69
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Left Outer Nested Loop Join
| Access Table Name = EIS.EIS_NAME_CASE_XREF ID = 2,63
| | Index Scan: Name = EIS.XIF1EIS_NAME_CASE_ ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: NAME_CASE_ID (Ascending)
| | #Columns = 2
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Insert Into Sorted Temp Table ID = t5
| #Columns = 102
| #Sort Key Columns = 4
| | Key 1: (Ascending)
| | Key 2: (Ascending)
| | Key 3: (Ascending)
| | Key 4: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 501
| | Row Width = 960
| | Sort Limited To Estimated Row Count
| Piped
Access Temp Table ID = t5
| #Columns = 102
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Piped Inner
| Access Table Name = EIS.T_RC ID = 2,659
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| Residual Predicate(s)
| | #Predicates = 1
| Insert Into Sorted Temp Table ID = t6
| | #Columns = 1
| | #Sort Key Columns = 1
| | | Key 1: (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 1
| | | Row Width = 8
| | Piped
| | Duplicate Elimination
| Access Temp Table ID = t6
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
Return Data to Application
| #Columns = 104

End of section

Nov 12 '05 #8

P: n/a

"romicva" <ro*****@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Hello Mark A,

I figured out how to get explain data.
----------------> from 8.2.3:
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "EIS"
SQL Statement:

SELECT nc.*, mni.*, pid.*, ncx.name_case_xref_id, ncx.case_id,
CSD.*, COALESCE((
SELECT DISTINCT nc_id
FROM ((
SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY))U
WHERE u.nc_id =nc.name_case_id), 0)AS hit
FROM eis_name_case nc JOIN eis_master_name_index mni ON
nc.master_name_index_id =mni.master_name_index_id JOIN
eis_master_name_index_pid pid ON nc.master_name_index_pid =
pid.master_name_index_pid LEFT JOIN eis_case_subject_desc
csd ON csd.name_case_id =nc.name_case_id LEFT JOIN
eis_name_case_xref ncx ON ncx.name_case_id =nc.name_case_id
WHERE nc.master_name_index_pid IN (
SELECT DISTINCT master_name_index_pid
FROM eis.eis_name_case
WHERE name_case_id IN (
SELECT DISTINCT U.NC_ID
FROM ((
SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY))U join t_rc MNI on MNI.NC_ID =
U.NC_ID
FETCH FIRST 5000 ROWS ONLY)AND lu_name_type <> 2
ORDER BY master_name_index_pid
FETCH FIRST 500 ROWS ONLY)AND nc.row_active_inactive =1 AND
lu_name_type <> 2
ORDER BY nc.master_name_index_pid, mni.last_name, mni.first_name,
mni.middle_name
FETCH FIRST 501 ROWS ONLY
Section Code Page = 1252

Estimated Cost = 4045344.000000
Estimated Cardinality = 501.000000

----------------> from 8.1.4:
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "EIS"
SQL Statement:

SELECT nc.*, mni.*, pid.*, ncx.name_case_xref_id, ncx.case_id,
CSD.*, COALESCE((SELECT DISTINCT nc_id
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
WHERE u.nc_id =nc.name_case_id), 0) AS hit
FROM eis_name_case nc JOIN eis_master_name_index mni ON
nc.master_name_index_id =mni.master_name_index_id JOIN
eis_master_name_index_pid pid ON
nc.master_name_index_pid =pid.master_name_index_pid LEFT
JOIN eis_case_subject_desc csd ON csd.name_case_id =
nc.name_case_id LEFT JOIN eis_name_case_xref ncx ON
ncx.name_case_id =nc.name_case_id
WHERE nc.master_name_index_pid IN
(SELECT DISTINCT master_name_index_pid
FROM eis.eis_name_case
WHERE name_case_id IN (
SELECT DISTINCT U.NC_ID
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U join t_rc MNI on
MNI.NC_ID =U.NC_ID
FETCH FIRST 5000 ROWS ONLY) AND lu_name_type <> 2
ORDER BY master_name_index_pid
FETCH FIRST 500 ROWS ONLY) AND nc.row_active_inactive =
1 AND lu_name_type <> 2
ORDER BY nc.master_name_index_pid, mni.last_name,
mni.first_name, mni.middle_name
FETCH FIRST 501 ROWS ONLY
Section Code Page = 1252

Estimated Cost = 6581.564941
Estimated Cardinality = 12.606277


I created all the objects you posted, although some foreign keys would not
create because I did not have the DDL for the parent table. However, this
will not affect the performance of a select statement. I updated the
statistics with the row counts you gave me.

However, I also altered each table and set it to volatile, which tells DB2
to ignore most of the statistics and use an index whenever one is
available.. I did this because I don't have any real data, and the
distribution stats might be misleading.

I did an explain and got a Estimated Cost = 3029.18 (note the decimal after
3029).

When I set the tables back to not volatile, the Estimated Cost = 8,320,633
(no decimal). This lead me to believe that there is something wrong with the
stats or DB2 is fooled by them.

First, I would reorg the tables and indexes. You will need a large amount of
temp space to reorg all the indexes on EIS_CASE_SUBJECT_DESC (see my last
paragraph below). If you can't reorg all the indexes, drop them and recreate
them one at a time.

Then I would try to run runstats again, with distribution on key columns and
indexes all. Do not take samples. You do not need to continuously update
stats unless the data changes significantly. If that does not work, alter
the tables to volatile.

BTW, you have way too many indexes on the EIS_CASE_SUBJECT_DESC. Although it
will not affect queries, it will affect your load or insert times
significantly. If a column has less than 10 unique values, you should drop
the index. For example race, blood_type, hair color, eye color, marital
status, and many others will likely never be used by DB2 for queries to
improve performance.
Nov 12 '05 #9

P: n/a
In article <11*********************@z14g2000cwz.googlegroups. com>,
ro*****@gmail.com says...
Hello Mark Yudkin,

I believe I saw your post on this subject, where you said that
"...feedling with indexes helped...". Any chance I can do the same?

Thank you.
Roman.


I've had problems with index usage which were solved by adding the 'with
distribution' option to runstats. You might give it a try.
Nov 12 '05 #10

P: n/a
In one query i had i forced it to use an INDEX by appending "OPTIMIZE
FOR 1 ROW". It ended up being that up to something like OPTIMIZE FOR
367 ROWS records it'd use the INDEX, but starting with at OPTIMIZE FOR
367 ROWS it stopped. I checked the selectivity and all that, but
couldn't figure it out. I'm by no means an expert in this. In the
PROCEDURE using DYNAMIC RESULT SETS 1 instead of OPTIMIZE FOR 1 ROW did
the same thing, that is, both work.

B.

Nov 12 '05 #11

P: n/a
Brian Tkatch wrote:
In one query i had i forced it to use an INDEX by appending "OPTIMIZE
FOR 1 ROW". It ended up being that up to something like OPTIMIZE FOR
367 ROWS records it'd use the INDEX, but starting with at OPTIMIZE FOR
367 ROWS it stopped. I checked the selectivity and all that, but
couldn't figure it out. I'm by no means an expert in this. In the
PROCEDURE using DYNAMIC RESULT SETS 1 instead of OPTIMIZE FOR 1 ROW did
the same thing, that is, both work.

B.

Ok woudl refrain from playing with specific numbers on OPTIMIZE FOR.
Either use OPTIMIZE FOR 1 ROW or don't use it at all.
Teh effect of OPTIMIZE FOR <x> ROWS is that it tried to serve up these
first <x> rows as quickly as possible.
This means: Avoid SORTs avoid TEMP! And because of that you will see
indexes used that normally wouldn't.
The point of column distribution was made.. Did you try that.
Also You have a LOT of FETCH FIRST in there. More than I have ever seen...
What are these for? It appears you may have been trying to coax the
optimizer and in the processed confused it totally.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #12

P: n/a
Hello all,

thank you for trying to help me! I really appretiate it. Unfortunatelly
all this didn't get me far. I tried what Mark A advised in post 7. I
get cost variation between 1 mil and 4 mil (volatile vs not not
volatile), i got rid of all FETCH FIRST... as Serge mentioned in post
12 (no noticable difference). Never the cost goes as low as 6000 as it
was in 8.1.4. Again, same data, same hardware, same query. What else
can I do to get 8.2 optimizer to be as good as 8.1.4

Nov 12 '05 #13

P: n/a
>Either use OPTIMIZE FOR 1 ROW or don't use it at all.
Teh effect of OPTIMIZE FOR <x> ROWS is that it tried to serve up these
first <x> rows as quickly as possible.
This means: Avoid SORTs avoid TEMP! And because of that you will see
indexes used that normally wouldn't.


Interesting. Thanx.

B.

Nov 12 '05 #14

P: n/a
"romicva" <ro*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hello all,

thank you for trying to help me! I really appretiate it. Unfortunatelly
all this didn't get me far. I tried what Mark A advised in post 7. I
get cost variation between 1 mil and 4 mil (volatile vs not not
volatile), i got rid of all FETCH FIRST... as Serge mentioned in post
12 (no noticable difference). Never the cost goes as low as 6000 as it
was in 8.1.4. Again, same data, same hardware, same query. What else
can I do to get 8.2 optimizer to be as good as 8.1.4

I did my test with 8.2 FP10 (8.2.3) in which I got a cost of about 3209
after setting the tables to volatile. I don't understand why you are having
a problem when I don't, but I would restart the instance if possible. If not
possible, make sure you do the following:

db2 flush package cache dynamic

There may be some other configuration problems in the database or dbm
(instance) configurations, or problems with your system temporary tables, or
maybe even bufferpool sizes. If you want to provide this information, we
will look at it.
Nov 12 '05 #15

P: n/a
We fiddled with the columns we place in the indices, eliminated an index
that was introduced prior to index or'ing, and such things. Basically,
EXPLAIN everything, analyse, experiment. We set up a test system so that we
wouldn't affect production, but used the full database content.
"romicva" <ro*****@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hello Mark Yudkin,

I believe I saw your post on this subject, where you said that
"...feedling with indexes helped...". Any chance I can do the same?

Thank you.
Roman.

Nov 12 '05 #16

P: n/a
Thank you Mark, spasibo :-)

Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.