Re: Indexing Problem
Do a "update statistics" and then re-run your SQL again (with set explain
on) ...
hpandu wrote:
> 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