SQL/Optimizer Question
A user asked why Informix 11.50 chose a sequential scan for SELECT * FROM case_to_case_relation WHERE i_case_relation_cd = 3 despite an index on that column. Replies suggested stale/missing statistics, an extra "IS NOT NULL" filter in the sqexplain output, or low selectivity. After refreshing statistics and rechecking, the poster found the value 3 accounted for over 90% of the rows, so the index was useless for SELECT * — a count(*) did use the index. John Miller gave I/O math showing a sequential (large-block) scan is far cheaper than ~49,000 random index/data page reads. Resolved as expected optimizer behaviour.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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;
On 11 September 2012 at 22:01 SUSAN JONES <sjones@clerk.org> wrote:
> 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;
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Because the statistics are out of date or non-existent.
Susan:
The sqexplain output says that there are 97,541 rows with that value for
the i_case_relation_cd table. How many are there in actuallity?
Are the data distributions for this table up-to-date? Were they gathered
using the suite of commands recommended in the Performance Guide? Were the
distributions gathered with manual commands? Dostats? AUS? What is the
output from:
dbschema -d <databasename> -hd case_to_case_relation
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Sep 11, 2012 at 5:01 PM, SUSAN JONES <sjones@clerk.org> wrote:
> 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;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3bac1d95a79104c9739f8e
Hi , Are you sure your sqexplain.out is correct file? I see little mismatch
between SELECT present outside v/s SELECT inside the sqexplain.out file.The
WHERE clause has additional "i_case_relation_cd is not null" line..This might
be forcing an optimizer to use sequential scan.. Regards, Dharmendra
> To: ids@iiug.org
> From: sjones@clerk.org
> Subject: SQL/Optimizer Question [28290]
> Date: Tue, 11 Sep 2012 17:01:05 -0400
>
> 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;
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Original Post:
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<stuff cut>
Response:
So in one place you say the query where clause is just "i_case_relation_cd =
3" but then in the set explain output it's listed as "i_case_relation_cd is
not null and i_case_relation_cd =3". So you have listed the same column with 2
different filters and'd together where 1 of those filters appears to always be
true because you have a constraint that says that the column can't be null. I
could see how you could probably argue that perhaps the optimizer should be
able to see that the only filter that should apply is "i_case_relation_cd = 3"
which would cause it to use the index...but when you throw in the 2nd filter,
that makes it likely think it's going to have to scan a large percentage of
the rows in the index, so it might as well scan the table rather then do an
index look up and then a data page look up.
I guess I don't understand the need for the 2nd is not null filter when you
are looking for a specific column value, unless there is some typo here and
it's a filter on a different column and I can't read very well (which does
happen).
Jacques Renaut
Informix Advanced Support
APD Team
Sorry, I was trying a few things before I sent this post. This is the actual
sql without the "IS NOT NULL" clause and the execution plan still produces a
sequential scan. Any idea why the index isn't being used?
-- execution plan at /home/ifxcmgt/sqexplain.out at 10.64.21.10
QUERY: (OPTIMIZATION TIMESTAMP: 09-12-2012 06:54:35)
------
SELECT * FROM case_to_case_relation WHERE i_case_relation_cd = 3
Estimated Cost: 5410
Estimated # of Rows Returned: 98108
1) ifxcmgt.case_to_case_relation: SEQUENTIAL SCAN
Filters: ifxcmgt.case_to_case_relation.i_case_relation_cd = 3
As it was already replied there can be three reasons for that:
1- You don't have updated statistics and the engine may be thinking that
the table has just a few rows (check the nrows on systables record for this
table with the actual count(*) of records in the table
2- The engine may be considering that for "3" there is a high percentage of
the table, and since you're requesting all the fields (*) it's useless to
use the index. As an example, if 90% of the table records have
i_case_relation_cd = 3, and doing a SELECT *, the index is useless since it
would be slower. Again, the statistics are important. One way to check this
is to try "SELECT COUNT(*) FROM case_to_case_relation WHERE
i_case_relation_cd = 3". This may start to use the index, since you're not
requesting any data. Keep in mind that using the index is only good if it
really reduces the number of data records that you need to access
3- There is only the possibility that you're hitting a bug.... in this
simple case I'd say it's nearly impossible, but if the above two don't
explain the situation, you may consider opening a PMR to check this.
Regards.
On Wed, Sep 12, 2012 at 12:01 PM, SUSAN JONES <sjones@clerk.org> wrote:
> Sorry, I was trying a few things before I sent this post. This is the
> actual
> sql without the "IS NOT NULL" clause and the execution plan still produces
> a
> sequential scan. Any idea why the index isn't being used?
>
> -- execution plan at /home/ifxcmgt/sqexplain.out at 10.64.21.10
> QUERY: (OPTIMIZATION TIMESTAMP: 09-12-2012 06:54:35)
> ------
> SELECT * FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> Estimated Cost: 5410
> Estimated # of Rows Returned: 98108
>
> 1) ifxcmgt.case_to_case_relation: SEQUENTIAL SCAN
>
> Filters: ifxcmgt.case_to_case_relation.i_case_relation_cd = 3
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf302ef94aacfa0d04c97f4850
Thanks for everyone's response.
I did make sure statistics were updated. In fact I executed them again just to
be sure. I checked the nrow count in the sysmaster table against the actual
number of rows in the case_to_case_relation table and they matched.
I discovered that the index is used when I execute this statement
SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3
BUT a sequential scan is still being used when I execute this statement
SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3
The last thing mentioned by Fernando was it may be for "3" there is a high
percentage of that value in the table which would make the index useless. The
percentage of 3's in the table is over 90%. Something I didn't think to check.
Once again thank you all.
Just eyeballing the schema provided I am guessing you will get 16 rows per
2KB data
page. assumed row size of 120 bytes.
WHERE i_case_relation_cd = 3 is currently evaluated to return 98,108 rows
while we do not have the statistics on the table, the cluster value would
indicate
how many data pages would be accessed when doing an index scan, this could
be
as high as 98,108. Let assume the cluster value gives you 2 hits per page.
This means the
data page access associated with this index is 49,009. Now add to that
the
number of index pages which would be scanned guessing to be about 1000
index
pages. So you have 1000 index page + 49,009 data pages so you can
see the index scan could take as high as 50,009 random I/Os.
With a sequential scan the database can do a large single I/O that will
encompass many data
pages in one I/O because the pages are physically adjacent. You can take
the number of used
pages in the table and divide it by 64 or 128 (depending on the version,
os and a few other things). So if
we assume your table has 250,000 data pages ( which would represent 4
million rows). the number of I/O
for the sequent scan would be 250,000/64 which is 3,906 I/O. This is
allot smaller than the 49,009 I/O for an index
scan, Why should the optimizer not choose the sequential scan??
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/12/2012 04:01:59 AM:
> From: "SUSAN JONES" <sjones@clerk.org>
> To: ids@iiug.org,
> Date: 09/12/2012 04:03 AM
> Subject: Re: SQL/Optimizer Question [28296]
> Sent by: ids-bounces@iiug.org
>
> Sorry, I was trying a few things before I sent this post. This is the
actual
> sql without the "IS NOT NULL" clause and the execution plan still
produces a
> sequential scan. Any idea why the index isn't being used?
>
> -- execution plan at /home/ifxcmgt/sqexplain.out at 10.64.21.10
> QUERY: (OPTIMIZATION TIMESTAMP: 09-12-2012 06:54:35)
> ------
> SELECT * FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> Estimated Cost: 5410
> Estimated # of Rows Returned: 98108
>
> 1) ifxcmgt.case_to_case_relation: SEQUENTIAL SCAN
>
> Filters: ifxcmgt.case_to_case_relation.i_case_relation_cd = 3
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
On 12 September 2012 at 15:21 SUSAN JONES <sjones@clerk.org> wrote:
> Thanks for everyone's response.
>
> I did make sure statistics were updated. In fact I executed them again just
to
> be sure. I checked the nrow count in the sysmaster table against the actual
> number of rows in the case_to_case_relation table and they matched.
>
> I discovered that the index is used when I execute this statement
> SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> BUT a sequential scan is still being used when I execute this statement
>
> SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> The last thing mentioned by Fernando was it may be for "3" there is a high
> percentage of that value in the table which would make the index useless. The
> percentage of 3's in the table is over 90%. Something I didn't think to
check.
> Once again thank you all.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The two select statements look the same, what is the difference between them?
Can you try replace the asterisk with the column used in the condition?
On Sep 12, 2012 3:21 PM, "SUSAN JONES" <sjones@clerk.org> wrote:
> Thanks for everyone's response.
>
> I did make sure statistics were updated. In fact I executed them again
> just to
> be sure. I checked the nrow count in the sysmaster table against the actual
> number of rows in the case_to_case_relation table and they matched.
>
> I discovered that the index is used when I execute this statement
> SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> BUT a sequential scan is still being used when I execute this statement
>
> SELECT count(*) FROM case_to_case_relation WHERE i_case_relation_cd = 3>
> The last thing mentioned by Fernando was it may be for "3" there is a high
> percentage of that value in the table which would make the index useless.
> The
> percentage of 3's in the table is over 90%. Something I didn't think to
> check.
> Once again thank you all.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3074b386261b3704c98705ef
As for #2:
as a feature enhacement is it doable for explain stats to report the
selectivity factor for value(s) in the where clause and it's associated index
? I know you can glean that info from dbschema -hd however for convenience it
would be a nice.
Mark