IDS Forum

SQL/Optimizer Question

Posted By: SUSAN JONES
Date: Tuesday, 11 September 2012, at 5:01 p.m.

AIX 5.3
IBM Informix Dynamic Server Version 11.50.FC9

Why does the optimizer conduct a sequential scan on this query even though there is an index on the column i_case_relation_cd?

SELECT * FROM case_to_case_relation WHERE i_case_relation_cd = 3

This is the execution plan:
-- execution plan at /home/ifxcmgt/sqexplain.out at 10.64.21.10
QUERY: (OPTIMIZATION TIMESTAMP: 09-11-2012 16:30:44)
------
SELECT * FROM case_to_case_relation WHERE i_case_relation_cd is not null
and i_case_relation_cd = 3

Estimated Cost: 5408
Estimated # of Rows Returned: 97541

1) ifxcmgt.case_to_case_relation: SEQUENTIAL SCAN

Filters: (ifxcmgt.case_to_case_relation.i_case_relation_cd
IS NOT NULL AND ifxcmgt.case_to_case_relation.i_case_relation_cd = 3 )

This is the structure of this table including indexes:

eate table 'ifxcmgt'.case_to_case_relation (

i_case_associated_case_id SERIAL not null,

i_case_id INT not null,

i_related_case_id INT not null,

i_case_relation_cd INT not null,

d_start_date DATE,

c_start_time CHAR(8),

d_end_date DATE,

c_end_time CHAR(8),

i_case_relation_status_cd INT,

d_status_date DATE,

c_voided CHAR(1) not null,

c_create_user CHAR(8) not null,

i_create_location_cd INT not null,

i_create_department_cd INT not null,

dt_create_datetime DATETIME YEAR TO SECOND not null,

c_update_user CHAR(8),

i_update_location_cd INT,

i_update_department_cd INT,

dt_update_datetime DATETIME YEAR TO SECOND
)
extent size 4700 next size 1608
lock mode row;

create index 'ifxcmgt'.fk_77 on 'ifxcmgt'.case_to_case_relation

(

i_case_id

)

in ccmsidxdbs01;

create index 'ifxcmgt'.fk_78 on 'ifxcmgt'.case_to_case_relation

(

i_related_case_id

)

in ccmsidxdbs01;

create index 'ifxcmgt'.fk_79 on 'ifxcmgt'.case_to_case_relation

(

i_case_relation_cd

)

in ccmsidxdbs01;

create index 'ifxcmgt'.fk_80 on 'ifxcmgt'.case_to_case_relation

(

i_case_relation_status_cd

)

in ccmsidxdbs01;

create unique index 'ifxcmgt'.pk_42 on 'ifxcmgt'.case_to_case_relation

(

i_case_associated_case_id

)

in ccmsidxdbs01;

alter table 'ifxcmgt'.case_to_case_relation add constraint foreign key

(i_case_id)

references 'ifxcmgt'.case_master

(i_case_id)

constraint fk_case_to_case779;

alter table 'ifxcmgt'.case_to_case_relation add constraint foreign key

(i_related_case_id)

references 'ifxcmgt'.case_master

(i_case_id)

constraint fk_case_to_case867;

alter table 'ifxcmgt'.case_to_case_relation add constraint primary key

(i_case_associated_case_id)

constraint pk_case_to_case_relation24;

alter table 'ifxcmgt'.case_to_case_relation add constraint check

((c_voided IN ('Y' ,'N' )))

constraint c235_1310;

alter table 'ifxcmgt'.case_to_case_relation add constraint foreign key

(i_case_relation_status_cd)

references 'ifxcmgt'.cd_case_relation_status

(i_case_relation_status_cd)

constraint fk_case_to_case209;

alter table 'ifxcmgt'.case_to_case_relation add constraint foreign key

(i_case_relation_cd)

references 'ifxcmgt'.cd_case_relation

(i_case_relation_cd)

constraint fk_case_to_case975;

Messages In This Thread

• SQL/Optimizer Question
  SUSAN JONES -- Tuesday, 11 September 2012, at 5:01 p.m.
  • Re: SQL/Optimizer Question
    david@smooth1.co.uk -- Tuesday, 11 September 2012, at 5:14 p.m.
  • Re: SQL/Optimizer Question
    Art Kagel -- Tuesday, 11 September 2012, at 5:19 p.m.
  • RE: SQL/Optimizer Question
    Dharmendra Sharma -- Tuesday, 11 September 2012, at 6:02 p.m.
  • Re: SQL/Optimizer Question
    JACQUES RENAUT -- Tuesday, 11 September 2012, at 6:21 p.m.
  • Re: SQL/Optimizer Question
    SUSAN JONES -- Wednesday, 12 September 2012, at 7:01 a.m.
    • Re: SQL/Optimizer Question
      Fernando Nunes -- Wednesday, 12 September 2012, at 7:13 a.m.
      • Re: SQL/Optimizer Question
        MARK JALKIEWICZ -- Monday, 17 September 2012, at 11:45 a.m.
    • Re: SQL/Optimizer Question
      John Miller iii -- Wednesday, 12 September 2012, at 10:51 a.m.
  • Re: SQL/Optimizer Question
    SUSAN JONES -- Wednesday, 12 September 2012, at 10:20 a.m.
    • Re: SQL/Optimizer Question
      david@smooth1.co.uk -- Wednesday, 12 September 2012, at 3:58 p.m.
    • Re: SQL/Optimizer Question
      Fernando Nunes -- Wednesday, 12 September 2012, at 4:27 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.