RE: Indexing Problem

Harish Please confirm that both tables have 13000 rows in them? Has update statistics medium been run on both tables since they were last loaded? Are the distribution patterns of tm_id identical for both tables? If the answer is no to any of these, this may explain the difference. If the answer is yes to all the abover questions ... What is actually the problem? Does one delete take alot longer than the other? Does it matter that one uses an index and the other doesnt (13000 rows is not very much)? Murray Wood PS You did not say what version of Informix, platform you are using. -----Original Message----- From: hpandu [SMTP:hpandu@miel.mot.com] Sent: Wednesday, June 30, 1999 6:31 AM To: informix-list@iiug.org Subject: Indexing Problem Hi, 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