Re: Why Sequential Scan not index ?
From: "K.Hong Lee" <kwonghong@yahoo.com>
>
>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;
Because msisdn is not the first part of the key.
> >>>>>>
>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/
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com