It's not using the index (again!)

The optimizer cannot use the indexes on source_table for column2 values because you are applying the YEAR(), MONTH() & DAY() functions to that column to build a comparison string. In addition, you only have MEDIUM statistics on the index keys which is insufficient for best use of the optimizer's intelligence. Thirdly, as you state that there are few or no rows in source_table which means that the optimizer is doing the best job it can. If there are few rows in the table reading the few data pages in a sequential scan is cheaper than reading several index pages and then, statistically, having to read most of the data pages anyway to process the selected rows to get the values of column3. I think that the query plan developed by the optimizer may be the best possible for the current status of the database. If the size of the tables and the key distribution changes so will the query plan. HOWEVER, you will have to generate a fuller set of statistics and Data Distributions. The recommended set of UPDATE STATISTICS commands for these tables, based on the IDS 7.2x release notes, to provide as complete set of stats as possible at the least cost, is: UPDATE STATISTICS MEDIUM FOR TABLE source_table DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE source_table(source_column1) DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE source_table( source_column1, source_column2); UPDATE STATISTICS MEDIUM FOR TABLE target_table DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE target_table(source_column1) DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE target_table( source_column1, source_column2); Art S. Kagel Stephen Roach wrote: > > Hi folks > > I'm doing an update to a fairly large table and the indexes don't seem > to be being used. The set up is something like this: > > CREATE TABLE source_table(source_column1 CHAR(7), > source_column2 CHAR(10), > source_column3 CHAR(80), > status CHAR(1));> > CREATE INDEX src_pk ON source_table (source_column1, > source_column2);> > UPDATE STATISTICS MEDIUM FOR TABLE source_table;> > CREATE TABLE target_table(target_column1 CHAR(7), > target_column2 DATETIME YEAR TO DAY, > target_column3 CHAR(80));> > CREATE INDEX tar_pk ON target_table (target_column1, > target_column2);> > UPDATE STATISTICS MEDIUM FOR TABLE target_table;> > UPDATE target_tab SET > ( > target_column3 > )> = ((SELECT source_column3 > FROM source_table src > WHERE target_table.target_column1 = src.source_column1 > AND target_table.target_column2 = > MDY(MONTH(src.source_column2[5, 6]), > DAY(src.source_column2[7, 8]), > YEAR(src.source_column2[1, 4)) > AND src.status = "U")) > WHERE EXISTS > ( SELECT 1 > FROM source_table src > WHERE target_table.target_column1 = src.source_column1 > AND target_table.target_column2 = > MDY(MONTH(src.source_column2[5, 6]), > DAY(src.source_column2[7, 8]), > YEAR(src.source_column2[1, 4)) > AND src.status = "U"); > > sqexplain.out: > > Estimated Cost 37210 > Estimated # of Rows Returned: 16045 > > 1) user.target_table: INDEX PATH > > Filters: EXISTS <subquery> > > (1) Index Keys: target_column1 target_column2 > > Subquery: > --------- > Estimated cost: 3 > Estimated # of Rows Returned: 283594 > > 1) user.src: SEQUENTIAL SCAN > > Filters: <the join from the SET = above - I think> > > Subquery: > --------- > Estimated cost: 3 > Estimated # of Rows Returned: 283594 > > 1) user.src: SEQUENTIAL SCAN > > Filters: <the join from the WHERE EXISTS above - I think> > > Table target_table has about 35,000 rows and this UPDATE still runs > the full scans even when source_table is empty! > > Is my problem the date conversion? How about I left this out of the > index - even 'though this is part of the primary key? > > Phew! > > TIA > > --------------------------------------------------------- > Steve Roach: Remove NOSPAM from address to reply: > steve_roach@NOSPAMibm.net > steve_roach@NOSPAMhotmail.com

Hi folks I'm doing an update to a fairly large table and the indexes don't seem to be being used. The set up is something like this: CREATE TABLE source_table(source_column1 CHAR(7), source_column2 CHAR(10), source_column3 CHAR(80), status CHAR(1)); CREATE INDEX src_pk ON source_table (source_column1, source_column2); UPDATE STATISTICS MEDIUM FOR TABLE source_table; CREATE TABLE target_table(target_column1 CHAR(7), target_column2 DATETIME YEAR TO DAY, target_column3 CHAR(80)); CREATE INDEX tar_pk ON target_table (target_column1, target_column2); UPDATE STATISTICS MEDIUM FOR TABLE target_table; UPDATE target_tab SET ( target_column3 )= ((SELECT source_column3 FROM source_table src WHERE target_table.target_column1 = src.source_column1 AND target_table.target_column2 = MDY(MONTH(src.source_column2[5, 6]), DAY(src.source_column2[7, 8]), YEAR(src.source_column2[1, 4)) AND src.status = "U")) WHERE EXISTS ( SELECT 1 FROM source_table src WHERE target_table.target_column1 = src.source_column1 AND target_table.target_column2 = MDY(MONTH(src.source_column2[5, 6]), DAY(src.source_column2[7, 8]), YEAR(src.source_column2[1, 4)) AND src.status = "U"); sqexplain.out: Estimated Cost 37210 Estimated # of Rows Returned: 16045 1) user.target_table: INDEX PATH Filters: EXISTS <subquery> (1) Index Keys: target_column1 target_column2 Subquery: --------- Estimated cost: 3 Estimated # of Rows Returned: 283594 1) user.src: SEQUENTIAL SCAN Filters: <the join from the SET = above - I think> Subquery: --------- Estimated cost: 3 Estimated # of Rows Returned: 283594 1) user.src: SEQUENTIAL SCAN Filters: <the join from the WHERE EXISTS above - I think> Table target_table has about 35,000 rows and this UPDATE still runs the full scans even when source_table is empty! Is my problem the date conversion? How about I left this out of the index - even 'though this is part of the primary key? Phew! TIA --------------------------------------------------------- Steve Roach: Remove NOSPAM from address to reply: steve_roach@NOSPAMibm.net steve_roach@NOSPAMhotmail.com