Re: Indexing Problem

UPDATE STATISTICS? From: hpandu <hpandu@miel.mot.com> > >I am facing some different problem for two table having similar >definition on indexing. >Can any of you clear me how I can tackle this. > >The problem: > >TWO IDENTICAL TABLE behaving differently: >----------------------------------------- > >In the database, we have table "effort_description" with four >fields. (with the three foriegn key indexed proj_id, task_id, and >tm_id) and fourth field week_start with composite index.(which is shown >below). The size of the table is 13,000 rows. > > >------------------------------------------------------------------------ > >INFO - effort_description: Columns Indexes Privileges References >.... >Display information about indexes for the columns in a table. > >----------------------- timetool@db_ashwini ---- Press CTRL-W for Help >--- > >Index name Owner Type Cluster Columns > > 119_67 informix unique No proj_id > >task_id > >tm_id > >week_start > > 119_117 informix dupls No proj_id > > 119_118 informix dupls No task_id > > 119_119 informix dupls No tm_id > >------------------------------------------------------------------------ > > > >The other table is "week_effort" table, we have the same definition for >indexes. >which is given below. And "week_effort" with 12 fields. This table has >three foreign key indexed, which are proj_id, task_id and tm_id. ( >fourth field start_date with composite index) with total fields in the >table is 12. > >------------------------------------------------------------------------ > >INFO - week_effort: Columns Indexes Privileges References Status >.... >Display information about indexes for the columns in a table. > >----------------------- timetool@db_ashwini ---- Press CTRL-W for Help >-- > >Index name Owner Type Cluster Columns > > 127_138 informix unique No proj_id > task_id > tm_id > >start_date > > 127_135 informix dupls No proj_id > > 127_136 informix dupls No task_id > > 127_137 informix dupls No tm_id > >----------------------------------------------------------------------- > > > > I did following query execution on both the tables. > >set explain on; >delete from effort_description where tm_id=1000; >delete from week_effort where tm_id=1000;> > >In sqexplain.out, >------------------ > > >QUERY: >------ >delete from effort_description where tm_id=1000> >Estimated Cost: 2 >Estimated # of Rows Returned: 2 > >1) informix.effort_description: INDEX PATH > > (1) Index Keys: tm_id > Lower Index Filter: informix.effort_description.tm_id = 1000 > > >QUERY: >------ >delete from week_effort where tm_id=1000> >Estimated Cost: 2 >Estimated # of Rows Returned: 2 > >1) informix.week_effort: SEQUENTIAL SCAN > > Filters: informix.week_effort.tm_id = 1000 > >------------------------------------------------------------ > >I found the delete operation in week_effort does in SEQUENTIAL SCAN. So >I tried to make in INDEX PATH but couldn't succeed. Eventhough the >definition of both the tables are same, it didn't work in similar way. > >Pls. let know, how this can be rectified..? > > >Thanks in Advance, >Harish Pandurangan >hpandu@miel.mot.com > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com