Could somebody explain to me why a particular table analyze is taking so
long? This is a large table, but still it seems the time to analyze this
table is out of proportion. These are the statement and the times in seconds
they take:
CREATE TABLE [ADDRESS] ([ADDRESS_ID] INTEGER PRIMARY KEY, [ADDRESS_LINE_1]
TEXT, [POSTCODE] TEXT, [UPDATED_DATE] INTEGER, [ADDRESS_LINE_2] TEXT,
[ADDRESS_LINE_3] TEXT, [ADDRESS_LINE_4] TEXT, [ADDRESS_LINE_5] TEXT)
0.95 INSERT INTO ADDRESS(ADDRESS_ID, ADDRESS_LINE_1, POSTCODE,
UPDATED_DATE, ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4,
ADDRESS_LINE_5) values(:ADDRESS_ID, :ADDRESS_LINE_1, :POSTCODE,
:UPDATED_DATE, :ADDRESS_LINE_2, :ADDRESS_LINE_3, :ADDRESS_LINE_4,
:ADDRESS_LINE_5) SELECT ADDRESS_ID, ADDRESS_LINE_1, POSTCODE, UPDATED_DATE,
ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4, ADDRESS_LINE_5 FROM ADDRESS
0.12 CREATE INDEX IDX1_ADDRESS_ADDRESS_LINE_1 ON ADDRESS(ADDRESS_LINE_1)
0.14 CREATE INDEX IDX2_ADDRESS_POSTCODE ON ADDRESS(POSTCODE)
0.11 CREATE INDEX IDX3_ADDRESS_UPDATED_DATE ON ADDRESS(UPDATED_DATE)
0.14 CREATE INDEX IDX4_ADDRESS_ADDRESS_LINE_2 ON ADDRESS(ADDRESS_LINE_2)
0.12 CREATE INDEX IDX5_ADDRESS_ADDRESS_LINE_3 ON ADDRESS(ADDRESS_LINE_3)
0.12 CREATE INDEX IDX6_ADDRESS_ADDRESS_LINE_4 ON ADDRESS(ADDRESS_LINE_4)
0.11 CREATE INDEX IDX7_ADDRESS_ADDRESS_LINE_5 ON ADDRESS(ADDRESS_LINE_5)
0.05 analyze ADDRESS
CREATE TABLE [ADDRESSLINK] ([ADDRESS_LINK_ID] INTEGER PRIMARY KEY,
[ADDRESS_ID] INTEGER, [FOREIGN_ID] INTEGER, [ADDRESS_TYPE] INTEGER,
[ADDRESS_LINK_TYPE] INTEGER)
0.74 INSERT INTO ADDRESSLINK(ADDRESS_LINK_ID, ADDRESS_ID, FOREIGN_ID,
ADDRESS_TYPE, ADDRESS_LINK_TYPE) values(:ADDRESS_LINK_ID, :ADDRESS_ID,
:FOREIGN_ID, :ADDRESS_TYPE, :ADDRESS_LINK_TYPE) SELECT ADDRESS_LINK_ID,
ADDRESS_ID, FOREIGN_ID, ADDRESS_TYPE, ADDRESS_LINK_TYPE FROM ADDRESSLINK
0.12 CREATE INDEX IDX1_ADDRESSLINK_ADDRESS_ID ON ADDRESSLINK(ADDRESS_ID)
0.12 CREATE INDEX IDX2_ADDRESSLINK_FOREIGN_ID ON ADDRESSLINK(FOREIGN_ID)
0.03 analyze ADDRESSLINK
CREATE TABLE [PHONE] ([PHONE_ID] INTEGER PRIMARY KEY, [PHONE_NUMBER]
TEXT, [UPDATED_DATE] INTEGER)
0.61 INSERT INTO PHONE(PHONE_ID, PHONE_NUMBER, UPDATED_DATE)
values(:PHONE_ID, :PHONE_NUMBER, :UPDATED_DATE) SELECT PHONE_ID,
PHONE_NUMBER, UPDATED_DATE FROM PHONE
0.12 CREATE INDEX IDX1_PHONE_PHONE_NUMBER ON PHONE(PHONE_NUMBER)
0.12 CREATE INDEX IDX2_PHONE_UPDATED_DATE ON PHONE(UPDATED_DATE)
0.02 analyze PHONE
CREATE TABLE [PHONELINK] ([PHONE_LINK_ID] INTEGER PRIMARY KEY,
[PHONE_ID] TEXT, [FOREIGN_ID] INTEGER, [PHONE_LINK_TYPE] TEXT,
[PHONE_TYPE_ID] TEXT)
0.8 INSERT INTO PHONELINK(PHONE_LINK_ID, PHONE_ID, FOREIGN_ID,
PHONE_LINK_TYPE, PHONE_TYPE_ID) values(:PHONE_LINK_ID, :PHONE_ID,
:FOREIGN_ID, :PHONE_LINK_TYPE, :PHONE_TYPE_ID) SELECT PHONE_LINK_ID,
PHONE_ID, FOREIGN_ID, PHONE_LINK_TYPE, PHONE_TYPE_ID FROM PHONELINK
0.2 CREATE INDEX IDX1_PHONELINK_PHONE_ID ON PHONELINK(PHONE_ID)
0.12 CREATE INDEX IDX2_PHONELINK_FOREIGN_ID ON PHONELINK(FOREIGN_ID)
0.14 CREATE INDEX IDX3_PHONELINK_PHONE_LINK_TYPE ON
PHONELINK(PHONE_LINK_TYPE)
0.16 CREATE INDEX IDX4_PHONELINK_PHONE_TYPE_ID ON
PHONELINK(PHONE_TYPE_ID)
0.05 analyze PHONELINK
CREATE TABLE [PHONETYPE] ([PHONE_TYPE_ID] TEXT,
[PHONE_TYPE_DESCRIPTION] TEXT, [DORMANT_FLAG] INTEGER)
0.05 INSERT INTO PHONETYPE(PHONE_TYPE_ID, PHONE_TYPE_DESCRIPTION,
DORMANT_FLAG) values(:PHONE_TYPE_ID, :PHONE_TYPE_DESCRIPTION, :DORMANT_FLAG)
SELECT PHONE_TYPE_ID, PHONE_TYPE_DESCRIPTION, DORMANT_FLAG FROM PHONETYPE
CREATE TABLE [ENTRY] ([ENTRY_ID] INTEGER PRIMARY KEY, [ADDED_BY]
INTEGER, [UPDATED_BY] INTEGER, [READ_CODE] TEXT, [TERM_ID] TEXT,
[ENCOUNTER_ID] INTEGER, [SECURE_TYPE] INTEGER, [ADDED_DATE] INTEGER,
[UPDATED_DATE] INTEGER, [FREE_TEXT] TEXT, [DORMANT_FLAG] INTEGER,
[ENTRY_TYPE] INTEGER, [START_DATE] INTEGER, [END_DATE] INTEGER, [PROBLEM_ID]
INTEGER, [ENTRY_FLAGS] TEXT, [PATIENT_ID] INTEGER, [SUBJECT_TYPE] TEXT,
[TERM_TEXT] TEXT)
294.94 INSERT INTO ENTRY(ENTRY_ID, ADDED_BY, UPDATED_BY, READ_CODE,
TERM_ID, ENCOUNTER_ID, SECURE_TYPE, ADDED_DATE, UPDATED_DATE, FREE_TEXT,
DORMANT_FLAG, ENTRY_TYPE, START_DATE, END_DATE, PROBLEM_ID, ENTRY_FLAGS,
PATIENT_ID, SUBJECT_TYPE, TERM_TEXT) values(:ENTRY_ID, :ADDED_BY,
:UPDATED_BY, :READ_CODE, :TERM_ID, :ENCOUNTER_ID, :SECURE_TYPE, :ADDED_DATE,
:UPDATED_DATE, :FREE_TEXT, :DORMANT_FLAG, :ENTRY_TYPE, :START_DATE,
:END_DATE, :PROBLEM_ID, :ENTRY_FLAGS, :PATIENT_ID, :SUBJECT_TYPE,
:TERM_TEXT) SELECT ENTRY_ID, ADDED_BY, UPDATED_BY, READ_CODE, TERM_ID,
ENCOUNTER_ID, SECURE_TYPE, ADDED_DATE, UPDATED_DATE, FREE_TEXT,
DORMANT_FLAG, ENTRY_TYPE, START_DATE, END_DATE, PROBLEM_ID, ENTRY_FLAGS,
PATIENT_ID, SUBJECT_TYPE, TERM_TEXT FROM ENTRY
51.02 CREATE INDEX IDX1_ENTRY_ADDED_BY ON ENTRY(ADDED_BY)
39.77 CREATE INDEX IDX2_ENTRY_UPDATED_BY ON ENTRY(UPDATED_BY)
48.12 CREATE INDEX IDX3_ENTRY_READ_CODE ON ENTRY(READ_CODE)
27.59 CREATE INDEX IDX4_ENTRY_ENCOUNTER_ID ON ENTRY(ENCOUNTER_ID)
32.19 CREATE INDEX IDX5_ENTRY_ADDED_DATE ON ENTRY(ADDED_DATE)
38.84 CREATE INDEX IDX6_ENTRY_UPDATED_DATE ON ENTRY(UPDATED_DATE)
39.11 CREATE INDEX IDX7_ENTRY_FREE_TEXT ON ENTRY(FREE_TEXT)
40.91 CREATE INDEX IDX8_ENTRY_ENTRY_TYPE ON ENTRY(ENTRY_TYPE)
37.8 CREATE INDEX IDX9_ENTRY_START_DATE ON ENTRY(START_DATE)
39.84 CREATE INDEX IDX10_ENTRY_PROBLEM_ID ON ENTRY(PROBLEM_ID)
41.72 CREATE INDEX IDX11_ENTRY_ENTRY_FLAGS ON ENTRY(ENTRY_FLAGS)
35.08 CREATE INDEX IDX12_ENTRY_PATIENT_ID ON ENTRY(PATIENT_ID)
40.01 CREATE INDEX IDX13_ENTRY_SUBJECT_TYPE ON ENTRY(SUBJECT_TYPE)
63.42 CREATE INDEX IDX14_ENTRY_TERM_TEXT ON ENTRY(TERM_TEXT)
1629.05 analyze ENTRY
CREATE TABLE [AUTHORISATION] ([ENTRY_ID] INTEGER,
[NUMBER_AUTHORISED] INTEGER, [MARKED_FOR_ISSUE_FLAG] INTEGER,
[AUTHORISATION_TYPE] INTEGER, [EMERGENCY_AUTHORISATION] INTEGER,
[MINIMUM_DAYS_BETWEEN_ISSUES] INTEGER, [NUMBER_OF_ISSUES] INTEGER,
[TREATMENT_ID] INTEGER, [CURRENT_AUTHORISATION_FLAG] INTEGER,
[MLEX_PRODUCT_ID] INTEGER, [MLEX_FORM_ID] INTEGER, [MLEX_PACK_ID] INTEGER,
[DOSAGE_INSTRUCTIONS] TEXT, [SHORT_DOSE] TEXT, [NUMBER_OF_PACKS] INTEGER,
[SUPPLY_AMOUNT] REAL, [SUPPLY_UNITS] TEXT, [PRIVATE_FLAG] INTEGER,
[PATIENT_ID] INTEGER, [LAST_ISSUED_DATE] INTEGER, [AUTHORISED_DATE] INTEGER,
[BATCH_ISSUE_FLAG] INTEGER)
39.7 INSERT INTO AUTHORISATION(ENTRY_ID, NUMBER_AUTHORISED,
MARKED_FOR_ISSUE_FLAG, AUTHORISATION_TYPE, EMERGENCY_AUTHORISATION,
MINIMUM_DAYS_BETWEEN_ISSUES, NUMBER_OF_ISSUES, TREATMENT_ID,
CURRENT_AUTHORISATION_FLAG, MLEX_PRODUCT_ID, MLEX_FORM_ID, MLEX_PACK_ID,
DOSAGE_INSTRUCTIONS, SHORT_DOSE, NUMBER_OF_PACKS, SUPPLY_AMOUNT,
SUPPLY_UNITS, PRIVATE_FLAG, PATIENT_ID, LAST_ISSUED_DATE, AUTHORISED_DATE,
BATCH_ISSUE_FLAG) values(:ENTRY_ID, :NUMBER_AUTHORISED,
:MARKED_FOR_ISSUE_FLAG, :AUTHORISATION_TYPE, :EMERGENCY_AUTHORISATION,
:MINIMUM_DAYS_BETWEEN_ISSUES, :NUMBER_OF_ISSUES, :TREATMENT_ID,
:CURRENT_AUTHORISATION_FLAG, :MLEX_PRODUCT_ID, :MLEX_FORM_ID, :MLEX_PACK_ID,
:DOSAGE_INSTRUCTIONS, :SHORT_DOSE, :NUMBER_OF_PACKS, :SUPPLY_AMOUNT,
:SUPPLY_UNITS, :PRIVATE_FLAG, :PATIENT_ID, :LAST_ISSUED_DATE,
:AUTHORISED_DATE, :BATCH_ISSUE_FLAG) SELECT ENTRY_ID, NUMBER_AUTHORISED,
MARKED_FOR_ISSUE_FLAG, AUTHORISATION_TYPE, EMERGENCY_AUTHORISATION,
MINIMUM_DAYS_BETWEEN_ISSUES, NUMBER_OF_ISSUES, TREATMENT_ID,
CURRENT_AUTHORISATION_FLAG, MLEX_PRODUCT_ID, MLEX_FORM_ID, MLEX_PACK_ID,
DOSAGE_INSTRUCTIONS, SHORT_DOSE, NUMBER_OF_PACKS, SUPPLY_AMOUNT,
SUPPLY_UNITS, PRIVATE_FLAG, PATIENT_ID, LAST_ISSUED_DATE, AUTHORISED_DATE,
BATCH_ISSUE_FLAG FROM AUTHORISATION
2.27 CREATE INDEX IDX0_AUTHORISATION_ENTRY_ID ON AUTHORISATION(ENTRY_ID)
2.59 CREATE INDEX IDX1_AUTHORISATION_NUMBER_OF_ISSUES ON
AUTHORISATION(NUMBER_OF_ISSUES)
2.38 CREATE INDEX IDX2_AUTHORISATION_TREATMENT_ID ON
AUTHORISATION(TREATMENT_ID)
3.41 CREATE INDEX IDX3_AUTHORISATION_DOSAGE_INSTRUCTIONS ON
AUTHORISATION(DOSAGE_INSTRUCTIONS)
2.38 CREATE INDEX IDX4_AUTHORISATION_SHORT_DOSE ON
AUTHORISATION(SHORT_DOSE)
2.66 CREATE INDEX IDX5_AUTHORISATION_SUPPLY_AMOUNT ON
AUTHORISATION(SUPPLY_AMOUNT)
2.78 CREATE INDEX IDX6_AUTHORISATION_SUPPLY_UNITS ON
AUTHORISATION(SUPPLY_UNITS)
2.45 CREATE INDEX IDX7_AUTHORISATION_PATIENT_ID ON
AUTHORISATION(PATIENT_ID)
2.62 CREATE INDEX IDX8_AUTHORISATION_LAST_ISSUED_DATE ON
AUTHORISATION(LAST_ISSUED_DATE)
2.44 CREATE INDEX IDX9_AUTHORISATION_AUTHORISED_DATE ON
AUTHORISATION(AUTHORISED_DATE)
0.95 analyze AUTHORISATION
The table with the long analyze is the table ENTRY and this has indeed a few
million rows, but the table AUTHORISATION has a bit less than 1 million rows
and the analyze there is proportionally much faster.
Are these times just normal and is this just how it is or is there something
that I should be aware of?
Thanks for any advice.
RBS
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
