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.
16  2137 
"romicva" <ro*****@gmail. com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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?
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.goo glegroups.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.
Hello Mark, what kind of information can I provide?
Thank you.
Roman.
Hello Mark Yudkin,
I believe I saw your post on this subject, where you said that
"...feedlin g with indexes helped...". Any chance I can do the same?
Thank you.
Roman.
"romicva" <ro*****@gmail. com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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.
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_CA SE
(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_IND EX_PID INTEGER NOT NULL,
LU_NAME_TYPE INTEGER NOT NULL,
PERSONNEL_ID INTEGER NOT NULL,
MASTER_NAME_IND EX_ID INTEGER NOT NULL,
WORKING_COPY_YN INTEGER,
REARREST_INFO INTEGER,
ROW_ACTIVE_INAC TIVE INTEGER NOT NULL,
ROW_INSERTED_DA TE TIMESTAMP NOT NULL,
ROW_INSERTED_PE RSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PER SON VARCHAR(20) NOT NULL,
ROW_UPDATED_DAT E TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_NAME_CA SE LOCKSIZE ROW APPEND OFF NOT VOLATILE;
ALTER TABLE EIS.EIS_NAME_CA SE ADD CONSTRAINT XPKEIS_NAME_CAS E PRIMARY
KEY (NAME_CASE_ID );
ALTER TABLE EIS.EIS_NAME_CA SE 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_CA SE ADD CONSTRAINT R__127 FOREIGN KEY
(PERSONNEL_ID )
REFERENCES EIS.EIS_PERSONN EL (PERSONNEL_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_NAME_CA SE ADD CONSTRAINT R__138 FOREIGN KEY
(MASTER_NAME_IN DEX_ID )
REFERENCES EIS.EIS_MASTER_ NAME_INDEX (MASTER_NAME_IN DEX_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_NAME_CA SE ADD CONSTRAINT R__99 FOREIGN KEY
(MASTER_NAME_IN DEX_PID )
REFERENCES EIS.EIS_MASTER_ NAME_INDEX_PID (MASTER_NAME_IN DEX_PID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.NC_ROW_ACT_ INACT ON EIS.EIS_NAME_CA SE
(ROW_ACTIVE_INA CTIVE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF2EIS_NAM E_CASE ON EIS.EIS_NAME_CA SE
(MASTER_NAME_IN DEX_PID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF3EIS_NAM E_CASE ON EIS.EIS_NAME_CA SE (LU_NAME_TYPE
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF4EIS_NAM E_CASE ON EIS.EIS_NAME_CA SE (PERSONNEL_ID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF5EIS_NAM E_CASE ON EIS.EIS_NAME_CA SE
(MASTER_NAME_IN DEX_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL05091 9123932470 ON EIS.EIS_NAME_CA SE
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/*************** *************** *************** *************** ****/
--below table has 1833432 rows
CREATE TABLE EIS.EIS_NAME_CA SE_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_INAC TIVE INTEGER NOT NULL,
ROW_INSERTED_DA TE TIMESTAMP NOT NULL,
ROW_INSERTED_PE RSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PER SON VARCHAR(20) NOT NULL,
ROW_UPDATED_DAT E TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_NAME_CA SE_XREF LOCKSIZE ROW APPEND OFF NOT
VOLATILE;
ALTER TABLE EIS.EIS_NAME_CA SE_XREF ADD CONSTRAINT SQL050919130448 330
PRIMARY KEY (NAME_CASE_XREF _ID );
ALTER TABLE EIS.EIS_NAME_CA SE_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_NAM E_CASE_ ON EIS.EIS_NAME_CA SE_XREF
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF2EIS_NAM E_CASE_ ON EIS.EIS_NAME_CA SE_XREF (CASE_ID
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL05091 9130441470 ON
EIS.EIS_NAME_CA SE_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_IN DEX_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_INAC TIVE INTEGER NOT NULL,
ROW_INSERTED_DA TE TIMESTAMP NOT NULL,
ROW_INSERTED_PE RSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PER SON VARCHAR(20) NOT NULL,
ROW_UPDATED_DAT E 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
SQL050919130353 690 PRIMARY KEY (MASTER_NAME_IN DEX_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_MAS TER_NAM ON EIS.EIS_MASTER_ NAME_INDEX
(SUFFIX ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX SYSIBM.SQL05091 9130351080 ON
EIS.EIS_MASTER_ NAME_INDEX (MASTER_NAME_IN DEX_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/*************** *************** *************** *************** *********/
--below table has 1649889 rows
CREATE TABLE EIS.EIS_MASTER_ NAME_INDEX_PID
(MASTER_NAME_IN DEX_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_NU MBER VARCHAR(50) NOT NULL,
HISTORICAL_SID VARCHAR(50),
ROW_ACTIVE_INAC TIVE INTEGER NOT NULL,
ROW_INSERTED_DA TE TIMESTAMP NOT NULL,
ROW_INSERTED_PE RSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PER SON VARCHAR(20) NOT NULL,
ROW_UPDATED_DAT E 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
SQL050919130418 870 PRIMARY KEY (MASTER_NAME_IN DEX_PID );
CREATE INDEX EIS.IDX_MNI_H_S ID 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.SQL05091 9130412110 ON
EIS.EIS_MASTER_ NAME_INDEX_PID (MASTER_NAME_IN DEX_PID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
/*************** *************** *************** *************** ***/
-- below table has 438923 rows
CREATE TABLE EIS.EIS_CASE_SU BJECT_DESC
(CASE_SUBJECT_D ESC_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_I D INTEGER,
LU_EYE_COLOR_ID INTEGER,
LU_HAIR_COLOR_I D INTEGER,
LU_TYPE_BUILD_I D INTEGER,
RACE INTEGER,
ETHNICITY INTEGER,
SEX INTEGER,
AGE_CLASSIFICAT ION INTEGER,
BLOOD_TYPE INTEGER,
LU_MARITAL_STAT US_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_I D 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_CONDITI ON_ID INTEGER,
LU_TEETH_ID INTEGER,
LU_BODY_ODOR_ID INTEGER,
LU_BODY_HAIR_ID INTEGER,
LU_HAND_USE_ID INTEGER,
LU_SOBRIETY_TYP E_ID INTEGER,
LU_RESIDENT_STA TUS_ID INTEGER,
ARREST_NUMBER INTEGER,
COMPLAINT_NUMBE R 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_OCCUPAT ION 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_GRA DUATE_YN INTEGER,
GENERAL_EQUIVAL ENCY_DIPLOMA_YN INTEGER,
OTHER_YN INTEGER,
OTHER_DESC VARCHAR(300),
ROW_ACTIVE_INAC TIVE INTEGER NOT NULL,
ROW_INSERTED_DA TE TIMESTAMP NOT NULL,
ROW_INSERTED_PE RSON VARCHAR(20) NOT NULL,
ROW_UPDATED_PER SON VARCHAR(20) NOT NULL,
ROW_UPDATED_DAT E TIMESTAMP NOT NULL )
DATA CAPTURE NONE IN USERSPACE1 NOT LOGGED INITIALLY;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC LOCKSIZE ROW APPEND OFF NOT
VOLATILE;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT PKEIS_CASE_SUBJ EC
PRIMARY KEY (CASE_SUBJECT_D ESC_ID );
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__16789 FOREIGN
KEY (LU_EYE_COLOR_I D )
REFERENCES EIS.EIS_LU_EYE_ COLOR (LU_EYE_COLOR_I D )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__175 FOREIGN
KEY (BIRTH_COUNTRY )
REFERENCES EIS.EIS_LU_COUN TRY (LU_COUNTRY )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__215 FOREIGN
KEY (LU_COMPLEXION_ ID )
REFERENCES EIS.EIS_LU_COMP LEXION (LU_COMPLEXION_ ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_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_SU BJECT_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_SU BJECT_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_SU BJECT_DESC ADD CONSTRAINT R__243 FOREIGN
KEY (ETHNICITY )
REFERENCES EIS.EIS_LU_ETHN ICITY (ETHNICITY )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_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_SU BJECT_DESC ADD CONSTRAINT R__245 FOREIGN
KEY (AGE_CLASSIFICA TION )
REFERENCES EIS.EIS_LU_AGE_ CLASSIFICATION (AGE_CLASSIFICA TION
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__246 FOREIGN
KEY (BIRTH_STATE )
REFERENCES EIS.EIS_LU_STAT E (STATE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__247 FOREIGN
KEY (BLOOD_TYPE )
REFERENCES EIS.EIS_LU_BLOO D_TYPE (BLOOD_TYPE )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__317 FOREIGN
KEY (LU_MARITAL_STA TUS_ID )
REFERENCES EIS.EIS_LU_MARI TAL_STATUS (LU_MARITAL_STA TUS_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__322 FOREIGN
KEY (LU_CAREER_ID )
REFERENCES EIS.EIS_LU_CARE ER (LU_CAREER_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__331 FOREIGN
KEY (LU_SCHOOL_GRAD E_ID )
REFERENCES EIS.EIS_LU_SCHO OL_GRADE (LU_SCHOOL_GRAD E_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__405 FOREIGN
KEY (LU_DEFORMITY_I D )
REFERENCES EIS.EIS_LU_DEFO RMITY (LU_DEFORMITY_I D )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__406 FOREIGN
KEY (LU_SPEECH_ID )
REFERENCES EIS.EIS_LU_SPEE CH (LU_SPEECH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__407 FOREIGN
KEY (LU_ACCENT_ID )
REFERENCES EIS.EIS_LU_ACCE NT (LU_ACCENT_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_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_SU BJECT_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_SU BJECT_DESC ADD CONSTRAINT R__410 FOREIGN
KEY (LU_FACIAL_HAIR _ID )
REFERENCES EIS.EIS_LU_FACI AL_HAIR (LU_FACIAL_HAIR _ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__411 FOREIGN
KEY (LU_FACIAL_HAIR _COLOR_ID )
REFERENCES EIS.EIS_LU_FACI AL_HAIR_COLOR
(LU_FACIAL_HAIR _COLOR_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__412 FOREIGN
KEY (LU_FACIAL_HAIR _LENGTH_ID )
REFERENCES EIS.EIS_LU_FACI AL_HAIR_LENGTH
(LU_FACIAL_HAIR _LENGTH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__413 FOREIGN
KEY (LU_HAIR_TEXTUR E_ID )
REFERENCES EIS.EIS_LU_HAIR _TEXTURE (LU_HAIR_TEXTUR E_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__414 FOREIGN
KEY (LU_HAIR_CONDIT ION_ID )
REFERENCES EIS.EIS_LU_HAIR _CONDITION (LU_HAIR_CONDIT ION_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__415 FOREIGN
KEY (LU_TEETH_ID )
REFERENCES EIS.EIS_LU_TEET H (LU_TEETH_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__416 FOREIGN
KEY (LU_BODY_ODOR_I D )
REFERENCES EIS.EIS_LU_BODY _ODOR (LU_BODY_ODOR_I D )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__417 FOREIGN
KEY (LU_BODY_HAIR_I D )
REFERENCES EIS.EIS_LU_BODY _HAIR (LU_BODY_HAIR_I D )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_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_SU BJECT_DESC ADD CONSTRAINT R__535 FOREIGN
KEY (LU_SOBRIETY_TY PE_ID )
REFERENCES EIS.EIS_LU_SOBR IETY_TYPE (LU_SOBRIETY_TY PE_ID )
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY
OPTIMIZATION;
ALTER TABLE EIS.EIS_CASE_SU BJECT_DESC ADD CONSTRAINT R__536 FOREIGN
KEY (LU_RESIDENT_ST ATUS_ID )
REFERENCES EIS.EIS_LU_RESI DENT_STATUS (LU_RESIDENT_ST ATUS_ID
)
ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE
QUERY OPTIMIZATION;
CREATE INDEX EIS.IDX_CSD_AGE ON EIS.EIS_CASE_SU BJECT_DESC (AGE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_AGE _TO ON EIS.EIS_CASE_SU BJECT_DESC (AGE_TO
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_ARR ON EIS.EIS_CASE_SU BJECT_DESC
(ARREST_NUMBER ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_HIG HT ON EIS.EIS_CASE_SU BJECT_DESC (HEIGHT_IN
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_HIG HT_TO ON EIS.EIS_CASE_SU BJECT_DESC
(HEIGHT_TO_IN ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_WEI GHT ON EIS.EIS_CASE_SU BJECT_DESC (WEIGHT
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.IDX_CSD_WEI GHT_TO ON EIS.EIS_CASE_SU BJECT_DESC
(WEIGHT_TO ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE UNIQUE INDEX EIS.PKEIS_CASE_ SUBJEC ON EIS.EIS_CASE_SU BJECT_DESC
(CASE_SUBJECT_D ESC_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF10EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_EYE_COLOR_I D ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF11EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAIR_COLOR_ ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF12EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_TYPE_BUILD_ ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF13EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC (RACE
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF14EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(ETHNICITY ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF15EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC (SEX
ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF16EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(AGE_CLASSIFICA TION ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF17EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(BIRTH_STATE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF18EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(BLOOD_TYPE ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF19EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_MARITAL_STA TUS_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF21EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_CAREER_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF22EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_SCHOOL_GRAD E_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF23EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_DEFORMITY_I D ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF24EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_SPEECH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF25EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_ACCENT_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF26EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAIR_LENGTH _ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF27EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAIR_STYLE_ ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF28EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_FACIAL_HAIR _ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF29EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_FACIAL_HAIR _COLOR_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF30EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_FACIAL_HAIR _LENGTH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF31EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAIR_TEXTUR E_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF32EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAIR_CONDIT ION_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF33EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_TEETH_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF34EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_BODY_ODOR_I D ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF35EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_BODY_HAIR_I D ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF36EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_HAND_USE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF37EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_SOBRIETY_TY PE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF38EIS_CA SE_SUBJ ON EIS.EIS_CASE_SU BJECT_DESC
(LU_RESIDENT_ST ATUS_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF3EIS_CAS E_SUBJR ON EIS.EIS_CASE_SU BJECT_DESC
(NAME_CASE_ID ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF4EIS_CAS E_SUBJE ON EIS.EIS_CASE_SU BJECT_DESC
(BIRTH_COUNTRY ASC )
PCTFREE 10 DISALLOW REVERSE SCANS;
CREATE INDEX EIS.XIF9EIS_CAS E_SUBJE ON EIS.EIS_CASE_SU BJECT_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_x ref_id, ncx.case_id,
CSD.ACCESSORIES ,CSD.AGE,CSD.AG E_CLASSIFICATIO N,CSD.AGE_TO,CS D.ARREST_NUMBER ,CSD.BIRTH_CITY ,CSD.BIRTH_COUN TRY,CSD.BIRTH_S TATE,CSD.BLOOD_ TYPE,CSD.CASE_S UBJECT_DESC_ID, CSD.COAT,CSD.CO MPLAINT_NUMBER, CSD.CONTACT_LEN SES_YN,CSD.DOB_ DATE,CSD.DRESS_ SKIRT,CSD.ETHNI CITY,CSD.EYES,C SD.GENERAL_EQUI VALENCY_DIPLOMA _YN,CSD.GLASSES _YN,CSD.GLOVES, CSD.HAT,CSD.HEI GHT_FT,CSD.HEIG HT_IN,CSD.HEIGH T_TO_FT,CSD.HEI GHT_TO_IN,CSD.H IGH_SCHOOL_GRAD UATE_YN,CSD.HOM ELESS_YN,CSD.JE WELRY,CSD.JUVEN ILE_YN,CSD.LU_A CCENT_ID,CSD.LU _BODY_HAIR_ID,C SD.LU_BODY_ODOR _ID,CSD.LU_CARE ER_ID,CSD.LU_CO MPLEXION_ID,CSD .LU_DEFORMITY_I D,CSD.LU_EYE_CO LOR_ID,CSD.LU_F ACIAL_HAIR_COLO R_ID,CSD.LU_FAC IAL_HAIR_ID,CSD .LU_FACIAL_HAIR _LENGTH_ID,CSD. LU_HAIR_COLOR_I D,CSD.LU_HAIR_C ONDITION_ID,CSD .LU_HAIR_LENGTH _ID,CSD.LU_HAIR _STYLE_ID,CSD.L U_HAIR_TEXTURE_ ID,CSD.LU_HAND_ USE_ID,CSD.LU_M ARITAL_STATUS_I D,CSD.LU_RESIDE NT_STATUS_ID,CS D.LU_SCHOOL_GRA DE_ID,CSD.LU_SO BRIETY_TYPE_ID, CSD.LU_SPEECH_I D,CSD.LU_TEETH_ ID,CSD.LU_TYPE_ BUILD_ID,CSD.MA SK,CSD.NAME_CAS E_ID,CSD.OTHER_ DESC,CSD.OTHER_ YN,CSD.PANTS,CS D.PRIMARY_OCCUP ATION,CSD.RACE, CSD.ROW_ACTIVE_ INACTIVE,CSD.RO W_INSERTED_DATE ,CSD.ROW_INSERT ED_PERSON,CSD.R OW_UPDATED_DATE ,CSD.ROW_UPDATE D_PERSON,CSD.SE X,CSD.SHIRT,CSD .SHOES,CSD.WEIG HT,CSD.WEIGHT_T O,
COALESCE((SELEC T 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_subjec t_desc csd
ON csd.name_case_i d = nc.name_case_id
LEFT JOIN eis_name_case_x ref ncx
ON ncx.name_case_i d = nc.name_case_id
WHERE nc.master_name_ index_pid IN (SELECT DISTINCT
master_name_ind ex_pid
FROM eis.eis_name_ca se
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_ind ex_pid
FETCH FIRST 500 ROWS ONLY
)
AND nc.row_active_i nactive = 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
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_x ref_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_i d), 0)AS hit
FROM eis_name_case nc JOIN eis_master_name _index mni ON
nc.master_name_ index_id =mni.master_nam e_index_id JOIN
eis_master_name _index_pid pid ON nc.master_name_ index_pid =
pid.master_name _index_pid LEFT JOIN eis_case_subjec t_desc
csd ON csd.name_case_i d =nc.name_case_i d LEFT JOIN
eis_name_case_x ref ncx ON ncx.name_case_i d =nc.name_case_i d
WHERE nc.master_name_ index_pid IN (
SELECT DISTINCT master_name_ind ex_pid
FROM eis.eis_name_ca se
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_ind ex_pid
FETCH FIRST 500 ROWS ONLY)AND nc.row_active_i nactive =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_CA SE ID = 2,220
| | | | Index Scan: Name = SYSIBM.SQL05091 9123932470 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_IND EX_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_CA SE_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_CA SE ID = 2,220
| | | Index Scan: Name = EIS.NC_ROW_ACT_ INACT ID = 6
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: ROW_ACTIVE_INAC TIVE (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_SU BJECT_DESC ID = 2,48
| | | Index Scan: Name = EIS.XIF3EIS_CAS E_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_x ref_id, ncx.case_id,
CSD.*, COALESCE((SELEC T DISTINCT nc_id
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
WHERE u.nc_id =nc.name_case_i d), 0) AS hit
FROM eis_name_case nc JOIN eis_master_name _index mni ON
nc.master_name_ index_id =mni.master_nam e_index_id JOIN
eis_master_name _index_pid pid ON
nc.master_name_ index_pid =pid.master_nam e_index_pid LEFT
JOIN eis_case_subjec t_desc csd ON csd.name_case_i d =
nc.name_case_id LEFT JOIN eis_name_case_x ref ncx ON
ncx.name_case_i d =nc.name_case_i d
WHERE nc.master_name_ index_pid IN
(SELECT DISTINCT master_name_ind ex_pid
FROM eis.eis_name_ca se
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_ind ex_pid
FETCH FIRST 500 ROWS ONLY) AND nc.row_active_i nactive =
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_CA SE 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_IND EX_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_CA SE ID = 2,220
| | Index Scan: Name = EIS.XIF2EIS_NAM E_CASE ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_IND EX_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_IND EX_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_MAST ER_NAME ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_IND EX_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_MASTE R_NAME ID = 2
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: MASTER_NAME_IND EX_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_SU BJECT_DESC ID = 2,48
| | Index Scan: Name = EIS.XIF3EIS_CAS E_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_CA SE_XREF ID = 2,63
| | Index Scan: Name = EIS.XIF1EIS_NAM E_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
"romicva" <ro*****@gmail. com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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_x ref_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_i d), 0)AS hit
FROM eis_name_case nc JOIN eis_master_name _index mni ON
nc.master_name_ index_id =mni.master_nam e_index_id JOIN
eis_master_name _index_pid pid ON nc.master_name_ index_pid =
pid.master_name _index_pid LEFT JOIN eis_case_subjec t_desc
csd ON csd.name_case_i d =nc.name_case_i d LEFT JOIN
eis_name_case_x ref ncx ON ncx.name_case_i d =nc.name_case_i d
WHERE nc.master_name_ index_pid IN (
SELECT DISTINCT master_name_ind ex_pid
FROM eis.eis_name_ca se
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_ind ex_pid
FETCH FIRST 500 ROWS ONLY)AND nc.row_active_i nactive =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_x ref_id, ncx.case_id,
CSD.*, COALESCE((SELEC T DISTINCT nc_id
FROM (
(SELECT NC_ID
FROM t_rc
FETCH FIRST 2500 ROWS ONLY)) U
WHERE u.nc_id =nc.name_case_i d), 0) AS hit
FROM eis_name_case nc JOIN eis_master_name _index mni ON
nc.master_name_ index_id =mni.master_nam e_index_id JOIN
eis_master_name _index_pid pid ON
nc.master_name_ index_pid =pid.master_nam e_index_pid LEFT
JOIN eis_case_subjec t_desc csd ON csd.name_case_i d =
nc.name_case_id LEFT JOIN eis_name_case_x ref ncx ON
ncx.name_case_i d =nc.name_case_i d
WHERE nc.master_name_ index_pid IN
(SELECT DISTINCT master_name_ind ex_pid
FROM eis.eis_name_ca se
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_ind ex_pid
FETCH FIRST 500 ROWS ONLY) AND nc.row_active_i nactive =
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_SUBJEC T_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_SUBJEC T_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.
In article <11************ *********@z14g2 000cwz.googlegr oups.com>, ro*****@gmail.c om says... Hello Mark Yudkin,
I believe I saw your post on this subject, where you said that
"...feedlin g 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Paul |
last post by:
I'm confused about something I did this week. I have an 8.1.7.4 database with tables partitioned by dates. I recently dropped my maxvalue tablespace, added a few more partitions for up-coming dates, then put the maxval partition back on. (I save the data that was in the maxval partition first and put it back in when I was done.
These tables also contains BLOBS.
All of my indexes were at a status of either "Valid" or "Usable" in the case of...
|
by: Chris |
last post by:
Any help would be appreciated.
I am running a script that does the following in succession.
1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures
I would like to improve the performance of the analysis in step 4 by
|
by: Steve_CA |
last post by:
Hi,
The more I read, the more confused I'm getting ! (no wonder they say
ignorance is bliss)
I just got back from the bookstore and was flipping through some SQL Server
Administration books.
One says, that to get the best query performance, youi do two things:
|
by: pheonix1t |
last post by:
hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).
The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
|
by: mairhtin o'feannag |
last post by:
Hello,
I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created indexes
are taking up huge amounts of space. I used the following query to
determine *WHICH* indexes are in the tablespace in question. Now I just
need to get the size of each of the indexes returned.
select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
| | |
by: Jeff |
last post by:
This is the first time that I remember ever having too many indexes on a
table, but it has happened.
I have en employees table and store in many places, on tables, the id of the
employee that performed some action. Yes, I know, that could be in an audit
trail but it isn't. For example, who printed a sales order, who processed it
etc is stored on the sales orders table.
Well, I have run out of indexes on the employees table when trying...
|
by: boa |
last post by:
I'm currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...
Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and
place tables and index data...
|
by: lesperancer |
last post by:
you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come
does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?
does access2000 / 2003 allow more indexes per table ?
|
by: MMcCarthy |
last post by:
The more data you include in your tables the more you will need to have indexes to search and sort that data. However, there is a balance between having enough indexes and too many. Too many indexes will slow down the speed of updates on your records.
Access presets a number of Indexes for you. If you look in Tools ... Options under the Tables/Queries tab you will see that under "Auto Index on Import/Create" there is a list as follows:
...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
