RE: Why Sequential Scan not index ?
If msisdn doesn't head an index then a table scan will occur. Put a
separate index on msisdn (and update statistics in case you hadn't done so
already).
-----Original Message-----
From: owner-informix-list@iiug.iiug.org
[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of K.Hong Lee
Sent: Monday, August 21, 2000 2:09 AM
To: informix-list@iiug.org
Subject: Why Sequential Scan not index ?
hi guys,
The sqexplain.out tells what this SQL is a sequential
instead on index path. why ? from the schema as you
can see, the primary keys are on imsi and msisdn (by
default they should be index keys as well).
select * from t1 where msisdn = 2;
>>>>>>
sqexplain.out
<<<<<<
QUERY:
------
select * from t1 where msisdn = 2
Estimated Cost: 89119
Estimated # of Rows Returned: 750001
1) ussdtest.t1: SEQUENTIAL SCAN
Filters: ussdtest.t1.msisdn = 2
<<<<<<<
Table Schema
>>>>>>>
DBSCHEMA Schema Utility INFORMIX-SQL Version
7.20.UC4
Copyright (C) Informix Software, Inc., 1984-1996
Software Serial Number AAC#J915555
{ TABLE "ussdtest".t1 row size = 54 number of columns
= 8 index size = 51 }
create table "ussdtest".t1
(
imsi char(15) not null constraint
"ussdtest".n100_2,
msisdn char(15) not null constraint
"ussdtest".n100_3,
ussd_enabled integer not null constraint
"ussdtest".n100_4,
hlr_pri integer not null constraint
"ussdtest".n100_5,
hlr_sec integer not null constraint
"ussdtest".n100_6,
date_last_trans integer not null constraint
"ussdtest".n100_7,
total_no_trans integer not null constraint
"ussdtest".n100_8,
update_timestamp integer not null constraint
"ussdtest".n100_9,
primary key (imsi,msisdn) constraint
"ussdtest".u100_1
) extent size 500 next size 500 lock mode row;
revoke all on "ussdtest".t1 from "public";
Appreciate your reply.
Regards
Lee
__________________________________________________
Do You Yahoo!?
Yahoo! Mail ' Free email you can access from anywhere!
http://mail.yahoo.com/