RE: FW: Sequential scan
After the obvious 'update stats' issues . . .
How unique is that column? Is it possible that this value isn't unique
enough to justify using an index?
John Carlson
Informix Database Administrator
EDS - WHSmith USA
3200 Windy Hill Road, Suite 1500 West
Atlanta, GA 30330
-----Original Message-----
From: William Rice [mailto:ricew@operamail.com]
Sent: Tuesday, January 30, 2001 8:50 AM
To: informix-list@iiug.org
Subject: Re: FW: Sequential scan
I agree with tech support ...
There might be a few _extreme_ cases where I might have an index on a
column which is aready the leading column of another index on that
table.
Will
In article <955ppo$1ag$1@news.xmission.com>,
"Dirk Moolman" <dirkm@reach.co.za> wrote:
>
> Is an index on paymonth necessary ? We had technical support out
here at
> our site, and they requested that I drop all indexes on my system
where the
> column of a stand-alone index is already the leading column in
another
> composite index (for the same table of course).
>
> They said that we had alot of duplication where indexes were
concerned and
> by duplicating the optimiser had more work to do, which could affect
our
> performance.
>
> Any comments ?
>
> Dirk
> Reach Technologies
>
> -----Original Message-----
> From: owner-informix-list@iiug.iiug.org
> [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Mark Griebling
> Sent: Monday, January 29, 2001 5:41 PM
> To: shahid.mehmood; informix-list@iiug. org (E-mail)
> Subject: Re:
>
> Hi Shadid,
>
> You need an index on payprmbcalc.paymonth.
>
> Later,
>
> Mark
>
> At 05:58 PM 1/29/01 +0500, shahid.mehmood wrote:
>
> OS: SCO UNIX 5.0, IX: OWS 7.20.UC2
>
> I have a table named payprmbcalc which also has index on its three
fields.
> This
> table contains 28000+ records. when I run a query on this table with
the db
> engine searches the table using sequential scan, where as I am
expecting
> search
> using an index. this query is taking too long to complete. can you
guys
> suggest
> any thing how to improve this query ...
>
> The table schema and 'set explain on' output is attached here with.
>
> The Table Schema ...
>
> { TABLE "paydba".payprmbcalc row size = 58 number of columns = 9
index size
> =
> 36 }
> create table "paydba".payprmbcalc
> (
> paymonth integer not null constraint "paydba".n196_305,
> internalno char(6) not null constraint "paydba".n196_306,
> currency char(10) not null constraint "paydba".n196_307,
> balance decimal(10,2),
> product decimal(10,2),
> taxprofit decimal(10,2),
> ntaxprofit decimal(10,2),
> lastedit date,
> lastuser char(10)
> );
>
> create unique index "paydba".pp001idx on "paydba".payprmbcalc
> (paymonth,internalno, currency);
>
> the 'set explain output' ...
>
> QUERY:
> ------
> select employee.employeeno , payprmbcalc.internalno,
> employee.fname , employee.mname , employee.lname ,
> payprmbcalc.currency , payprmbcalc.balance ,
> employee.appointment , employee.termination ,
> payprmbcalc.product , payprmbcalc.taxprofit ,
> payprmbcalc.ntaxprofit
> from payprmbcalc, employee
> where payprmbcalc.paymonth = 2000130000
> and employee.internalno = payprmbcalc.internalno
> order by 1>
> Estimated Cost: 7
> Estimated # of Rows Returned: 5
> Temporary Files Required For: Order By
>
> 1) paydba.payprmbcalc: SEQUENTIAL SCAN { why ? i need index
scan }
>
> Filters: paydba.payprmbcalc.paymonth = 2000130000
>
> 2) paydba.employee: INDEX PATH
>
> (1) Index Keys: internalno
> Lower Index Filter: paydba.employee.internalno =
> paydba.payprmbcalc.internalno
>
> an early reply will be highly appreciated.
>
> thanks.
>
> Shahid Mehmood
> Software Designer & IX DBA
> Information Systems Department
> The Aga Khan University,
> Karachi
>
> Official: Yes
>
>
Sent via Deja.com
http://www.deja.com/